Example of Specifying an INDEX Hint in Oracle

The format for an index hint is:

select /*+ index(TABLE_NAME INDEX_NAME) */ col1...

There are a number of rules that need to be applied to this hint:

The TABLE_NAME is mandatory in the hint
The table alias MUST be used if the table is aliased in the query
If TABLE_NAME or alias is spelled incorrectly then the hint will not be used.
The INDEX_NAME is optional.
If an INDEX_NAME is entered without a TABLE_NAME then the hint will not be applied.
If a TABLE_NAME is supplied on its own then the optimizer will decide which index to use based on statistics.
If the INDEX_NAME is spelt incorrectly but the TABLE_NAME is spelled correctly then the hint will not be applied even though the TABLE_NAME is correct.
If there are multiple index hints to be applied, then the simplest way of addressing this is to repeat the index hint syntax for each index e.g.:
```
SELECT /*+ index(TABLE_NAME1 INDEX_NAME1) index(TABLE_NAME2 INDEX_NAME2) */ col1...
```

Remember that the parser/optimizer may have transformed/rewritten the query or may have chosen an access path which make the use of the index invalid and this may result in the index not being used.

Note: As long as the index() hint structure is correct this will force the use of the Cost Based Optimizer (CBO). This will happen even if the alias or table name is incorrect.

Examples

The examples below use a table CBOTAB with a unique single column index called CBOTAB1 on column COL1.

Correct hint to force use of the index:

explain plan for select /*+ index(cbotab) */ col1 from cbotab;
explain plan for select /*+ index(cbotab cbotab1) */ col1 from cbotab;
explain plan for select /*+ index(a cbotab1) */ col1 from cbotab a;

Query Plan
--------------------------------------------------------------------------------
SELECT STATEMENT   [CHOOSE] Cost=151
  INDEX FULL SCAN CBOTAB1 [ANALYZED]  Cost=151 Card=10000 Bytes=100000

1. The TABLE_NAME is mandatory in the hint. In the following example the TABLE_NAME was omitted so the index was not used.

SQL>  explain plan for select /*+ index() */ col1 from cbotab;
Query Plan
--------------------------------------------------------------------------------
SELECT STATEMENT   [CHOOSE] Cost=10
  TABLE ACCESS FULL CBOTAB [ANALYZED]  Cost=10 Card=10000 Bytes=100000

2. The INDEX_NAME is optional. Both of the following examples use the index:

explain plan for select /*+ index(cbotab) */ col1 from cbotab;
explain plan for select /*+ index(cbotab cbotab1) */ col1 from cbotab;

Query Plan
--------------------------------------------------------------------------------
SELECT STATEMENT   [CHOOSE] Cost=151
  INDEX FULL SCAN CBOTAB1 [ANALYZED]  Cost=151 Card=10000 Bytes=100000

3. The table alias MUST be used if the table is aliased in the query:

SQL>  explain plan for select /*+ index(cbotab) */ col1 from cbotab mytable;
Query Plan
--------------------------------------------------------------------------------
SELECT STATEMENT   [CHOOSE] Cost=10
  TABLE ACCESS FULL CBOTAB [ANALYZED]  Cost=10 Card=10000 Bytes=100000

4. Correct use of alias in hint:

SQL>  explain plan for select /*+ index(mytable) */ col1 from cbotab mytable;
Query Plan
--------------------------------------------------------------------------------
SELECT STATEMENT   [CHOOSE] Cost=151
  INDEX FULL SCAN CBOTAB1 [ANALYZED]  Cost=151 Card=10000 Bytes=100000

5. If TABLE_NAME or alias is spelled incorrectly then the hint will not be used.

SQL>  explain plan for select /*+ index(COBTAB) */ col1 from cbotab;
SQL>  explain plan for select /*+ index(MITABLE) */ col1 from cbotab mytable;
Query Plan
--------------------------------------------------------------------------------
SELECT STATEMENT   [CHOOSE] Cost=10
  TABLE ACCESS FULL CBOTAB [ANALYZED]  Cost=10 Card=10000 Bytes=100000

6. If an INDEX_NAME is entered without a TABLE_NAME then the hint will not be applied.

SQL>  explain plan for select /*+ index(cbotab1) */ col1 from cbotab;
Query Plan
--------------------------------------------------------------------------------
SELECT STATEMENT   [CHOOSE] Cost=10
  TABLE ACCESS FULL CBOTAB [ANALYZED]  Cost=10 Card=10000 Bytes=100000

7. If a TABLE_NAME is supplied on its own then the optimizer will decide which index to use based on statistics.

explain plan for select /*+ index(cbotab) */ col1 from cbotab;

Query Plan
--------------------------------------------------------------------------------
SELECT STATEMENT   [CHOOSE] Cost=151
  INDEX FULL SCAN CBOTAB1 [ANALYZED]  Cost=151 Card=10000 Bytes=100000

8. If the INDEX_NAME is spelt incorrectly but the TABLE_NAME is spelt correctly then the hint will not be applied even though the TABLE_NAME is correct.

SQL>  explain plan for select /*+ index(cbotab COBTAB1) */ col1 from cbotab;
Query Plan
--------------------------------------------------------------------------------
SELECT STATEMENT   [CHOOSE] Cost=10
  TABLE ACCESS FULL CBOTAB [ANALYZED]  Cost=10 Card=10000 Bytes=100000

Example of Specifying an INDEX Hint in Oracle

Examples

Latest Posts