2012-04-17Indexing NULL in the Oracle Database

The Oracle database does not include rows into an index if all indexed columns are NULL. That means, every index is a partial index—like having a where clause:

CREATE INDEX idx
          ON tbl (A, B, C, ...)
       WHERE A IS NOT NULL
         AND B IS NOT NULL
         AND C IS NOT NULL
             ...;         

Consider the EMP_DOP index. It has only one column: the DATE_OF_BIRTH. A row, that does not have a DATE_OF_BIRTH value set, is not added to this index.

INSERT INTO employees ( subsidiary_id, employee_id
                      , first_name   , last_name
                      , phone_number)
               VALUES ( ?, ?, ?, ?, ? );   

The DATE_OF_BIRTH is not specified and defaults to NULL—hence, the record is not added to the EMP_DOB index. As a consequence, the index cannot support a query for records where the DATE_OF_BIRTH IS NULL:

SELECT first_name, last_name
  FROM employees
 WHERE date_of_birth IS NULL;

----------------------------------------------------
| Id | Operation         | Name      | Rows | Cost |
----------------------------------------------------
|  0 | SELECT STATEMENT  |           |    1 |  477 |
|* 1 |  TABLE ACCESS FULL| EMPLOYEES |    1 |  477 |
----------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("DATE_OF_BIRTH" IS NULL)

Nevertheless, the record is inserted into a concatenated index if at least one index column is not NULL:

CREATE INDEX demo_null
          ON employees (subsidiary_id, date_of_birth);

The above created row is in the index because the SUBSIDIARY_ID is not NULL. This index can therefore support a query for all employees of a specific subsidiary, not having a DATE_OF_BIRTH value:

SELECT first_name, last_name
  FROM employees
 WHERE subsidiary_id = ?
   AND date_of_birth IS NULL;

--------------------------------------------------------------
| Id | Operation                   | Name      | Rows | Cost |
--------------------------------------------------------------
|  0 | SELECT STATEMENT            |           |    1 |    2 |
|  1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES |    1 |    2 |
|* 2 |   INDEX RANGE SCAN          | DEMO_NULL |    1 |    1 |
--------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("SUBSIDIARY_ID"=TO_NUMBER(?) 
          AND "DATE_OF_BIRTH" IS NULL)

Please note that the index covers the entire where clause; all predicates are used as access predicates during the INDEX RANGE SCAN.

We can also use this concept for the original query to find all records where DATE_OF_BITH IS NULL. The DATE_OF_BIRTH column has to be at the first position in the index, so it can be used as access predicate. Although we do not need a second index column for the query itself, we have to add one to make sure all rows are covered by the index. You can use any column that has a NOT NULL constraint for that purpose.

But you can also use a constant expression that can never be NULL. That makes sure that the index has all rows—even if DATE_OF_BIRTH IS NULL.

DROP   INDEX emp_dob;
CREATE INDEX emp_dob ON employees (date_of_birth, '1');

Technically, this index is a function based index.