Why is Oracle query not using an index?

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.

---