How Are NULLs Actually Indexed ? (Fascination) January 30, 2008Posted by Richard Foote in Index Internals, Indexing NULLs, Oracle General, Oracle Indexes.
A nice question by Jeff regarding how Oracle goes about indexing NULLs has prompted me to show how one could go about actually determining the answer. The basic question is are NULLs treated as just another column value and grouped accordingly or does Oracle have to somehow search through all the leaf blocks looking for all occurrences of these mysterious NULLs.
The answer is that NULLs are basically considered to be potentially the largest value possible by Oracle and so are all grouped and sorted together at the “end” of the index structure (assuming the column is the leading column in the concatenated index, else they’ll be listed last for each distinct column that precedes it in the index).
The fact that index range scans are just as efficient when searching for NULLs values as for any other value strongly supports this assumption, but how does one actually prove it ?
The first obvious thing to check would be to create a little table and associated index with a few rows and a few NULL column values thrown in and see the results of a SELECT … ORDER BY. One would expect the order of an ascending index to match the order of the resulting output. Indeed, NULL values are by default listed last in ORDER BY ascending listings suggesting they would likewise be grouped and sorted last within an index.
The next thing to check would possibly be to use the DUMP function to again see what Oracle is likely to do with NULL values. The DUMP function displays the raw decimal representation of the specific character (depending of course on the character-set) . For NULL values however, there’s actually nothing to display other than a NULL text to represent there’s nothing actually there.
The best place to check of course is within the actual index itself. By determining the actual block that stores our example index, we can perform an index block dump and look at the resultant trace file that describes a representation of the index block to see precisely how Oracle deals with NULLs within indexes.
A quick check of the HEADER_FILE and HEADER_BLOCK in DBA_SEGMENTS will give us the index segment header location.To find the associated index root/leaf block simply add 1 to the HEADER_BLOCK.
Dump the block via the
ALTER SYSTEM DUMP DATAFILE a BLOCK b
command and look at the trace file in USER_DUMP_DEST (where ‘a‘ represents the datafile id and ‘b‘ represents the block id determined from dba_segments).
The resultant output clearly shows that yes:
Leading column NULLs values are all grouped together
They are all listed at the “end” of the index structure
Any NULLs in the non-leading indexed columns are listed “last” for each distinct value in the leading columns in which they appear
Any index entry consisting of nothing but NULLs are not actually stored within the index
This NULLs Index Dump demo goes through this entire process with a little working example and describes the relevant section of the index block dump.
I spend some time discussing block dumps in my seminar as it’s an extremely useful tool when determining and learning how Oracle actually works.