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

 Note The effect of adjusting optimizer_index_cost_adj parameter is not really to force an index use per se.
Rather, it forces an adjustment to the query cost, which makes it more likely that the resulting cost will be such
that the optimizer chooses to use an index.
A note of caution is appropriate here: it's our experience that in the overwhelming majority of cases,
the cost optimizer does know best. If you create primary and unique keys on all tables, and index all
foreign keys as well as any non-unique columns that are likely to figure in your SQL queries, you've
already provided the optimizer all the information that it needs. Of course, you must ensure that you
gather timely optimizer statistics with the correct settings. If you follow the recommendations here,
chances are that the cost optimizer will produce optimal plans just about all the time. You may
occasionally find the need to intervene and override the optimizer's choices, but it'll be somewhat of a
rare event.
Applying the INDEX Hint
Sometimes, the optimizer won't use an index, although you're certain the index will really help
performance. The cost-based optimizer isn't perfect; it doesn't always have an accurate understanding
of the nature and distribution of data. Developers and DBAs often possess a better understanding of
their application and the nature of their data. In cases where you think the optimizer ought to use an
index, you can force it to use an index by specifying an INDEX hint in the query.
An INDEX hint instructs the optimizer to use an index scan for a specific table. The optimizer will
respect the hint for all types of indexes, such as normal. B-tree indexes as well as function-based,
bitmap, bitmap join, and domain indexes.
You specify an INDEX hint in the following way:
SQL> select /*+ index (employees emp_dept_idx) +/
employee_id, department_id from employees;
where department_id > 50;
When you specify that the optimizer must use a specific index, remember that the optimizer will
honor your instruction and not perform a full table scan, but it also will ignore other potential indexes it
may have considered. Thus, specifying the INDEX hint will force the optimizer to use only the index you
specify with the hint.
You can also specify the INDEX hint without specifying an index, as shown here:
SQL> select /*+ index (employees) +/
employee_id, department_id from employees;
where department_id > 50;
Since the INDEX hint in this example doesn't specify an index to use, the optimizer has the leeway to
select the best index—the index resulting in the least cost. The optimizer may also decide to use multiple
indexes and merge the results. The optimizer is unlikely to use a full table scan, however, though it will
use one if no indexes exist to be used! Note that if you have multiple indexes on a table and you just
specify the index hint, hoping that the query will perform better because the optimizer might use one of
the indexes, you may be in for a surprise sometimes. There is the possibility that the optimizer may