Tuning “IS NULL” Query Criteria with Clever Oracle Indexing

Some poor performing queries I run into at customers are ones that involve “IS NULL” criteria similar to:

UPDATE table1
 SET col1 = :1
 WHERE col1 IS NULL;
 
UPDATE STATEMENT Optimizer=ALL_ROWS (Cost=144 Card=3 Bytes=195)
 UPDATE OF ‘TABLE1?
 TABLE ACCESS (FULL) OF ‘TABLE1? (TABLE) (Cost=144 Card=3 Bytes=195)

Oracle does not store NULL values in indexes, so even if an index did exist on the TABLE1.COL1 column, it would not be usable. Many customers believe there is no way to tune this type of statement and that a full table scan is inevitable.

However, there is a helpful trick with indexes to help tune this statement. Create an index and add a constant value to the end of the index so NULL values are stored.

CREATE INDEX t1 ON table1(col1, -1);
 
UPDATE table1
 SET col2 = :1
 WHERE col1 IS NULL;
 
UPDATE STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=189 Bytes=756)
 UPDATE OF ‘TABLE1?
 INDEX (RANGE SCAN) OF ‘T1? (INDEX) (Cost=3 Card=189 Bytes=756)

The cost is definitely lower and when I ran tests with these two options, it took just over 25 seconds to run the orginal statement 10,000 times while it took 2 seconds to run the better performing version. I also tested this new index against a similar index that does not contain the constant second column and both performed identical.