Oracle Scratchpad

October 15, 2010

Good Nulls

Filed under: CBO,Function based indexes,Indexing,NULL,Tuning — Jonathan Lewis @ 6:17 pm BST Oct 15,2010

I’ve often been heard to warn people of the accidents that can happen when they forget about the traps that appear when you start allowing columns to be NULL – but sometimes NULLs are good, especially when it helps Oracle understand where the important (e.g. not null) data might be.

An interesting example of this came up on OTN a few months ago where someone was testing the effects of changing a YES/NO column into a YES/NULL column (which is a nice idea because it allows you to create a very small index on the YESes, and avoid creating a histogram to tell the optimizer that the number of YESes is small).

They were a little puzzled, though, about why their tests showed Oracle using an index to find data in the YES/NO case, but not using the index in the YES/NULL case. I supplied a short explanation on the thread, and was planning to post a description on the blog, but someone on the thread supplied a link to AskTom where Tom Kyte had already answered the question, so I’m just going to leave you with a link to his explanation.


  1. I used to recommend the values ‘M’ and NULL for gender columns – ok, maybe political incorrect – until some guy from a big agency told me that they have 5 genders to maintain :-)

    Comment by Uwe Hesse — October 16, 2010 @ 6:51 pm BST Oct 16,2010 | Reply

    • Uhm…5 genders…
      5 function based indexes can be of help?

      Comment by lascoltodelvenerdi — October 16, 2010 @ 8:03 pm BST Oct 16,2010 | Reply

    • 9 in UK

      Comment by Timur Akhmadeev — October 18, 2010 @ 12:34 am BST Oct 18,2010 | Reply

      • 9! 9! We got 9 from the UK, no one say more? :)

        Seriously, if we can not (or we want not) use bitmap indexes, I think that introduction of function based indexes can be of help.

        Comment by lascoltodelvenerdi — October 18, 2010 @ 10:27 pm BST Oct 18,2010 | Reply

        • lascoltodelvenerdi,

          The function-based index idea could be very useful here – especially if you use it to exclude the extremely popular values from the index (which, for the sake of argument, I’ll assume is the Male / Female).

          An index on something like (case sex when ‘Male’ then null when ‘Female’ then null else sex end) – could be very helpful.

          And in some cases your previous thought about multiple function-based indexes (perhaps using 11g’s option for virtual columns and indexes on virtual columns) would be more appropriate with definitions like: (case sex when ‘Unknown’ then ‘Unknown’ else null).

          Comment by Jonathan Lewis — October 19, 2010 @ 10:39 am BST Oct 19,2010

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