Indexar NULL

La base de datos no incluye los registros dentro de un índice si todas las columnas indexadas son NULL. Eso significa que cada índice es un índice parcial así como un filtro where:

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

Consideremos un índice llamado EMP_DOB. Tiene solamente una columna: DATE_OF_BIRTH. Un registro sin valor para la columna DATE_OF_BIRTH no se incluye en este índice.

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

La sentencia insert no pone por defecto en NULL el campo DATE_OF_BIRTH así que el registro no se añadirá al índice EMP_DOB. Como consecuencia, el índice no podrá soportar una sentencia para los registros en los que DATE_OF_BIRTH IS NULL.

SELECT first_name, last_name
  FROM employees
 WHERE date_of_birth IS NULL

Sin embargo, el registro está insertado dentro del índice concatenado si por lo menos, una de las columnas no es NULL:

CREATE INDEX demo_null
          ON employees (subsidiary_id, date_of_birth)

El registro de arriba está agregado al índice porque la columna SUBSIDIARY_ID no es NULL. Este índice puede soportar una sentencia para todos los empleados con un ID secundario especifico aunque no haya valores para DATE_OF_BIRTH:

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)

Nótese que el índice cubre todo el filtro where; todos los filtros se usan como accesos de predicados durante el INDEX RANGE SCAN.

Se puede extender este concepto para la sentencia original con el fin de encontrar todos los registros donde DATE_OF_BIRTH IS NULL. Para eso, la columna DATE_OF_BIRTH tiene que ser la que esté más a la izquierda dentro del índice, así puede ser usada como predicado de acceso. Aunque no se necesita el segundo índice para esta sentencia, agregamos otra columna que nunca podrá ser NULL para asegurarnos de que el índice tenga todos los registros. Podemos usar cualquier columna que tenga una restricción NOT NULL, como SUBSIDIARY_ID para cumplir este objetivo.

Alternativamente, es posible utilizar una expresión que nunca será NULL. Eso asegurará que el índice tenga todos los registros -aun si DATE_OF_BIRTH es NULL.

DROP   INDEX emp_dob

CREATE INDEX emp_dob ON employees (date_of_birth, 'X')

Técnicamente, este índice es un índice basado en función. Este ejemplo también refuta el mito de que la base de datos de Oracle no puede indexar NULL.

Si te gusta mi manera de explicar, te encantará mi libro.