Oracle employs several methods, known as access paths, to retrieve data for a SQL statement. Optimizer hints are comments within the SQL code used to explicitly instruct the optimizer to use a specific access path, overriding its default decision-making process.
FULL(table_name)
This hint tells the optimizer to do a full table scan for the specified table.
SQL> SELECT /*+ FULL(sales) */ * FROM sales WHERE id < 300;
Execution Plan
----------------------------------------------------------
Plan hash value: 781590677
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 299 | 7475 | 1143 (2)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| SALES | 299 | 7475 | 1143 (2)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"<300)CLUSTER(table_name)
This hint tells the optimizer to do a cluster scan for the specified table.( Used in CLUSTERED environment. )
SQL> SELECT /*+ CLUSTER(sales) */ * FROM sales WHERE id = 1;
Execution Plan
----------------------------------------------------------
Plan hash value: 22352277
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| SALES | 1 | 25 | 3 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | SALES_IDX | 1 | | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=1)
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
1 - SEL$1 / SALES@SEL$1
U - CLUSTER(sales)ROWID(table_name)
This hint tells the optimizer to do a ROWID scan for the specified table.
SQL> SELECT /*+ ROWID(sales) */ * FROM sales;
Execution Plan
----------------------------------------------------------
Plan hash value: 781590677
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000K| 23M| 1146 (2)| 00:00:01 |
| 1 | TABLE ACCESS FULL| SALES | 1000K| 23M| 1146 (2)| 00:00:01 |
---------------------------------------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
1 - SEL$1 / SALES@SEL$1
U - ROWID(sales)HASH(table_name)
This hint tells the optimizer to use hash access method for the specified table.
SQL> SELECT /*+ HASH(sales) */ * FROM sales;
Execution Plan
----------------------------------------------------------
Plan hash value: 781590677
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000K| 23M| 1146 (2)| 00:00:01 |
| 1 | TABLE ACCESS FULL| SALES | 1000K| 23M| 1146 (2)| 00:00:01 |
---------------------------------------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
1 - SEL$1 / SALES@SEL$1
U - HASH(sales)HASH_AJ(table_name)
Transforms a NOT IN/NOT EXISTS subquery to a hash anti-join.
SQL> SELECT * FROM dept WHERE deptno NOT IN (SELECT /*+ HASH_AJ */ deptno FROM emp);
Execution Plan
----------------------------------------------------------
Plan hash value: 218628244
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN ANTI NA | | 1 | 23 | 6 (17)| 00:00:01 |
| 2 | SORT JOIN | | 4 | 80 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| DEPT | 4 | 80 | 2 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 5 | SORT UNIQUE | | 14 | 42 | 4 (25)| 00:00:01 |
| 6 | TABLE ACCESS FULL | EMP | 14 | 42 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("DEPTNO"="DEPTNO")
filter("DEPTNO"="DEPTNO")
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
0 - SEL$2
U - HASH_AJMERGE_AJ(table_name)
Transforms a NOT IN/NOT EXISTS subquery to a MERGE anti-join.
SQL> SELECT * FROM dept WHERE deptno NOT IN (SELECT /*+ MERGE_AJ */ deptno FROM emp);
Execution Plan
----------------------------------------------------------
Plan hash value: 218628244
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN ANTI NA | | 1 | 23 | 6 (17)| 00:00:01 |
| 2 | SORT JOIN | | 4 | 80 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| DEPT | 4 | 80 | 2 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 5 | SORT UNIQUE | | 14 | 42 | 4 (25)| 00:00:01 |
| 6 | TABLE ACCESS FULL | EMP | 14 | 42 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("DEPTNO"="DEPTNO")
filter("DEPTNO"="DEPTNO")
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
0 - SEL$2
U - MERGE_AJINDEX(table_name [index_name])
This hint tells the optimizer to do index scan for the specified table using the specified index(s). If a list of indexes is specified, the optimizer chooses the one with the lowest cost. If no index is specified then the optimizer chooses the available index for the table with the lowest cost.
SQL> SELECT /*+ INDEX(sales sales_idx) */ * FROM sales where id <= 100000;
Execution Plan
----------------------------------------------------------
Plan hash value: 1469818611
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| 2441K| 629 (1)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| SALES | 100K| 2441K| 629 (1)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | SALES_IDX | 100K| | 213 (1)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"<=100000)
SQL> SELECT /*+ INDEX(sales sales_idx sales_idx2) */ * FROM sales where order_id = 6291;
Execution Plan
----------------------------------------------------------
Plan hash value: 38061082
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 112 | 2800 | 114 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| SALES | 112 | 2800 | 114 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | SALES_IDX2 | 112 | | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ORDER_ID"=6291)
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
1 - SEL$1 / SALES@SEL$1
U - INDEX(sales sales_idx sales_idx2)
SQL> SELECT /*+ INDEX(sales) */ * FROM sales where id <= 100000;
Execution Plan
----------------------------------------------------------
Plan hash value: 1469818611
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| 2441K| 629 (1)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| SALES | 100K| 2441K| 629 (1)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | SALES_IDX | 100K| | 213 (1)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"<=100000)INDEX_ASC(table_name [index_name])
This hint tells the optimizer to do ASCENDING index scan for the specified table using the specified index(s). If a list of indexes is specified, the optimizer chooses the one with the lowest cost. If no index is specified then the optimizer chooses the available index for the table with the lowest cost.
SQL> SELECT /*+ INDEX_ASC(sales sales_idx) */ * FROM sales where id = 100000;
Execution Plan
----------------------------------------------------------
Plan hash value: 22352277
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| SALES | 1 | 25 | 3 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | SALES_IDX | 1 | | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=100000)
SQL> SELECT /*+ INDEX_ASC(sales sales_idx sales_idx2) */ * FROM sales where order_id = 6291;
Execution Plan
----------------------------------------------------------
Plan hash value: 38061082
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 112 | 2800 | 114 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| SALES | 112 | 2800 | 114 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | SALES_IDX2 | 112 | | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ORDER_ID"=6291)
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
1 - SEL$1 / SALES@SEL$1
U - INDEX_ASC(sales sales_idx sales_idx2)
SQL> SELECT /*+ INDEX_ASC(sales) */ * FROM sales where id <= 100000;
Execution Plan
----------------------------------------------------------
Plan hash value: 1469818611
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| 2441K| 629 (1)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| SALES | 100K| 2441K| 629 (1)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | SALES_IDX | 100K| | 213 (1)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"<=100000)INDEX_DESC(table_name [index_name])
This hint tells the optimizer to do DESCENDING index scan for the specified table using the specified index(s). If a list of indexes is specified, the optimizer chooses the one with the lowest cost. If no index is specified then the optimizer chooses the available index for the table with the lowest cost.
SQL> SELECT /*+ INDEX_DESC(sales sales_idx) */ * FROM sales where id = 100000;
Execution Plan
----------------------------------------------------------
Plan hash value: 2415328169
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| SALES | 1 | 25 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN DESCENDING | SALES_IDX | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=100000)
filter("ID"=100000)
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
1 - SEL$1 / SALES@SEL$1
U - INDEX_DESC(sales sales_idx)
SQL> SELECT /*+ INDEX_DESC(sales sales_idx sales_idx2) */ * FROM sales where order_id = 6291;
Execution Plan
----------------------------------------------------------
Plan hash value: 1525438474
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 112 | 2800 | 114 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| SALES | 112 | 2800 | 114 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN DESCENDING | SALES_IDX2 | 112 | | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ORDER_ID"=6291)
filter("ORDER_ID"=6291)
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (U - Unused (2))
---------------------------------------------------------------------------
1 - SEL$1 / SALES@SEL$1
U - INDEX_DESC(sales sales_idx sales_idx2)
U - INDEX_DESC(sales sales_idx sales_idx2)
SQL> SELECT /*+ INDEX_DESC(sales) */ * FROM sales where id <= 100000;
Execution Plan
----------------------------------------------------------
Plan hash value: 2415328169
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| 2441K| 629 (1)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| SALES | 100K| 2441K| 629 (1)| 00:00:01 |
|* 2 | INDEX RANGE SCAN DESCENDING | SALES_IDX | 100K| | 213 (1)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"<=100000)
filter("ID"<=100000)
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
1 - SEL$1 / SALES@SEL$1
U - INDEX_DESC(sales)INDEX_COMBINE(table_name [index_name])
This hint tells the optimizer to combine multiple bitmap indexes on the table if the cost shows that to do so would give better performance.
SQL> SELECT /*+ INDEX_COMBINE(sales2 bitmap_id_idx bitmap_order_idx) */ * FROM sales2 WHERE id = 1 AND order_id = 1;
Execution Plan
----------------------------------------------------------
Plan hash value: 2040715661
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| SALES2 | 1 | 25 | 2 (0)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 3 | BITMAP AND | | | | | |
|* 4 | BITMAP INDEX SINGLE VALUE | BITMAP_ORDER_IDX | | | | |
|* 5 | BITMAP INDEX SINGLE VALUE | BITMAP_ID_IDX | | | | |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("ORDER_ID"=1)
5 - access("ID"=1)
SQL> SELECT /*+ INDEX_COMBINE(bitmap_id_idx bitmap_order_idx) */ * FROM sales2 WHERE id = 1 AND order_id = 1;
Execution Plan
----------------------------------------------------------
Plan hash value: 2040715661
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| SALES2 | 1 | 25 | 2 (0)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 3 | BITMAP AND | | | | | |
|* 4 | BITMAP INDEX SINGLE VALUE | BITMAP_ORDER_IDX | | | | |
|* 5 | BITMAP INDEX SINGLE VALUE | BITMAP_ID_IDX | | | | |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("ORDER_ID"=1)
5 - access("ID"=1)
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (N - Unresolved (1))
---------------------------------------------------------------------------
1 - SEL$1
N - INDEX_COMBINE(bitmap_id_idx bitmap_order_idx)INDEX_JOIN(table_name [index_name])
This hint tells the optimizer to use index join as an access path.
SQL> SELECT /*+ INDEX_JOIN(sales2 bitmap_id_idx bitmap_order_idx) */ * FROM sales2 WHERE id = 1 AND order_id = 1;
Execution Plan
----------------------------------------------------------
Plan hash value: 2040715661
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| SALES2 | 1 | 25 | 2 (0)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 3 | BITMAP AND | | | | | |
|* 4 | BITMAP INDEX SINGLE VALUE | BITMAP_ORDER_IDX | | | | |
|* 5 | BITMAP INDEX SINGLE VALUE | BITMAP_ID_IDX | | | | |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("ORDER_ID"=1)
5 - access("ID"=1)
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (U - Unused (2))
---------------------------------------------------------------------------
1 - SEL$1 / SALES2@SEL$1
U - INDEX_JOIN(sales2 bitmap_id_idx bitmap_order_idx)
U - INDEX_JOIN(sales2 bitmap_id_idx bitmap_order_idx)
SQL> SELECT /*+ INDEX_JOIN(bitmap_id_idx bitmap_order_idx) */ * FROM sales2 WHERE id = 1 AND order_id = 1;
Execution Plan
----------------------------------------------------------
Plan hash value: 2040715661
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| SALES2 | 1 | 25 | 2 (0)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 3 | BITMAP AND | | | | | |
|* 4 | BITMAP INDEX SINGLE VALUE | BITMAP_ORDER_IDX | | | | |
|* 5 | BITMAP INDEX SINGLE VALUE | BITMAP_ID_IDX | | | | |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("ORDER_ID"=1)
5 - access("ID"=1)
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (N - Unresolved (1))
---------------------------------------------------------------------------
1 - SEL$1
N - INDEX_JOIN(bitmap_id_idx bitmap_order_idx)INDEX_SS(table_name [index_name])
Perform a index skip scan for the specified table.
SQL> SELECT /*+ INDEX_SS(test_objects test_objects_i) */ owner, object_name FROM test_objects WHERE object_name = 'DBMS_OUTPUT';
Execution Plan
----------------------------------------------------------
Plan hash value: 1293870291
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 1 (0)| 00:00:01 |
|* 1 | INDEX SKIP SCAN | TEST_OBJECTS_I | 1 | 25 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_NAME"='DBMS_OUTPUT')
filter("OBJECT_NAME"='DBMS_OUTPUT')
SQL> SELECT /*+ INDEX_SS(test_objects) */ owner, object_name FROM test_objects WHERE object_name = 'DBMS_OUTPUT';
Execution Plan
----------------------------------------------------------
Plan hash value: 1293870291
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 1 (0)| 00:00:01 |
|* 1 | INDEX SKIP SCAN | TEST_OBJECTS_I | 1 | 25 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_NAME"='DBMS_OUTPUT')
filter("OBJECT_NAME"='DBMS_OUTPUT')INDEX_SS_ASC(table_name [index_name])
Perform a ASCENDING index skip scan for the specified table.
SQL> SELECT /*+ INDEX_SS_ASC(test_objects test_objects_i) */ owner, object_name FROM test_objects WHERE object_name = 'DBMS_OUTPUT';
Execution Plan
----------------------------------------------------------
Plan hash value: 1293870291
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 1 (0)| 00:00:01 |
|* 1 | INDEX SKIP SCAN | TEST_OBJECTS_I | 1 | 25 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_NAME"='DBMS_OUTPUT')
filter("OBJECT_NAME"='DBMS_OUTPUT')
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
1 - SEL$1 / TEST_OBJECTS@SEL$1
U - INDEX_SS_ASC(test_objects test_objects_i)
SQL> SELECT /*+ INDEX_SS_ASC(test_objects) */ owner, object_name FROM test_objects WHERE object_name = 'DBMS_OUTPUT';
Execution Plan
----------------------------------------------------------
Plan hash value: 1293870291
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 1 (0)| 00:00:01 |
|* 1 | INDEX SKIP SCAN | TEST_OBJECTS_I | 1 | 25 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_NAME"='DBMS_OUTPUT')
filter("OBJECT_NAME"='DBMS_OUTPUT')
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
1 - SEL$1 / TEST_OBJECTS@SEL$1
U - INDEX_SS_ASC(test_objects)INDEX_SS_DESC(table_name [index_name])
Perform a DESCENDING index skip scan for the specified table.
SQL> SELECT /*+ INDEX_SS_DESC(test_objects test_objects_i) */ owner, object_name FROM test_objects WHERE object_name = 'DBMS_OUTPUT';
Execution Plan
----------------------------------------------------------
Plan hash value: 524023766
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 1 (0)| 00:00:01 |
|* 1 | INDEX SKIP SCAN DESCENDING| TEST_OBJECTS_I | 1 | 25 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_NAME"='DBMS_OUTPUT')
filter("OBJECT_NAME"='DBMS_OUTPUT')
SQL> SELECT /*+ INDEX_SS_DESC(test_objects) */ owner, object_name FROM test_objects WHERE object_name = 'DBMS_OUTPUT';
Execution Plan
----------------------------------------------------------
Plan hash value: 524023766
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 1 (0)| 00:00:01 |
|* 1 | INDEX SKIP SCAN DESCENDING| TEST_OBJECTS_I | 1 | 25 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_NAME"='DBMS_OUTPUT')
filter("OBJECT_NAME"='DBMS_OUTPUT')NO_INDEX_SS(table_name [index_name])
The NO_INDEX_SS hint causes the optimizer to exclude a skip scan of the specified indexes on the specified table.
SQL> SELECT /*+ NO_INDEX_SS(test_objects test_objects_i) */ owner, object_name FROM test_objects WHERE object_name = 'DBMS_OUTPUT';
Execution Plan
----------------------------------------------------------
Plan hash value: 355287670
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 1 (0)| 00:00:01 |
|* 1 | INDEX FULL SCAN | TEST_OBJECTS_I | 1 | 25 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_NAME"='DBMS_OUTPUT')
filter("OBJECT_NAME"='DBMS_OUTPUT')
SQL> SELECT /*+ NO_INDEX_SS(test_objects) */ owner, object_name FROM test_objects WHERE object_name = 'DBMS_OUTPUT';
Execution Plan
----------------------------------------------------------
Plan hash value: 355287670
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 1 (0)| 00:00:01 |
|* 1 | INDEX FULL SCAN | TEST_OBJECTS_I | 1 | 25 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_NAME"='DBMS_OUTPUT')
filter("OBJECT_NAME"='DBMS_OUTPUT')INDEX_FFS(table_name [index_name])
Perform a fast full index scan rather than a table scan.
SQL> SELECT /*+ INDEX_FFS(test_objects test_objects_i) */ owner, object_name FROM test_objects WHERE object_name = 'DBMS_OUTPUT';
Execution Plan
----------------------------------------------------------
Plan hash value: 1877643037
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| TEST_OBJECTS_I | 1 | 25 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='DBMS_OUTPUT')
SQL> SELECT /*+ INDEX_FFS(test_objects) */ owner, object_name FROM test_objects WHERE object_name = 'DBMS_OUTPUT';
Execution Plan
----------------------------------------------------------
Plan hash value: 1877643037
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| TEST_OBJECTS_I | 1 | 25 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='DBMS_OUTPUT')NO_INDEX_FFS(table_name [index_name])
Don’t perform a fast full index scan.
SQL> SELECT /*+ NO_INDEX_FFS(test_objects test_objects_i) */ owner, object_name FROM test_objects WHERE object_name = 'DBMS_OUTPUT';
Execution Plan
----------------------------------------------------------
Plan hash value: 1293870291
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 1 (0)| 00:00:01 |
|* 1 | INDEX SKIP SCAN | TEST_OBJECTS_I | 1 | 25 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_NAME"='DBMS_OUTPUT')
filter("OBJECT_NAME"='DBMS_OUTPUT')
SQL> SELECT /*+ NO_INDEX_FFS(test_objects) */ owner, object_name FROM test_objects WHERE object_name = 'DBMS_OUTPUT';
Execution Plan
----------------------------------------------------------
Plan hash value: 1293870291
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 1 (0)| 00:00:01 |
|* 1 | INDEX SKIP SCAN | TEST_OBJECTS_I | 1 | 25 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_NAME"='DBMS_OUTPUT')
filter("OBJECT_NAME"='DBMS_OUTPUT')
SQL> SELECT /*+ NO_INDEX_FFS(test_objects test_objects_i) */ owner, object_name FROM test_objects WHERE sharing = 'METADATA LINK';
Execution Plan
----------------------------------------------------------
Plan hash value: 3570092908
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 33 | 1188 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST_OBJECTS | 33 | 1188 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SHARING"='METADATA LINK')
SQL> SELECT /*+ NO_INDEX_FFS(test_objects) */ owner, object_name FROM test_objects WHERE sharing = 'METADATA LINK';
Execution Plan
----------------------------------------------------------
Plan hash value: 3570092908
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 33 | 1188 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST_OBJECTS | 33 | 1188 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SHARING"='METADATA LINK')NO_INDEX(table_name [index_name])
This hint tells the optimizer to NOT do index scan for the specified table using the specified index(s).
SQL> SELECT /*+ NO_INDEX(sales sales_idx) */ * FROM sales where id <= 100000;
Execution Plan
----------------------------------------------------------
Plan hash value: 781590677
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| 2441K| 1144 (2)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| SALES | 100K| 2441K| 1144 (2)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"<=100000)
SQL> SELECT /*+ NO_INDEX(sales sales_idx sales_idx2) */ * FROM sales where order_id = 6291;
Execution Plan
----------------------------------------------------------
Plan hash value: 781590677
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 112 | 2800 | 1145 (2)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| SALES | 112 | 2800 | 1145 (2)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ORDER_ID"=6291)
SQL> SELECT /*+ NO_INDEX(sales) */ * FROM sales where id <= 100000;
Execution Plan
----------------------------------------------------------
Plan hash value: 781590677
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| 2441K| 1144 (2)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| SALES | 100K| 2441K| 1144 (2)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"<=100000)
