When an indexed column is NULL, or when all columns in a concatenated index are NULL, the row concerned will not have entry in a B*-Tree index. This is a fundamental and important concept because it is, therefore, not possible to use a B*-Tree index to find NULL values, although it is possible to find a value that is NOT NULL.
It’s therefore usually wise to define the columns that might be referenced in WHERE clauses as NOT NULL so that indexing these columns can be effective. However, it can be worthwhile using NULL values in an indexed column if some or all the following conditions apply:
- The column is almost always NULL.
- We never want to find rows where the column is NULL.
- We do want to search for rows where the column is NOT NULL.
- We want to minimize the space required by the index.
Because NULLs are not stored in an index, an index created when the preceding conditions are true will be very compact and can be used to quickly locate rows where the column contains a value.
Index merges might be substantially slower in Oracle Standard Edition because the Standard Edition cannot leverage the bitmap conversion approach to index merge used in the Enterprise Edition.
-- Oracle®Performance Survival Guide, pg. 118
