monline

Most people (using Oracle DBs) sooner or later face the question: are NULL values indexed in the DB or do queries use full tables scans if you include an "IS NULL" condition in them ... even if the column being evaluated is indexed?

I've read a number of different articles about this and most stated that "IS NULL" conditions use full table scans no matter what.

The truth (~ authentic answer ... at least to me ) comes from Tom Kyte. He explains the problem in detail in his blog entry titled "Something about nothing...".

The point is: if all columns -that are involved in the index to be used- are nullable, then it will take a full table scan. However if there is only one NOT NULL column involved in the index, then "IS NULL" conditions on any nullable columns in the index will use the index.

I fully agree with Tom that any index should involve at least one NOT NULL column. Actually most columns should be NOT NULL ... or at least one should check from time to time (during support phase of a system's lifetime) whether there're any nullable columns that are actually NOT NULL.