Question: I want to use Oracle to force index usage for a SQL.
I've tried an index hint, but I still need to force index as the hint is
ignored. What is the trick in Oracle force index?
Answer: Given accurate CBO statistics, the Oracle optimizer will
also choose the "best" index, and it's not always a good idea to force a
specific index unless you are sure that the index will always be the
fastest path to your rows.
easiest way to force index usage is with the index hint. When
forcing an index, always use the table alias whenever you have a query
that specifies an alias. For example, the following query will force the
use of the dept_idx index because the emp table is aliased with ?e?:
select /*+ index(e,dept_idx) */ * from
Also, never reference the schema name in an index hint Index
hints will be ignored when the schema owner is specified in the hint.
For example, the following hint will be ignored and it will not force
the dept_idx index:
select /*+ index(scott.emp,dept_idx)
*/ * from emp;