Force Oracle to use an index for IS [NOT] NULL predicates.

In my previous post, I mentioned how Oracle can't keep track of NULLS in an index, and consequently, cannot use an index to satisfy the IS NULL predicates (WHERE clauses).

There is a way of making Oracle do that though....

If you are trying to make Oracle use an index-on-nullable-column, here's a nifty trick from a reader-comment at http://www.dba-oracle.com/oracle_tips_null_idx.htm :

SQL> select count(1) from t where n is null;

COUNT(1)
----------
334

Execution Plan
---------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=3)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T' (Cost=3 Card=334 Bytes=1002)

SQL> create index tind on t(n, 1); ----> here 1 is just any arbitary value.

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'t',cascade=>true);

PL/SQL procedure successfully completed.

SQL> select count(1) from t where n is null;

COUNT(1)
----------
334

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=4)
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'TIND' (NON-UNIQUE) (Cost=2 Card=3
34 Bytes=1336)


Sandeep Redkar