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.

• 100% Practical Training

• Hands
  On
  Training

• 100%
  Job
  Gurantee

Interested to be a trainer?

jobs@greenstech.in

For corporate Training

corp@greenstech.in

Copyright © 2018 Greens Technologys. All Rights Reserved. Privacy Policy