martes, febrero 04, 2014

Í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.

No hay comentarios: