First page Back Continue Last page Graphics
NULL and B-Tree Indexes
B-trees do not store entries for NULL
But can use to advantage
Notes:
Although b-tree indexes do not contain entries for NULL (and thus ‘IS NULL’ is not an indexable predicate), this can be used to our advantage in some situations. Suppose you have a column in a large table that contains mostly NULL and must return excellent response time when searched for the non-null values. If you create a b-tree index on this column it will be relatively small (containing only entries for the rows that contain values in the column) and should be very effective in searches for value in the column.
The example above shows creating an index on BIG.STATUS. While the table contains about 500,000 rows, only 10 rows contain value in STATUS:
SQL> update big set status = null;
486400 rows updated.
SQL> update big set status = 'A' where rownum<=10;
10 rows updated.
SQL> create index big_status on big(status) nologging nos
Index created.
Refer to the supplied script null_and_btree.sql for a copy of this demonstration.