de Martin LE TARNEC.

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, '1')

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.

Sugerencia

Agregar una columna que no puede ser NULL para indexar NULL como un valor.

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

Acerca del autor

Foto de Markus Winand

Markus Winand enseña eficientemente SQL, en casa y online. Minimiza el tiempo de desarrollo utilizando moderno SQL y optimiza el tiempo de ejecución con indexación inteligente. Para ello también ha publicado el libro SQL Performance Explained.

“Use The Index, Luke” de Markus Winand se halla bajo licencia Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
Aspectos legales | Contacto | SIN GARANTÍA | Marcas | Privacy | CC-BY-NC-ND 3.0 licencia