Hints to control access path Oracle 11g

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  |          |
|   1TABLE 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             |               |
|   1TABLE 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  |          |
|   1TABLE 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            |               |
|   1TABLE 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            |               |
|   1TABLE 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             |               |
|   1TABLE 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             |               |
|   1TABLE 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     |               |
|   1INDEX 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            |               |
|   1TABLE 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  |          |
|   1TABLE 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            |               |
|   1TABLE 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            |               |
|   1TABLE 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             |               |
|   1TABLE 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   |                  |
|   1VIEW              | 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