Databases Reference
In-Depth Information
INDEX_DESC Hint
If a SQL statement performs an index range scan, specifying the
INDEX_DESC
hint makes the database
scan an ascending order index in descending order of the indexed values. Note that if you specify this
hint for an index sorted in the descending order, the database scans the index entries in ascending order.
Tip
Don't rush to add a hint to your queries just because you can! See if you have the correct optimizer
statistics for the objects and also check the way your SQL statement is framed before resorting to the use of a
hint. Hints should be a last-ditch alternative.
INDEX_JOIN Hint
If two indexes contain all the columns required to return a query's results, you can specify that the
database use an index join. The index join is simply a hash join of the individual indexes that together
will return all the columns requested by the query. The database doesn't need to access the table data in
this case, as all data is returned from the indexes themselves. You specify the index join with the
INDEX_JOIN
hint, as in this example:
SQL> select /*+ INDEX_JOIN(e emp_manager_ix emp_department_ix) */ department_id
from employees e
where manager_id < 110
and department_id < 50;
Make sure that you have indexes on both the
manager_id
and the
department_id
columns before
incorporating the
INDEX_JOIN
hint. The indexes that you specify must contain all the columns required to
satisfy the query. The preceding
SELECT
statement results in two index range scans: one on the
emp_manager_ix
index and the other on the
emp_department_ix
index.
INDEX_SS Hint
The
INDEX_SS
hint tells the optimizer to perform an index skip scan. By default, the database scans the
index in the ascending order of the index values.
Troubleshooting a Failed INDEX Hint
You must be aware that specifying an
INDEX
hint doesn't guarantee that Oracle will definitely use an
index or indexes. Remember that that the choice of the access path (index or full table scan, for example)
is only part of what the optimizer takes into account when deciding upon an optimal execution plan for
a query. Besides the access path (along with any alternative paths), the optimizer also must evaluate join
methods. In some cases, based on the join methods the optimizer selects, it may decide not to use any
index. Another reason why the database still ends up performing a full table scan even after you specify
the
INDEX
hint is when you're dealing with a unique index, which has many
NULL
values. The optimizer
