Indexing NULL in the Oracle Database


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

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

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

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

The insert statement does not set the DATE_OF_BIRTH so it 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 DATE_OF_BIRTH IS NULL:

SELECT first_name, last_name
  FROM employees
 WHERE date_of_birth IS NULL
Try online at SQL Fiddle----------------------------------------------------
| 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 added to the index because the SUBSIDIARY_ID is not NULL. This index can thus support a query for all employees of a specific subsidiary that have no 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 filters are used as access predicates during the INDEX RANGE SCAN.

About our book “SQL Performance Explained”
Probably the best book on SQL performance I've read
Guillaume Lelarge on Amazon.co.uk (5 stars)

We can extend this concept for the original query to find all records where DATE_OF_BIRTH IS NULL. For that, the DATE_OF_BIRTH column has to be the leftmost column in the index so that it can be used as access predicate. Although we do not need a second index column for the query itself, we add another column that can never be NULL to make sure the index has all rows. We can use any column that has a NOT NULL constraint, like SUBSIDIARY_ID, for that purpose.

Alternatively, we can use a constant expression that can never be NULL. That makes sure 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. This example also dis­proves the myth that the Oracle database cannot index NULL.

Tweet this tip

Tip

Add a column that cannot be NULL to index NULL like any value.

If you like my way of explaining things, you’ll love my book.

About the Author

Photo of Markus Winand
Markus Winand tunes developers for high SQL performance. He also published the book SQL Performance Explained and offers in-house training as well as remote coaching at http://winand.at/

?Recent questions at
Ask.Use-The-Index-Luke.com

3
votes
2
answers
268
views

pagination with nulls

yesterday Markus Winand ♦♦ 771
pagination
2
votes
1
answer
1.9k
views
0
votes
2
answers
1.1k
views

different execution plans after failing over from primary to standby server

Sep 17 at 11:46 Markus Winand ♦♦ 771
oracle index update