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