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.
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 UTC Oct 16,2010 |
Uhm…5 genders…
5 function based indexes can be of help?
Comment by lascoltodelvenerdi — October 16, 2010 @ 8:03 pm UTC Oct 16,2010 |
9 in UK…
Comment by Timur Akhmadeev — October 18, 2010 @ 12:34 am UTC Oct 18,2010 |
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 UTC Oct 18,2010 |
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 UTC Oct 19,2010