Question:
I have a query and even though an optimal index exists, Oracle is
choosing not to use the index.
I can force the index to be used with an INDEX hint, but I want
the query to run properly without hints.
How do I get the optimizer to work properly in 10g?
Answer:
Oracle SQL not using an index is a common complaint, and
it’s often because the optimizer thinks that a full-scan is cheaper than
index access. Oracle not
using an index can be due to:
·
Bad/incomplete statistics – Make sure to re-analyze the
table and index with dbms_stats to ensure that the optimizer has good
metadata.
·
Wrong optimizer_mode – The first_rows optimizer mode is
to minimize response time, and it is more likely to use an index than
the default all_rows mode.
·
Bugs – See these important notes on
optimizer changes in 10g
that cause Oracle not to use an index.
·
Cost adjustment – In some cases, the optimizer will
still not use an index, and you must
decrease optimizer_index_cost_adj.
For testing a query that does not use an index, try testing it with a
RULE hint (select /*+ RULE */ col1).
If the query uses the index with a RULE hint, you know that it’s
an issue with the CBO.
