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

object_name operation options count
------------------------------ --------------- --------------- -------
ACHTRANSACTION_NU1 INDEX RANGE SCAN 209
ACHTRANSACTION_NU2 FULL SCAN 6
ACHTRANSACTION_NU2 RANGE SCAN 38
ACH_DETAIL_NU1 INDEX RANGE SCAN 4
ACH_DETAIL_NU2 FAST FULL SCAN 10
ACH_DETAIL_NU2 RANGE SCAN 9
ACH_DETAIL_PK INDEX RANGE SCAN 19
ACH_DETAIL_PK UNIQUE SCAN 6
…
SQL>
Avoiding an Index
It is much more likely that you'll be looking for ways for the cost optimizer to use the indexes you've
created, rather than for it to ignore existing indexes. However, there are times when you want to do
exactly this: you want to keep the optimizer from using an index.
Avoiding All Use of an Index
You may want to avoid an index, for example, when you are dealing with an unselective index. That's
because if an index is not selective enough, it can sometimes be more efficient to scan the entire
underlying table.
If a SQL statement contains a construct such as a WHERE clause that includes an indexed column, the
optimizer is likely to use the index on that column. To preclude that from happening, you can specify the
NO_INDEX hint to let the optimizer disallow the use of a certain index. For example,
SQL> select /*+ NO_INDEX(employees emp_emp_id) */employee_id
from employees
where employee_id >200;
This example lists a specific index that you want the optimizer to ignore. If there are other indexes on the
table, the optimizer will still consider the use of those indexes.
Instead of specifying a single index, you can also list a set of indexes that the optimizer must ignore.
If you just specify the NO_INDEX hint without listing any indexes, the optimizer will ignore all indexes on
the table you specify. As with the INDEX hint that you'll encounter later in this chapter, the NO_INDEX hint
applies to B-tree, function-based, cluster, or domain indexes.
Avoiding Only the Fast Full Scan
You can use the NO_INDEX_FFS hint to direct the optimizer to avoid a fast full index scan of an index. Note
that you must specify the name of a specific index along with this hint, as in the case of an INDEX hint.
For example,
SQL> select /*+ no_index_ffs (items item_order_ix) +/ order_id from order_items;