Oracle NO_INDEX hint: Syntax, Purpose and Example

Syntax: /*+no_index (Table_name Index_name)*/
Purpose: NO_INDEX hint explicitly notifies the optimizer to not to use the specified index(s). This can be used for query testing purpose without dropping the actual index. In some cases queries will give better performance without indexes. This difference can be tested using this hint. This hint applies to function_based, B-tree, bitmap, cluster indexes.

*.If NO_INDEX specifies a single available index, then the optimizer does not consider a scan on this index. Other indexes not specified are still considered.
*. If NO_INDEX specifies a list of available indexes, then the optimizer does not consider a scan on any of the specified indexes. Other indexes not specified in the list are still considered.
*. If NO_INDEX specifies no indexes, then the optimizer does not consider a scan on any index on the table. This behavior is the same as a NO_INDEX hint that specifies a list of all available indexes for the table.
Example:
SQL>create table hint_test as select * from all_objects;

Table created.

SQL>select count(*) from hint_test;

  COUNT(*)
----------
     67799

SQL>create index idx_hint_test on hint_test(object_id);

Index created.

exec dbms_stats.gather_table_stats(ownname => 'STHOMAS', tabname => 'HINT_TEST', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, cascade => TRUE,method_opt => ' FOR ALL COLUMNS SIZE AUTO', degree => 8);

General query with where clause will use the table index.
SQL> select * from hint_test where object_type='FUNCTION';

Execution Plan
----------------------------------------------------------
Plan hash value: 315512698

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |  1738 |   166K|    72   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| HINT_TEST     |  1738 |   166K|    72   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_HINT_TEST |  1738 |       |     5   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_TYPE"='FUNCTION')

With no_index hint
select /*+no_index(HINT_TEST IDX_HINT_TEST)*/ * from hint_test where object_type='FUNCTION';

Execution Plan
----------------------------------------------------------
Plan hash value: 2875977681

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |  1738 |   166K|   272   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| HINT_TEST |  1738 |   166K|   272   (1)| 00:00:04 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_TYPE"='FUNCTION')
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google