Expansiones NVL

Muchas veces me he encontrado con queries relacionados a reportes de un ERP, en los cuales, debido a que los programadores no conocen en su totalidad los valores o número de parámetros a utilizar en cada ejecución, se opta por evaluar valores nulos y ejecutar una sola sentencia SQL, este tipo de condiciones las he visto de dos formas principalmente:

haciendo uso de un "OR"

(campo1 IS NULL OR campo1 = :b1)

o bien el uso de NVL o decode

campo1 = nvl(:b1, campo1)
campo1 = decode(:b1, null, campo1,:b1)

Pero ¿cuál de los dos pudiera ser la mejor opción?, a mi forma de ver, el NVL (o decode) es la mejor opción.

Para poder crear nuestro ejemplo, tenemos una tabla con 4 campos, el primero de baja cardinalidad, el segundo de muy alta cardinalidad y dos campos restantes con la misma distribución de datos. Crearemos unos índices y generaremos estadísticas.

SQL> CREATE TABLE prueba AS
  2  SELECT owner campo1,
  3    object_name campo2,
  4    created campo3,
  5    created campo4
  6  FROM dba_objects;

Tabla creada.

SQL> CREATE INDEX indice1 ON prueba(campo1,   campo2);

Índice creado.

SQL> CREATE INDEX indice3 ON prueba(campo3);

Índice creado.

SQL> CREATE INDEX indice4 ON prueba(campo4);

Índice creado.

SQL> BEGIN
  2    dbms_stats.gather_table_stats(ownname => 'SYS',   tabname => 'PRUEBA',   cascade => TRUE);
  3  END;
  4  /

Procedimiento PL/SQL terminado correctamente.

SQL> explain plan for
  2  SELECT *
  3  FROM prueba
  4  WHERE(campo1 IS NULL OR campo1 = :b1)
  5   AND(campo2 IS NULL OR campo2 = :b2);

------------------------------------
| Id  | Operation         | Name   |
------------------------------------
|   0 | SELECT STATEMENT  |        |
|*  1 |  TABLE ACCESS FULL| PRUEBA |
------------------------------------

En este caso el optimizador de costos, decide simplemente hacer un full table scan a la tabla. e incluso si se usa el hint "USE_CONCAT", el plan de ejecución no cambia.

Para nuestro segundo ejemplo usamos el NVL

---------------------------------------
| Id  | Operation                     |
---------------------------------------
|   0 | SELECT STATEMENT              |
|   1 |  CONCATENATION                |
|*  2 |   FILTER                      |
|*  3 |    TABLE ACCESS FULL          |
|*  4 |   FILTER                      |
|   5 |    TABLE ACCESS BY INDEX ROWID|
|*  6 |     INDEX SKIP SCAN           |
---------------------------------------

  2 - filter(:B2 IS NULL)
  3 - filter("CAMPO1"=NVL(:B1,"CAMPO1") AND "CAMPO2" IS NOT NULL)
  4 - filter(:B2 IS NOT NULL)
  6 - access("CAMPO2"=:B2)
      filter("CAMPO2"=:B2 AND "CAMPO1"=NVL(:B1,"CAMPO1"))

En este caso podemos notar dos cosas interesantes; la primera va relacionada con la descomposición de un sólo query a dos sentencias SQL. La primera opción que muestra, es el "peor de los casos", y la segunda opción, es el caso que tiene la más grande cardinalidad y por lo tanto, nos regresaría menos registros, en este caso un index skip scan parece ser la mejor opción. Lo segundo a notar, es la operación "FILTER", que significa, que sólo si se cumple la condición de filtro, se ejecutá la parte del plan que depende de esa operación. Un ejemplo claro de la situación de filter es:

SQL> SELECT *
  2  FROM prueba
  3  WHERE 1=2;
----------------------------
| Id  | Operation          |
----------------------------
|   0 | SELECT STATEMENT   |
|*  1 |  FILTER            |
|   2 |   TABLE ACCESS FULL|
----------------------------

  1 - filter(NULL IS NOT NULL)

Estadísticas
----------------------------------------------------
          1  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        461  bytes sent via SQL*Net to client
        389  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

Se puede ver el "FILTER" que dice "null is not null" lo cual siempre evalúa a Falso y por consiguiente, el Full Table Scan nunca se ejecuta (0 gets).

Volviendo a nuestro ejemplo del NVL, oracle decide expandir el plan de ejecución, apostando a que pudiera tener suerte e ir por pocos datos.

No hay forma de controlar de forma sencilla el plan de ejecución, ya que oracle siempre intentará obtener la menor cantidad de registros basado en las estadísticas.

El parámetro "_or_expand_nvl_predicate" es el que regula esta expansión de plan de ejecución:

SQL> alter session set "_or_expand_nvl_predicate"=false;

Sesión modificada.

SQL> explain plan for
  2  SELECT *
  3  FROM prueba
  4  WHERE campo1 = nvl(:b1,   campo1)
  5   AND campo2 = nvl(:b2,   campo2);

Explicado.

---------------------------
| Id  | Operation         |
---------------------------
|   0 | SELECT STATEMENT  |
|*  1 |  TABLE ACCESS FULL|
---------------------------

Al ser un parámetro escondido, no recomiendo por ningún motivo cambiarlo de true a false. Si por alguna razón quieren deshacerse del plan extendido, se puede usar el hint "NO_EXPAND".

Ahora, ¿qué sucede cuando tienes dos campos con la misma cardinalidad, y oracle tiene que expandir el query?, ¿qué índice o plan de ejecución va a tomar?

En primera instancia, hubiera creído que tomaría el primer índice en orden alfabético (ya que el optimizador en muchos casos selecciona de esa forma), pero la realidad es que para la expansión de predicados, lo que importa es el orden inverso en el predicado del query:

SQL> explain plan for
  2  SELECT *
  3  FROM prueba
  4  WHERE campo3 = nvl(:b1,   campo3)
  5    AND campo4 = nvl(:b2,   campo4);

-------------------------------------------------
| Id  | Operation                     | Name    |
-------------------------------------------------
|   0 | SELECT STATEMENT              |         |
|   1 |  CONCATENATION                |         |
|*  2 |   FILTER                      |         |
|*  3 |    TABLE ACCESS FULL          | PRUEBA  |
|*  4 |   FILTER                      |         |
|*  5 |    TABLE ACCESS BY INDEX ROWID| PRUEBA  |
|*  6 |     INDEX RANGE SCAN          | INDICE4 |
-------------------------------------------------


SQL> explain plan for
  2  SELECT *
  3  FROM prueba
  4  WHERE campo4 = nvl(:b1,   campo4)
  5    AND campo3 = nvl(:b2,   campo3);

-------------------------------------------------
| Id  | Operation                     | Name    |
-------------------------------------------------
|   0 | SELECT STATEMENT              |         |
|   1 |  CONCATENATION                |         |
|*  2 |   FILTER                      |         |
|*  3 |    TABLE ACCESS FULL          | PRUEBA  |
|*  4 |   FILTER                      |         |
|*  5 |    TABLE ACCESS BY INDEX ROWID| PRUEBA  |
|*  6 |     INDEX RANGE SCAN          | INDICE3 |
-------------------------------------------------

Hasta aquí, ya pudimos ver que de alguna forma podemos hacer que oracle tome un índice que nos interesa, pero en realidad, muchas veces el plan que oracle decide, no nos es suficiente, por ejemplo:

Imaginemos que tenemos un reporte que va hacia una tabla de facturación. El reporte permite la entrada de un rango de fecha y/o un rango de número de facturas. En este ejemplo, al usar NVL, oracle hará la expansión (seguramente) basado en el rango de número de facturas, ya que este campo suele tener mayor cardinalidad. Pero si nosotros sabemos de antemano, que el 90% de las ejecuciones son con un rango de fechas y no con un rango de número de facturas, ¿qué se puede hacer?

Podemos expandir manualmente el query.

Suponiendo que tenemos el siguiente query:

SQL> explain plan for
  2  SELECT *
  3  FROM prueba
  4  WHERE campo2 = nvl(:b1,   campo2)
  5    AND campo3 = nvl(:b2,   campo3);

-------------------------------------------------
| Id  | Operation                     | Name    |
-------------------------------------------------
|   0 | SELECT STATEMENT              |         |
|   1 |  CONCATENATION                |         |
|*  2 |   FILTER                      |         |
|*  3 |    TABLE ACCESS FULL          | PRUEBA  |
|*  4 |   FILTER                      |         |
|*  5 |    TABLE ACCESS BY INDEX ROWID| PRUEBA  |
|*  6 |     INDEX SKIP SCAN           | INDICE1 |
-------------------------------------------------

Podemos observar que se hace un skip scan del índice 1 o bien un FTS a la tabla, pero si sabemos de antemano que la mayoría de las veces la variable :b1 vendrá nula y no así :b2, lo mejor sería rehacer nuestro query para que al evaluar las variables, pueda tomar un mejor plan de ejecución.

Esto se puede lograr de la siguiente manera

SQL> explain plan for
  2  SELECT *
  3  FROM prueba
  4  WHERE :b1 is null
  5    AND campo3 = nvl(:b2,   campo3)
  6  union all
  7  SELECT *
  8  FROM prueba
  9  WHERE :b1 is not null
 10    AND campo2 = nvl(:b1,   campo2)
 11    AND campo3 = nvl(:b2,   campo3);

--------------------------------------------------
| Id  | Operation                      | Name    |
--------------------------------------------------
|   0 | SELECT STATEMENT               |         |
|   1 |  UNION-ALL                     |         |
|   2 |   CONCATENATION                |         |
|*  3 |    FILTER                      |         |
|*  4 |     TABLE ACCESS FULL          | PRUEBA  |
|*  5 |    FILTER                      |         |
|   6 |     TABLE ACCESS BY INDEX ROWID| PRUEBA  |
|*  7 |      INDEX RANGE SCAN          | INDICE3 |
|   8 |   CONCATENATION                |         |
|*  9 |    FILTER                      |         |
|* 10 |     TABLE ACCESS FULL          | PRUEBA  |
|* 11 |    FILTER                      |         |
|* 12 |     TABLE ACCESS BY INDEX ROWID| PRUEBA  |
|* 13 |      INDEX SKIP SCAN           | INDICE1 |
--------------------------------------------------


  3 - filter(:B1 IS NULL AND :B2 IS NULL)
  4 - filter("CAMPO3" IS NOT NULL)
  5 - filter(:B1 IS NULL AND :B2 IS NOT NULL)
  7 - access("CAMPO3"=:B2)
  9 - filter(:B1 IS NOT NULL AND :B1 IS NULL)
 10 - filter("CAMPO3"=NVL(:B2,"CAMPO3") AND "CAMPO2" IS NOT NULL)
 11 - filter(:B1 IS NOT NULL AND :B1 IS NOT NULL)
 12 - filter("CAMPO3"=NVL(:B2,"CAMPO3"))
 13 - access("CAMPO2"=:B1)
      filter("CAMPO2"=:B1)

Y de esta forma tenemos 4 filters. El primero, si :b1 y :b2 son nulos, entonces ejecuta un FTS. El segundo, si :b1 es nulo y :b2 no es nulo, utiliza el índice 3. El tercero, si :b1 es nulo y :b1 no es nulo, siempre se iguala a false por lo cual no se ejecuta. El cuarto evalúa que :b1 no sea nulo, siendo este, el mejor plan de ejecución y presenta el acceso a través del Index Skip Scan del índice 1.

Me ha tocado ver a diversos programadores que les es más fácil programar código dinámico y presentar las sentencias SQL con los predicados que no tienen un valor nulo. Así que si alguien tiene un código que maneje un cursor de referencia dinámico, o un armado de un query de forma dinámica y lo quiere compartir con nosotros, es más que bienvenido.