IS NULL doesn't always disable index

Coding "WHERE job IS NULL" is said to prevent you from being able to use an index, supposedly because indexes don't store null values.
 

Experimenting, I noticed that if a concatenated index has one column "A", that's defined as not null, it means every row will be in the index -- even if the other column "B" in the index is nullable and some rows have nulls.  What's surprising is if your SQL asks for rows "WHERE B is null" the index will be used to find these rows.
 

The optimizer is now smart enough (8.1.6) to know that if one column in a concatenated index is a not null column, then every row is guaranteed to be in the index, even if some of the values in the other column are null, and apparently the leaf blocks know which rows have nulls, allowing the index to be used to resolve the query.
 

Far from preventing the use of an index, if the SELECT only asks for columns in the concatenated index, Oracle will resolve the query using the index alone, no table access.
 

On a small test table Oracle wanted to full scan the table, so an INDEX hint was required to get this result. <BLOCKQUOTE
style="BORDER-LEFT: #000000 2px solid; MARGIN-LEFT: 5px; MARGIN-RIGHT: 0px; PADDING-LEFT: 5px; PADDING-RIGHT: 0px">        

        Received on Thu Sep 06 2001 - 18:37:19 CDT