Databases Reference
In-Depth Information
Just use the
TRUNC
function for the
SYSDATE
values and replace the equality operator with a range
comparison operator such a
GREATER THAN
or
LESS THAN
in order to eliminate the need to apply the
TRUNC
function on the
START_DATE
column. That is, instead of this statement
select * from employees where trunc(start_date) = trunc(sysdate);
use this statement
select * from employees where start_date >= trunc(sysdate)
and start_date < trunc(sysdate+1);
The removal of the
TRUNC
function on the indexed column
START_DATE
will make the optimizer use the
index on that column.
Skipping the Leading Portion of an Index
If you have a composite index on two or more columns and you don't use the leading portion of the
index in your query, the optimizer is very likely to ignore the index on the table and do a full a table scan.
Let's say you have an index on the columns A, B in the table
mytab
, with A the leading column. If you
then issue a SQL statement such as
select * from mytable where b=999
, the database ignores the index
on the columns A, B because it has to check every single index entry in the table for all possible values of
A.
Note that if you issue a query such as
select A, B from mytable
, the optimizer is more likely to use
the index on (A,B) because it realizes that both columns are part of the index. An index being much more
compact than the table, and because the database can get all the values that are requested by the query
from the index itself, the optimizer likely will perform a fast full scan of the index.
Even if a query leaves out the leading portion of the index, the database can still use the index,
provided the leading column in a composite index has very few distinct values. In such a case, the
database will perform an index skip scan, as explained in Chapter 5.
There can be multiple leading columns in the index prior to the column used in the predicate when
the optimizer chooses a skip scan. We've seen cases where as many as six columns preceded the
predicate column and the optimizer still used a skip scan. DBAs tend to think of skip scans as only being
feasible when the number of values in the leading columns is small, but “small” is a relative thing. If the
optimizer deems a skip scan to be a lower cost than a full table scan, then the optimizer will choose the
skip scan. In the end, it's the cost estimate that matters and that drives the choice.
Forcing the Optimizer to Use an Index
You can force the optimizer to use an index by using several techniques. You can use an
INDEX
hint
(there are several of these, as explained later) to tell the optimizer to use a specific index or even any
index the optimizer finds best. You can also adjust the initialization parameter
optimizer_index_cost_adj,
which, by making a direct adjustment to the computed cost of the index
access, makes it very likely to cause the database to use an index instead of doing a full table scan.
