Greens Technologys Whatsapp
Greens Technologys Whatsapp

Oracle query not using an index

+91-89399 15577

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

Impressed with our Course Content?
Attend a Free Demo Session to Experience our Quality!

Oracle SQL Training in Chennai

Our Reviews 5 Star Rating: Recommended - Best IT Training in Chennai

5  out of 5  based on 12263 ratings.

Google          5 Star Rating: Recommended - Best IT Training in Chennai

Facebook      5 Star Rating: Recommended - Best IT Training in Chennai

UrbanPro       5 Star Rating: Recommended - Best IT Training in Chennai

Sulekha         5 Star Rating: Recommended - Best IT Training in Chennai

Yet5              5 Star Rating: Recommended - Best IT Training in Chennai

Justdial          5 Star Rating: Recommended - Best IT Training in Chennai


Oracle SQL Training in Chennai

Highlights of Oracle SQL Training

  • The presence of experts in Oracle SQL real time as the training faculty.
  • Provides the best learning environment.
  • Limited students per each batch.
  • Faculty‚Äôs interaction with each and every student for the better subject retention.
  • Well, affordable Course Fee.

Oracle SQL Training in Chennai


Oracle SQL Training in Chennai
best Oracle SQL Training center in chennai

"I thought I knew Oracle SQL until I took this course. My company sent me here against my will. It was definitely worth and I found out how many things I was doing wrong. Karthik is awesome. but i got a lot inspired by you. I will keep in touch and will always try to learn from you as much as I can. Thanks once again Karthik"


Greens Technologys Overall Reviews


Greens Technologys Overall Reviews

5 out of 5 based on 17,981 ratings. 17,981 user reviews.





Relevant Courses