This article presents how you can control access path for a table.
Access path – how Oracle will read data from a single table. If a hint specifies an unavailable access path, then the optimizer ignores it.
it’s recommended to read following article:
Hints to control Join Operations 11g
Test data
To show how hints work we need some dummy data
DROP TABLE test_tbl; CREATE TABLE test_tbl ( id1 NUMBER, id2 NUMBER, id3 NUMBER, id4 NUMBER, id5 NUMBER, id6 NUMBER, id7 NUMBER ); BEGIN DBMS_RANDOM.initialize(1000); FOR i IN 1..1000 LOOP INSERT INTO test_tbl VALUES ( i, i, (ABS(MOD(DBMS_RANDOM.random, 10))+1), (ABS(MOD(DBMS_RANDOM.random, 10))+1), (ABS(MOD(DBMS_RANDOM.random, 10))+1), (ABS(MOD(DBMS_RANDOM.random, 10))+1), (ABS(MOD(DBMS_RANDOM.random, 10))+1) ); END LOOP; COMMIT; END; / CREATE INDEX test_tbl_idx1 ON test_tbl(id1 ASC); CREATE INDEX test_tbl_idx2 ON test_tbl(id2 DESC); CREATE INDEX test_tbl_idx3 ON test_tbl(id3 DESC, id4 ASC); CREATE bitmap INDEX test_tbl_idx5 ON test_tbl(id5); CREATE bitmap INDEX test_tbl_idx6 ON test_tbl(id6); CREATE bitmap INDEX test_tbl_idx7 ON test_tbl(id7); BEGIN DBMS_STATS.gather_table_stats(USER, 'TEST_TBL', cascade=>TRUE); END; / SELECT * FROM test_tbl ID1 ID2 ID3 ID4 ID5 ID6 ID7 -------- -------- -------- -------- -------- -------- -------- 1 1 4 9 5 8 5 2 2 6 4 3 1 9 3 3 5 4 6 6 5 4 4 5 7 1 7 8 ... ID1 ID2 ID3 ID4 ID5 ID6 ID7 -------- -------- -------- -------- -------- -------- -------- 996 996 3 4 9 6 4 997 997 7 10 3 4 6 998 998 4 7 4 3 9 999 999 4 4 4 5 2 1000 1000 2 7 7 9 9 |
FULL
Instructs optimizer to perform full scan on a table.
/*+ FULL( [@queryblock] tablespec ) */
explain plan FOR SELECT /*+ FULL(a) */ a.* FROM test_tbl a WHERE a.id1 = 2; SELECT * FROM TABLE(DBMS_XPLAN.display(NULL, NULL, 'basic')); Plan hash VALUE: 602094504 -------------------------------------- | Id | Operation | Name | -------------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS FULL| TEST_TBL | -------------------------------------- |
INDEX
Instructs optimizer to use specific index scan on a table. If many indexes are specified, index with lowest cost is used.
/*+ INDEX( [@queryblock] tablespec [indexspec] ) */
explain plan FOR SELECT /*+ INDEX(a test_tbl_idx5) */ a.* FROM test_tbl a; SELECT * FROM TABLE(DBMS_XPLAN.display(NULL, NULL, 'basic')); Plan hash VALUE: 3199705705 ------------------------------------------------------ | Id | Operation | Name | ------------------------------------------------------ | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS BY INDEX ROWID | TEST_TBL | | 2 | BITMAP CONVERSION TO ROWIDS| | | 3 | BITMAP INDEX FULL SCAN | TEST_TBL_IDX5 | ------------------------------------------------------ |
NO_INDEX
Instructs optimizer to not use index scan on a specific table. You can list index(es) to block.
/*+ NO_INDEX( [@queryblock] tablespec [indexspec] ) */
explain plan FOR SELECT /*+ NO_INDEX(a) */ a.* FROM test_tbl a WHERE a.id1 = 2; SELECT * FROM TABLE(DBMS_XPLAN.display(NULL, NULL, 'basic')); Plan hash VALUE: 602094504 -------------------------------------- | Id | Operation | Name | -------------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS FULL| TEST_TBL | -------------------------------------- |
INDEX_ASC
Instructs optimizer to use index scan for a table. If range scan is executed data are scanned in ascending order.
/*+ INDEX_ASC( [@queryblock] tablespec [indexspec] ) */
explain plan FOR SELECT /*+ index_asc(a) */ a.* FROM test_tbl a WHERE a.id1 = 10; SELECT * FROM TABLE(DBMS_XPLAN.display(NULL, NULL, 'basic')); Plan hash VALUE: 4155728042 ----------------------------------------------------- | Id | Operation | Name | ----------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS BY INDEX ROWID| TEST_TBL | | 2 | INDEX RANGE SCAN | TEST_TBL_IDX1 | ----------------------------------------------------- explain plan FOR SELECT /*+ index_asc(a) */ a.* FROM test_tbl a WHERE a.id2 = 10; SELECT * FROM TABLE(DBMS_XPLAN.display(NULL, NULL, 'basic')); Plan hash VALUE: 1500805335 ----------------------------------------------------- | Id | Operation | Name | ----------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS BY INDEX ROWID| TEST_TBL | | 2 | INDEX RANGE SCAN | TEST_TBL_IDX2 | ----------------------------------------------------- |
INDEX_DESC
Instructs optimizer to use index scan for a table. If range scan is executed data are scanned in descending order.
/*+ INDEX_DESC( [@queryblock] tablespec [indexspec] ) */
explain plan FOR SELECT /*+ index_desc(a) */ a.* FROM test_tbl a WHERE a.id1 = 10; SELECT * FROM TABLE(DBMS_XPLAN.display(NULL, NULL, 'basic')); Plan hash VALUE: 4029715237 ------------------------------------------------------ | Id | Operation | Name | ------------------------------------------------------ | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS BY INDEX ROWID | TEST_TBL | | 2 | INDEX RANGE SCAN DESCENDING| TEST_TBL_IDX1 | ------------------------------------------------------ explain plan FOR SELECT /*+ index_desc(a) */ a.* FROM test_tbl a WHERE a.id2 = 10; SELECT * FROM TABLE(DBMS_XPLAN.display(NULL, NULL, 'basic')); Plan hash VALUE: 3286466119 ------------------------------------------------------ | Id | Operation | Name | ------------------------------------------------------ | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS BY INDEX ROWID | TEST_TBL | | 2 | INDEX RANGE SCAN DESCENDING| TEST_TBL_IDX2 | ------------------------------------------------------ |
INDEX_FFS
instructs optimizer to use fast full index scan rather than full table scan.
/*+ INDEX_FFS( [@queryblock] tablespec [indexspec] ) */
explain plan FOR SELECT /*+ index_ffs(a) */ a.id1 FROM test_tbl a WHERE a.id1 = 10; SELECT * FROM TABLE(DBMS_XPLAN.display(NULL, NULL, 'basic')); Plan hash VALUE: 3323432215 ---------------------------------------------- | Id | Operation | Name | ---------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | INDEX FAST FULL SCAN| TEST_TBL_IDX1 | ---------------------------------------------- |
NO_INDEX_FFS
instructs optimizer to exclude fast full index scan of the specified indexes
/*+ NO_INDEX_FFS( [@queryblock] tablespec [indexspec] ) */
explain plan FOR SELECT /*+ no_index_ffs(a) */ SUM(a.id1) FROM test_tbl a; SELECT * FROM TABLE(DBMS_XPLAN.display(NULL, NULL, 'basic')); Plan hash VALUE: 765159746 --------------------------------------- | Id | Operation | Name | --------------------------------------- | 0 | SELECT STATEMENT | | | 1 | SORT AGGREGATE | | | 2 | TABLE ACCESS FULL| TEST_TBL | --------------------------------------- |
INDEX_SS
Instructs optimizer to execute index skip scan for a table.
/*+ INDEX_SS( [@queryblock] tablespec [indexspec] ) */
explain plan FOR SELECT /*+ index_ss(a) */ a.* FROM test_tbl a WHERE a.id4 = 6; SELECT * FROM TABLE(DBMS_XPLAN.display(NULL, NULL, 'basic')); Plan hash VALUE: 4219827539 ----------------------------------------------------- | Id | Operation | Name | ----------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS BY INDEX ROWID| TEST_TBL | | 2 | INDEX SKIP SCAN | TEST_TBL_IDX3 | ----------------------------------------------------- |
NO_INDEX_SS
Instructs optimizer to exclude of the specified indexes on the specified table
/*+ NO_INDEX_SS( [@queryblock] tablespec [indexspec] ) */
explain plan FOR SELECT /*+ no_index_ss(a) */ a.* FROM test_tbl a WHERE a.id4 = 6; SELECT * FROM TABLE(DBMS_XPLAN.display(NULL, NULL, 'basic')); Plan hash VALUE: 602094504 -------------------------------------- | Id | Operation | Name | -------------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS FULL| TEST_TBL | -------------------------------------- |
INDEX_SS_ASC
Instructs optimizer to execute index skip scan for a table. If range scan is executed data are scanned in ascending order.
/*+ INDEX_SS_ASC( [@queryblock] tablespec [indexspec] ) */
explain plan FOR SELECT /*+ index_ss_asc(a) */ a.* FROM test_tbl a WHERE a.id4 = 6; SELECT * FROM TABLE(DBMS_XPLAN.display(NULL, NULL, 'basic')); Plan hash VALUE: 4219827539 ----------------------------------------------------- | Id | Operation | Name | ----------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS BY INDEX ROWID| TEST_TBL | | 2 | INDEX SKIP SCAN | TEST_TBL_IDX3 | ----------------------------------------------------- |
INDEX_SS_DESC
Instructs optimizer to execute index skip scan for a table. If range scan is executed data are scanned in descending order.
/*+ INDEX_SS_DESC( [@queryblock] tablespec [indexspec] ) */
explain plan FOR SELECT /*+ index_ss_desc(a) */ a.* FROM test_tbl a WHERE a.id4 = 6; SELECT * FROM TABLE(DBMS_XPLAN.display(NULL, NULL, 'basic')); Plan hash VALUE: 2236761040 ----------------------------------------------------- | Id | Operation | Name | ----------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS BY INDEX ROWID| TEST_TBL | | 2 | INDEX SKIP SCAN DESCENDING| TEST_TBL_IDX3 | ----------------------------------------------------- |
INDEX_COMBINE
Instructs the optimizer to use a bitmap access path for the table. Indexes must have all columns necessary to resolve the query.
/*+ INDEX_COMBINE( [@queryblock] tablespec [indexspec] ) */
explain plan FOR SELECT /*+ index_combine(a) */ a.* FROM test_tbl a WHERE a.id5 = 6 AND a.id6 = 9 AND a.id7 = 2; SELECT * FROM TABLE(DBMS_XPLAN.display(NULL, NULL, 'basic')); Plan hash VALUE: 3683887751 ------------------------------------------------------ | Id | Operation | Name | ------------------------------------------------------ | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS BY INDEX ROWID | TEST_TBL | | 2 | BITMAP CONVERSION TO ROWIDS| | | 3 | BITMAP AND | | | 4 | BITMAP INDEX SINGLE VALUE| TEST_TBL_IDX5 | | 5 | BITMAP INDEX SINGLE VALUE| TEST_TBL_IDX6 | | 6 | BITMAP INDEX SINGLE VALUE| TEST_TBL_IDX7 | ------------------------------------------------------ |
INDEX_JOIN
Instructs the optimizer to use an index join as an access path.
/*+ INDEX_JOIN( [@queryblock] tablespec [indexspec] ) */
explain plan FOR SELECT /*+ index_join(a) */ a.id1, a.id2 FROM test_tbl a WHERE a.id1 BETWEEN 1 AND 100 AND a.id2 BETWEEN 5 AND 6; SELECT * FROM TABLE(DBMS_XPLAN.display(NULL, NULL, 'basic')); Plan hash VALUE: 1828353109 ----------------------------------------------- | Id | Operation | Name | ----------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | VIEW | INDEX$_join$_001 | | 2 | HASH JOIN | | | 3 | INDEX RANGE SCAN| TEST_TBL_IDX2 | | 4 | INDEX RANGE SCAN| TEST_TBL_IDX1 | ----------------------------------------------- |
CLUSTER
Instructs optimizer to use a cluster scan to access the specified table. This hint applies only to tables in an index cluster.
/*+ CLUSTER( [@queryblock] tablespec) */
SELECT /*+ CLUSTER(b) */ b.emp_name FROM dept a, emp b WHERE a.dept_id=b.dept_id AND b.emp_id=6; ------------------------------------------------------ | Id | Operation | Name | ------------------------------------------------------ | 0 | SELECT STATEMENT | | | 1 | NESTED LOOPS | | | 2 | TABLE ACCESS FULL | DEPT | | 3 | TABLE ACCESS CLUSTER| EMP | | 4 | INDEX UNIQUE SCAN | EMP_DEPT_CLUSTER_IDX | ------------------------------------------------------ |
HASH
Instructs optimizer to use a hash scan to access the specified table. This hint applies only to tables in a hash cluster.
/*+ HASH( [@queryblock] tablespec) */
SELECT /*+ HASH(b) */ b.emp_name FROM emp_h b, dept_h a WHERE a.dept_id=b.dept_id AND b.emp_id IN (6,7,8); ------------------------------------- | Id | Operation | Name | ------------------------------------- | 0 | SELECT STATEMENT | | | 1 | NESTED LOOPS | | | 2 | TABLE ACCESS FULL| DEPT_H | | 3 | TABLE ACCESS HASH| EMP_H | ------------------------------------- |
Have a fun 🙂
Tomasz