Buscar valores NULL en forma eficiente

Un problema que veo seguidamente, se relaciona con la escritura de consultas que buscan valores NULL en una tabla. Este es un problema muy común.
A continuación vamos a ver algunas soluciones que podemos implementar para buscar los valores NULL en forma eficiente y evitar lecturas innecesarias de bloques de datos.

Veamos algunos ejemplos:

SQL_9iR2> CREATE TABLE test AS
  2  SELECT decode(mod(rownum,100),0,null,level) id, 'nom_'||level nom
  3  FROM dual
  4  CONNECT BY level <= 1000000 ;

Table created.

SQL_9iR2> EXEC dbms_stats.GATHER_TABLE_STATS(USER,'TEST') ;

PL/SQL procedure successfully completed.

Veamos la cantidad de valores NULL que tiene la tabla que creamos:

SQL_9iR2> SELECT count(*)
  2  FROM test
  3  WHERE id IS NULL ;

  COUNT(*)
----------
     10000

1 row selected.

Veamos cómo se ejecuta internamente esa consulta:

SQL_9iR2> explain plan for
  2  SELECT count(*)
  3  FROM test
  4  WHERE id IS NULL ;

Explained.

SQL_9iR2> @explains

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     1 |     7 |   304 |
|   1 |  SORT AGGREGATE      |             |     1 |     7 |       |
|*  2 |   TABLE ACCESS FULL  | TEST        | 10000 | 70000 |   304 |
--------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("TEST"."ID" IS NULL)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.16       0.16       3144       3150          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.17       0.16       3144       3150          0           1

Observamos que obviamente está realizando un Full Scan. Qué sucede si creamos un índice B*Tree?

SQL_9iR2> CREATE INDEX test_id_idx ON test(id) ;

Index created.

SQL_9iR2> EXEC dbms_stats.GATHER_TABLE_STATS(USER,'TEST',cascade=>true) ;

PL/SQL procedure successfully completed.

SQL_9iR2> explain plan for
  2  SELECT count(*)
  3  FROM test
  4  WHERE id IS NULL ;

Explained.

SQL_9iR2> @explains

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     1 |     7 |   304 |
|   1 |  SORT AGGREGATE      |             |     1 |     7 |       |
|*  2 |   TABLE ACCESS FULL  | TEST        | 10000 | 70000 |   304 |
--------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("TEST"."ID" IS NULL)

Porqué nuestra consulta no está accediendo a través del índice? por la simple razón de que los índices B*Tree no indexan los valores NULL. Como estamos realizando un COUNT buscando la cantidad de valores NULL de la tabla, el CBO sabe que si accede por índice, la consulta nos va a retornar un resultado erróneo ya que no existen valores NULL en el índice. Es por eso, que en vez de acceder por índice, realiza un Full Scan de la tabla. Esto tiene como consecuencia una lectura innecesaria de bloques por no haber accedido a través de un índice.

Una solución para éste problema sería crear un FBI (Function Based-Index) para indexar sólo los valores NULL de la columna:

SQL_9iR2> CREATE INDEX test_id_nulo_idx ON test(NVL(id,'nulo')) ;

Index created.

SQL_9iR2> EXEC dbms_stats.GATHER_TABLE_STATS(USER,'TEST',cascade=>true) ;

PL/SQL procedure successfully completed.

SQL_9iR2> explain plan for
  2  SELECT count(*)
  3  FROM test
  4  WHERE NVL(id,'nulo') = 'nulo' ;

Explained.

SQL_9iR2> @explains

--------------------------------------------------------------------------
| Id  | Operation            |  Name             | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                   |     1 |     7 |    27 |
|   1 |  SORT AGGREGATE      |                   |     1 |     7 |       |
|*  2 |   INDEX RANGE SCAN   | TEST_ID_NULO_IDX  | 10001 | 70007 |    27 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(NVL("TEST"."ID",'nulo')='nulo')

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          1         26          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          1         26          0           1

Veamos qué sucede si creamos un índice Bitmap:

SQL_9iR2> DROP INDEX test_id_idx ;

Index dropped.

SQL_9iR2> CREATE BITMAP INDEX test_id_bitmap ON test(id) ;

Index created.

SQL_9iR2> EXEC dbms_stats.GATHER_TABLE_STATS(USER,'TEST',cascade=>true) ;

PL/SQL procedure successfully completed.

SQL_9iR2> explain plan for
  2  SELECT count(*)
  3  FROM test
  4  WHERE id IS NULL ;

Explained.

SQL_9iR2> @explains

--------------------------------------------------------------------------------
| Id  | Operation                   |  Name            | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     1 |     7 |    44 |
|   1 |  SORT AGGREGATE             |                  |     1 |     7 |       |
|   2 |   BITMAP CONVERSION COUNT   |                  |       |       |       |
|*  3 |    BITMAP INDEX SINGLE VALUE| TEST_ID_BITMAP   |       |       |       |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("TEST"."ID" IS NULL)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          1          5          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          1          5          0           1

Observamos que también podemos utilizar índices Bitmap para buscar valores NULL ya que ésta clase de índices SI indexan éstos valores.
Lo importante a tener en cuenta si utilizamos éste índice es que cada modificación que se realiza sobre el índice, requiere un gran trabajo del sistema que si utilizamos índices B*tree. Por otro lado, si a ésto le sumamos las modificaciones concurrentes que se realizan sobre la columna indexada, puede llegar a ser mortal para el sistema.