Creating indexes on Nullable Columns

advertisements

_____________________________________________________________________________________________________________________

Its usually wise to define the columns referenced in the where clause as NOT NULL so that indexing these columns can be effective. However, it can be worthwhile to use NULL values in an indexed column if 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.

Since NULLs are not stored in an index, an index created when the preceding conditions are true will be compact and can be used to locate rows quickly where the column contains a value. Ensure that the cost-based optimizer makes use of the appropriate index and uses a hint, if necessary.

_____________________________________________________________________________________________________________________

Website Stats