Index Hints Oracle Tips by Burleson Consulting

While we will investigate tuning with indexes in detail in Chapter 20, let’s make a quick review of using index hints. Index hints are quite useful when tuning SQL, especially in cases where the optimizer chooses the “wrong” index (e.g., not the most selective index). This happened most often when using the rule-based optimizer, but there are also cases where an index hint is appropriate for the CBO.

Index hints can also be placed inside subqueries. Oracle provides the index hint, the and_equal hint, the index_asc hint, the index_combine hint, the index_desc hint, and the index_ffs hint to redirect the optimizer's use of indexes to access table rows.

Let’s begin our discussion with the most common hint, the index hint.

The Index Hint

The index hint is used to explicitly specify a table name, in which case the optimizer will use the best index on the table, or the table and index name, in which case the optimizer will use the specified index.

There are a number of rules that need to be followed to invoke an index hint:

• If table name or index name is spelled incorrectly, then the hint will not be used. Here we see a query with a misspelled table name:

select /*+ index(erp, dept_idx) */ * from emp;

• The table name is mandatory in the hint. For example, the following hint will be ignored because the table name is not specified in the query:

select /*+ index(dept_idx) */ * from emp;

• The table alias must be used if the table is aliased in the query. For example, the following query will ignore the index hint because the emp table is aliased with “e”:

select /*+ index(emp,dept_idx) */ * from emp e;

• The index name is optional. If not specified, the optimizer will use the “best” index on the table, but this is not recommended for permanent tuning. The following query will direct the optimizer to choose the best index for the emp table:

 select /*+ index(e) */ * from emp e;

The most important of these rules is to always specify both the table name and the index name in an index hint. There is always a small chance that a change in the CBO statistics might cause the optimizer to use a different index, and it is considered good practice to always specify both the table name and the index name.

WARNING: In Oracle8i, the optimizer may have transformed or rewritten the query. This may cause the optimizer to choose an access path that makes the use of the index invalid, and this may result in the index hint being ignored.

The index_join Hint

The index_join hint explicitly instructs the optimizer to use an index join as an access path. For the hint to have a positive effect, a sufficiently small number of indexes must exist that contain all the columns required to resolve the query.

The and_equal Hint

The and_equal hint is used when a table has several non-unique single column indexes and you want multiple indexes to be used to service the query. The and_equal hint merges the indexes and makes the separate indexes behave as if they were a single concatenated index.

The and_equal hint requires the specification of the target table name and at least two index names, and no more than five index names. For example, assume that we have the following query to retrieve the names of all salesman that report to manager 7698. Let’s also assume that there exists a non-unique index on job and another non-unique index on mgr.

select
   ename,
   job,
   deptno,
   mgr
from
   emp
where
   job = 'SALESMAN'
and
   mgr = 7698;

Here is the default CBO execution plan for the query. Pay careful attention to the row access method:

OPERATION
----------------------------------------------------------------------
OPTIONS                        OBJECT_NAME                    POSITION
------------------------------ ---------------------------- ----------
 SELECT STATEMENT
                                                                     1
  TABLE ACCESS
BY INDEX ROWID                 EMP                                   1
    INDEX
RANGE SCAN                     JOB_IDX                               1

From this execution plan, we see that the optimizer chooses to perform an index range scan on the job_idx, and then perform ROWID probes into the emp table to find those employees for manager 7698. If we can tell Oracle to merge job_idx and mgr_idx, then we can resolve the query without probing every emp row for manager 7698.

To do this, we add the and_equal hint, specifying the table name, and the nonunique indexes on job and mgr.

select /*+ and_equal(emp, job_idx, mgr_idx) */
   ename,
   job,
   deptno,
   mgr
from
   emp
where
   job = 'SALESMAN'
and
   mgr = 7698
;

Here is the execution plan:

OPERATION
----------------------------------------------------------------------
OPTIONS                        OBJECT_NAME                    POSITION
------------------------------ ---------------------------- ----------
 SELECT STATEMENT
                                                                    30
  TABLE ACCESS
BY INDEX ROWID                 EMP                                   1
    AND-EQUAL
      INDEX
RANGE SCAN                     JOB_IDX                               1
      INDEX
RANGE SCAN                     MGR_IDX                               2

Here we see a very different execution plan. As we see, instead of just using the job_idx and probing for all rows in that job, it treats the indexes as if they were a single, concatenated index (Figure 12-3).

Figure 3: Merging non-unique indexes with the and_equal hint

Since the query performs index range scans on both indexes, a ROWID intersection operation will return only those ROWIDs that match both conditions in the where clause. The query only probes the emp table when it knows the rows needed, thereby saving unnecessary table I/O.

This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.