Índices basados en funciones. Problemas en migraciones de versión.

Una base de datos Oracle 9i tenía una tabla con un campo fecha y un índice basado en función para localizar los valores nulos. La función NVL asignaba un valor 'NULO' a los campos vacíos, con el fin de localizar estas filas nulas, y para no dar un conflicto de tipos, convertía la fecha a TO_CHAR.

De este modo, la consulta se ejecutaba así:

Ejecución en Oracle 9i

SQL>  create index fbi_fecha on test(NVL(TO_CHAR(FECHA),'NULO'));

Índice creado.

SQL> explain plan for

  2  select * from test

  3  where NVL(TO_CHAR(FECHA),'NULO') = 'NULO';

Explained.

SQL> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| Id  | Operation                   |  Name       | Rows  | Bytes | Cost  |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |             |   130 |  1040 |     5 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST        |   130 |  1040 |     5 |

|*  2 |   INDEX RANGE SCAN          | FBI_FECHA   |   130 |       |     3 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access(NVL(TO_CHAR("TEST"."FECHA"),'NULO')='NULO')

Note: cpu costing is off

15 rows selected.

No obstante, al migrar esta base de datos a Oracle 11g, esta misma sentencia no usaba el índice basado en función, y hacía un acceso FULL SCAN.

Ejecución en Oracle 11g

SQL>  create index fbi_fecha on test(NVL(TO_CHAR(FECHA),'NULO'));

Índice creado.

SQL> explain plan for

  2  select * from test

  3  where NVL(TO_CHAR(FECHA),'NULO') = 'NULO';

Explicado.

SQL> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------

Plan hash value: 1357081020

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      | 10681 | 85448 |   571   (9)| 00:00:07 |

|*  1 |  TABLE ACCESS FULL| TEST | 10681 | 85448 |   571   (9)| 00:00:07 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(NVL(TO_CHAR(INTERNAL_FUNCTION("FECHA")),'NULO')='NULO')

13 filas seleccionadas.

El motivo: aunque la sintaxis de creación de los índices ha sido la misma, internamente su almacenamiento es ligeramente distinto. Mientras en Oracle9i se almacena la función TO_CHAR sin formato de máscara, en Oracle11g se define con un formato de máscara por defecto.

Ejecución en Oracle 9i

SQL> select index_name, column_expression

  2  from user_ind_expressions

  3  where index_name='FBI_FECHA';

INDEX_NAME                     COLUMN_EXPRESSION

------------------------------ -----------------------------------------------

FBI_FECHA                      NVL(TO_CHAR("FECHA"),'NULO')

Ejecución en Oracle 11g

SQL> select index_name, column_expression

  2  from user_ind_expressions

  3  where index_name='FBI_FECHA';

INDEX_NAME                     COLUMN_EXPRESSION

------------------------------ -----------------------------------------------

FBI_FECHA                      NVL(TO_CHAR("FECHA",'DD/MM/RR'),'NULO')

De modo que, para que en Oracle 11g el optimizador considere el uso del íncide basado en función FBI_FECHA, la función de filtrado debe ser idéntica y debe incluir la máscara 'DD/MM/RR' que se ha añadido a la expresión del índice.

Ejecución en Oracle 11g

SQL> explain plan for

  2  select * from test

  3  where NVL(TO_CHAR(FECHA,'DD/MM/RR'),'NULO') = 'NULO';

Explicado.

SQL> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------------------

Plan hash value: 3576847778

-----------------------------------------------------------------------------------------

| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |           |   130 |  2210 |     5   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST      |   130 |  2210 |     5   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | FBI_FECHA |   130 |       |     3   (0)| 00:00:01 |

-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access(NVL(TO_CHAR(INTERNAL_FUNCTION("FECHA"),'DD/MM/RR'),'NULO')='NULO')

14 filas seleccionadas.

¿Te ha parecido interesante esta entrada? 
Si es así, échale un ojo a mi libro sobre Optimización SQL en Oracle.