INDEX Hint

The INDEX hint is frequently used to force one or more indexes to be executed for a given query. Oracle generally chooses the correct index or indexes with the optimizer, but when the optimizer chooses the wrong index or no index at all, this hint is excellent. You may also use multiple indexes with this hint and Oracle will choose one or more of the indexes specified based on the best plan. If you only specify one index, the optimizer considers only one index.

Syntax:

select /*+ INDEX (table index1, index2…) */ column1, …

from Table_Name

where conditions;

Example1:

select /*+ INDEX (emp deptno_idx) */ empno, ename, deptno
from emp
where deptno = 10;

Example2:

Multiple indexes for a single table can be specified, but it is usually better to specify only the most restrictive index on a given query (avoiding the merging of the result of each index). If multiple indexes are specified, Oracle chooses which (one or more) to use, so be careful or your hint could potentially be overridden.

select /*+ INDEX (emp deptno_idx, empno_idx) */ empno, ename, deptno
from emp
where deptno = 10
and empno = 7750;

Example3:

select /*+ INDEX */ empno, ename, deptno
from emp
where deptno = 1
and empno = 7750;

In this example, no index is specified. Oracle now weighs all of the possible indexes that are available and chooses one or more to be used. Since we have not specified a particular index, but we have specified the INDEX hint, the optimizer will not do a full table scan.