Restricciones NOT NULL

Para indexar una condición IS NULL en la base de datos Oracle, el índice debe tener una columna que nunca ha de ser NULL.

Eso dicho así, no es suficiente, puesto que no existen entradas NULL. La base de datos tiene que asegurar que nunca pueda haber una entrada NULL. De otra manera, la base de datos debe asumir que la tabla tiene registros que no están en el índice.

El siguiente índice soporta solamente la sentencia si la columna LAST_NAME tiene una restricción NOT NULL:

DROP INDEX emp_dob

CREATE INDEX emp_dob_name
          ON employees (date_of_birth, last_name)

SELECT *
  FROM employees
 WHERE date_of_birth IS NULL

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

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("DATE_OF_BIRTH" IS NULL)

Al borrar la restricción NOT NULL, el índice no se usará para esta sentencia:

ALTER TABLE employees MODIFY last_name NULL

SELECT *
  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 |
----------------------------------------------------

Además de la restricción NOT NULL, la base de datos también sabe que las expresiones constantes como en la sección anterior no pueden llegar a ser NULL.

Un índice sobre una función definida por el usuario, sin embargo, no puede imponer una restricción NOT NULL sobre la expresión del índice:

CREATE OR REPLACE FUNCTION blackbox(id IN NUMBER) RETURN NUMBER
DETERMINISTIC
IS BEGIN
   RETURN id;
END

DROP INDEX emp_dob_name

CREATE INDEX emp_dob_bb 
    ON employees (date_of_birth, blackbox(employee_id))

SELECT *
  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 |
----------------------------------------------------

El nombre de la función BLACKBOX deja bien claro el hecho de que el opti­mi­zador no tiene idea de lo que hace la función (ver la sección “Sin distinción entre mayúscula y minúscula usando UPPER o LOWER”). Se puede ver cuando la función pasa los valores de entrada, pero para la base de datos es sola­mente una función que devuelve un número. La característica NOT NULL del parámetro se pierde. Aunque el índice debe tener todos los registros, la base de datos no sabe eso, por lo que no puede usar el índice para esta sentencia.

Si sabes que la función nunca devuelve NULL, como en este ejemplo, puedes cambiar la sentencia para que lo considere:

SELECT *
  FROM employees
 WHERE date_of_birth IS NULL
   AND blackbox(employee_id) IS NOT NULL

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

La condición adicional en el filtro where es siempre verdadera y por eso no cambiará el resultado. Sin embargo, la base de datos Oracle reconoce que solamente selecciona registros que deben estar en la definición del índice.

No existe, desafortunadamente, una manera de etiquetar una función que nunca devuelve NULL pero puedes mover la función llamando a una columna virtual (desde 11g) y poner una restricción NOT NULL sobre esta columna.

ALTER TABLE employees ADD bb_expression
      GENERATED ALWAYS AS (blackbox(employee_id)) NOT NULL

DROP   INDEX emp_dob_bb

CREATE INDEX emp_dob_bb 
    ON employees (date_of_birth, bb_expression)

SELECT *
  FROM employees
 WHERE date_of_birth IS NULL
   AND blackbox(employee_id) IS NOT NULL

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

La base de datos sabe que algunas funciones internas devuelven solamente NULL si NULL está provisto como parámetro de entrada.

DROP INDEX emp_dob_bb

CREATE INDEX emp_dob_upname 
    ON employees (date_of_birth, upper(last_name))

SELECT *
  FROM employees
 WHERE date_of_birth IS NULL

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

La función UPPER conserva la característica NOT NULL de la columna LAST_NAME. Quitando la restricción, sin embargo, deja el índice sin utilidad:

ALTER TABLE employees MODIFY last_name NULL

SELECT *
  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 |
----------------------------------------------------

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