Oracle Scratchpad

June 1, 2009

Hints and Nulls

Filed under: CBO,Hints,Ignoring Hints,Indexing,NULL,Troubleshooting — Jonathan Lewis @ 7:16 pm GMT Jun 1,2009

From time to time I check the site statistics to see if they give me any clues about why people are coming to blog – and recently I noticed that over the last year a particular referral from the OTN Database forum was had appeared fairly regularly – and it’s one that covers a small but significant optimizer detail that combines two crtical questions:  why is the optimizer not using my index and why is the optimizer ignoring my hint under the heading “Index hint does not work”.

I’ll leave you to read the thread – but the short answer is NULL.

A hint is illegal if using it could produce the wrong answer, and indexes where every column is nullable won’t necessarily reference every row in its table.

[Further reading on “ignoring hints”]


  1. I’ll say that is the reason I found your blog originally (might have been a google result, but it was the same topic)… but stuck around for the rest of the articles ever since.

    Comment by Nathan Neulinger — June 1, 2009 @ 11:02 pm GMT Jun 1,2009 | Reply

  2. I follow it for the tidbits.

    You know Oracle’s internals. And those are good to know. SO i come back, even though a great deal of it is over my head.

    Comment by Brian Tkatch — June 2, 2009 @ 11:51 am GMT Jun 2,2009 | Reply

  3. That is exactly what I was Asking to myself, thank you for the answer.

    Comment by Siwel aragon — June 2, 2009 @ 1:31 pm GMT Jun 2,2009 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by