The index_asc Hint Oracle Tips by Burleson Consulting

The index_asc hint requests to use the ascending index on a range scan operation. Since this is the default behavior of the optimizers anyway, this hint has very limited use in SQL tuning.

The no_index Hint

The no_index hint forces the optimizer to ignore the presence of an index. The no_index hint is most commonly used in cases where you have determined that a parallel full-table scan will outperform an index range scan. This index is equivalent to the full hint and is rarely used in SQL tuning.

The index_desc Hint

The index_desc hint requests to use the descending index on a range scan operation. The index_desc hint gives better performance in cases where you are calculating the maximum values of a column with the max built-in function.

select /*+ index_desc(emp, sal_idx) */
   ename,
   max(salary)
from
   emp
;

Here we see that the index access method has changed from ascending to descending.

The index_combine Hint

The index_combine hint is used to force a bitmap access path for the table. If no indexes are given as arguments for the index_combine hint, the optimizer will choose whatever Boolean combination of bitmap indexes has the best costing estimate for the table access. The index_combine hint directs the optimizer to perform a ROWID intersection operation from both bitmaps (Figure 12-4). In practice, it is always a good idea to specify the table name and both index names in the hint.

Figure 4: A bitmap index merge execution

For example, assume the following bitmap indexes on the emp table:

create bitmap index
   dept_bit
on
   emp
   (deptno);

create bitmap index
   job_bit
on
   emp
   (job);

Now, let’s look at the query. Here we filter the result rows where job=salesman and deptno=30.

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

Here is the execution plan. Please note that we are using a bitmap index on deptno and then filtering for the job rows:

OPERATION
----------------------------------------------------------------------
OPTIONS                        OBJECT_NAME                    POSITION
------------------------------ ---------------------------- ----------
 SELECT STATEMENT
                                                                     1
  TABLE ACCESS
BY INDEX ROWID                 EMP                                   1
    BITMAP CONVERSION
TO ROWIDS                                                            1
      BITMAP INDEX
SINGLE VALUE                   DEPT_BIT                              1

Now, we add the index_combine hint to our query.

select /*+ index_combine(emp, dept_bit, job_bit) */
   ename,
   job,
   deptno,
   mgr
from
   emp
where
   job = 'SALESMAN'
and
   deptno = 30
;

Here is the new execution plan:

OPERATION
----------------------------------------------------------------------
OPTIONS                        OBJECT_NAME                    POSITION
------------------------------ ---------------------------- ----------
 SELECT STATEMENT
                                                                     2
  TABLE ACCESS
BY INDEX ROWID                 EMP                                   1
    BITMAP CONVERSION
TO ROWIDS                                                            1
      BITMAP AND
        BITMAP INDEX
SINGLE VALUE                   DEPT_BIT                              1
        BITMAP INDEX
SINGLE VALUE                   JOB_BIT                               2

Here we see that the bitmap indexes are merged together with a BITMAP CONVERSION execution method. This bitmap merge method will dramatically reduce the execution time of queries on large tables.

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