advertisements
_____________________________________________________________________________________________________________________
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')
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment