Tuning Index Usage - Expert Indexing in Oracle Database 11g - page 158

choose the wrong index if you've multiple indexes and just specify the index hint without specifying an
index. In fact, the optimizer will sometimes choose a far superior full table scan if you don't specify any
index hints whatsoever. All you may end up doing by specifying the INDEX hint without telling the
optimizer which index you want to use is forcing the usage of an inefficient index.
If you want to specify more than one index within the INDEX hint, Oracle recommends that you
specify the INDEX_COMBINE hint rather than the INDEX hint. You specify the INDEX_COMBINE hint in the
following way:
SQL> select /*+ index_combine(e emp_manager_ix emp_department_ix) */ *
from employees e
where manager_id = 108
or department_id=110;
In this case, the optimizer will use the combination of the two indexes you specify that has the lowest
cost. If you specify the INDEX_COMBINE hint without providing a list of indexes, the optimizer will use the
best combination of indexes based on its cost estimates.
Applying Related Hints
INDEX and the INDEX_COMBINE aren't the only hints you can use to instruct the optimizer to use an index.
Oracle Database allows you to use a number of index-related hints, which are briefly described in the
following sections. In addition to the hints discussed here, there are additional index-related hints such
as the INDEX_FFS hint, which tells the optimizer to perform a fast full index scan instead of a full table
scan. If an index contains all the columns necessary to satisfy a query, a fast full index scan is a good
alternative to a full table scan. The fast full index scan needs to access just the index and not both the
index and the table together, as is the case with a normal index range scan. The database scans the entire
index using multiblock reads. In most cases, a fast full index scan runs faster than a full index scan
because it can use multiblock I/O. You can also parallelize this type of scan just as a table scan. The
INDEX_FFS_DESC hint instructs the optimizer to do the fast full scan in a descending order. Sometimes you
may want to the optimizer to perform an index range scan (by specifying the INDEX hint), but it goes
ahead and does a full scan. Oracle offers you the two hints INDEX_RS_ASC and INDEX_RS_DESC to explicitly
direct the optimizer to perform an index range scan in the ascending or descending order. The cost
optimizer uses the value of the DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter to determine
the relative costs of full table scans and index fast full scans. A large value for this parameter can
influence the optimizer to choose a full table scan in preference to an index scan.
The following is a brief explanation of some additional index-related hints.
INDEX_ASC Hint
By default, the database scans indexes in ascending order of the index entries during an index range
scan. If you created a descending index, the database scans the index in the descending order. You can
use the INDEX_ASC hint to explicitly specify an ascending range scan.