Using index for IS NULL

Until some time I trully believed that Oracle doesn't store NULL in Oracle b-tree index. Apparently, this is not exactly true, i.e. false. Oracle doesn't store null values in index ONLY and ONLY when ALL columns in the index are null. If any of the index columns has not null value, the key is put in the index. My misconcept was so strong that I actually couldn't belive it until I dumped leaf block and saw myself that Oracle does store index keys with NULL columns.

Yesterday (or day before?) an interesting idea came to my mind while sitting on Tom Kyte's seminar here in Munich. Statement "IS NULL operator in filter predicates cannot use index becuase there is no guarantee that all not null values are actually within the index" is not correct either. It's quite easy to make optimizer using index range scan to seek rows with NULL columns. This might be expecially useful if it's not possible to change application source code. We just need create index including nullable column used in IS NULL and another column declared as NOT NULL. This can be any small not null column or dummy column created for that purpose. This way Oracle will always include NULL values of another column in the index.

And here is an example.

SQL> CREATE TABLE t (
  2    keycol NUMBER(10,0) NOT NULL,
  3    c CHAR(1) DEFAULT '1' NOT NULL ,
  4    ncol NUMBER(6,0) NULL,
  5    datacol NUMBER,
  6    CONSTRAINT t_pk PRIMARY KEY (keycol)
  7  );
Table created.

SQL> CREATE INDEX t_ind ON t (ncol,c);
Index created.

SQL> INSERT INTO t
  2     (keycol, ncol, datacol)
  3    SELECT ROWNUM, CASE
  4    WHEN DBMS_RANDOM.VALUE (0, 1000) < 1 THEN NULL
  5    ELSE TRUNC (DBMS_RANDOM.VALUE (0, 100000))
  6         END CASE, ROUND (DBMS_RANDOM.VALUE (1, 99999999999999999999999999))
  7    FROM (SELECT   NULL
  8         FROM DUAL
  9          CONNECT BY LEVEL <= 10000);
10000 rows created.

SQL> COMMIT ;
Commit complete.

SQL> BEGIN
  2    DBMS_STATS.gather_table_stats (USER, 'T');
  3  END;
  4  /
PL/SQL procedure successfully completed.

SQL> SET AUTOTRACE ON
SQL> SELECT *
  2    FROM t
  3   WHERE ncol IS NULL;

    KEYCOL C       NCOL    DATACOL
---------- - ---------- ----------
      1111 1            8.9202E+25
      4742 1            8.7495E+25
      5021 1            8.3964E+25
      8956 1            2.2568E+24
       269 1            6.8797E+25
       487 1            4.5290E+25
      7669 1            4.5904E+25
      7878 1            1.5710E+25

8 rows selected.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=8 Bytes=200)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=2 Card=8 Bytes=
          200)

   2    1     INDEX (RANGE SCAN) OF 'T_IND' (NON-UNIQUE) (Cost=2 Card=
          8)


Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
         16  consistent gets
          0  physical reads
         72  redo size
        903  bytes sent via SQL*Net to client
        651  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          8  rows processed

SQL> SET AUTOTRACE OFF

Now let's have a look at index leaf block. We need to find out the ROWID if the leaf block with null values on ncol. We don't need to dump the whole tree thanks to non-documented function sys_op_lbid. Thanks to Jonathan Lewis for mentioning that in Oracl-L thread.

SQL> SELECT object_id
  2    FROM user_objects
  3    WHERE object_name = 'T_IND';

 OBJECT_ID
----------
   1823665

SQL> SELECT DBMS_ROWID.rowid_relative_fno (myrowid) file_no,
  2          DBMS_ROWID.rowid_block_number (myrowid) block_no
  3    FROM (SELECT sys_op_lbid(1823665, 'L', t.ROWID) myrowid
  4             FROM t
  5            WHERE ncol IS NULL AND ROWNUM <>

   FILE_NO   BLOCK_NO
---------- ----------
         3      58521

SQL> ALTER SESSION SET tracefile_identifier = 'null_in_index';
Session altered.

SQL> ALTER SYSTEM DUMP DATAFILE 3 BLOCK 58521;
System altered.

Having a look at trace file (it will have 'null_in_index' appended to the name) we can actually see that all rows with NULL values in ncol column are referenced in the index:

... skip ...
Leaf block dump
===============
... skip ...
row#264[7872] flag: -----, lock: 0
col 0; NULL
col 1; len 1; (1):  31
col 2; len 6; (6):  00 c1 06 8e 00 0e
row#265[7884] flag: -----, lock: 0
col 0; NULL
col 1; len 1; (1):  31
col 2; len 6; (6):  00 c1 06 8f 00 d7
... skip ...
----- end of leaf block dump -----