Yo, como muchos otros, asumí y seguí a pies juntillas la máxima ‘no-uses-funciones-en-columnas-indexadas-en-las-cláusulas-where’. Esto siempre es dicho por parte de alguien que ’sabe’ Oracle (y suele ir acompañado de un cierto gesto grave y a menudo displicente).
Pero es que la documentación de Oracle también lo dice:
“Do not use SQL functions in predicate clauses or WHERE clauses. Any expression
using a column, such as a function having the column as its argument, causes the
optimizer to ignore the possibility of using an index on that column, even a unique
index, unless there is a function-based index defined that can be used.”
“No uses funciones SQL en cláusulas where o predicados. Cualquier expresión que use una columna (…) hace que el optimizador ignore la posibilidad de utilizar un índice en esa columna”
Pero esto es un ejemplo de lo que puede ocurrir: usamos una vieja (aunque en producción: vivita y coleando) base de datos Oracle 8i (Siebel):
(El modo del optimizador -”optimizer_mode”- es “choose” en el init.ora)
system@SIEBELDB> select * from V$VERSION;
BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
PL/SQL Release 8.1.7.4.0 - Production
CORE 8.1.7.0.0 Production
TNS for HPUX: Version 8.1.7.4.0 - Production
NLSRTL Version 3.4.1.0.0 - Production
system@SIEBELDB> set autotrace traceonly;
system@SIEBELDB> select max(DOM_DNS_FECHA_SOLICITUD)
2 from USERPROP.DOMINIO_DNS
3 where LOWER(DOM_DNS_DOMINIO) = LOWER(:nombreDominio);
Plan de Ejecución
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'DOMINIO_DNS'
Estadísticas
----------------------------------------------------------
0 recursive calls
4 db block gets
4225 consistent gets
4223 physical reads
0 redo size
211 bytes sent via SQL*Net to client
250 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Hasta aquí, todo según la máxima (los expertos siguen sonriendo displicentemente).
Pero, si cambiamos el modo del optimizador para que utilice costes:
system@SIEBELDB> alter session set optimizer_mode=first_rows;
Sesión modificada.
system@SIEBELDB> select max(DOM_DNS_FECHA_SOLICITUD)
2 from USERPROP.DOMINIO_DNS
3 where LOWER(DOM_DNS_DOMINIO) = LOWER(:nombreDominio);
Plan de Ejecución
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=3 Card=1 Bytes=138)
1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'IND_DOMINIO_DNS' (UNIQUE) (Cost=3 Card=832 Bytes=114816)
Estadísticas
----------------------------------------------------------
0 recursive calls
4 db block gets
1731 consistent gets
1695 physical reads
2416 redo size
226 bytes sent via SQL*Net to client
250 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
¡Toma! Así que Oracle no utiliza un índice si se utilizan funciones en columnas indexadas ¿no? Pues ESTÁ UTILIZANDO EL ÍNDICE (en FAST FULL SCAN).
Seguimos:
system@SIEBELDB> select DOM_DNS_FECHA_SOLICITUD
2 from USERPROP.DOMINIO_DNS
3 where LOWER(DOM_DNS_DOMINIO) = LOWER(:nombreDominio);
ninguna fila seleccionada
Plan de Ejecución
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=26 Card=832 Bytes=114816)
1 0 INDEX (FULL SCAN) OF 'IND_DOMINIO_DNS' (UNIQUE) (Cost=26 Card=832 Bytes=114816)
Estadísticas
----------------------------------------------------------
0 recursive calls
0 db block gets
1662 consistent gets
1659 physical reads
0 redo size
153 bytes sent via SQL*Net to client
210 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
0 rows processed
system@SIEBELDB>
¡Otra vez! Ahora LO ESTÁ UTILIZANDO (en FULL SCAN).
Los expertos han cambiado su sonrisa displicente por una expresión un poco distinta. Uno de ellos tiene una idea. Se le ilumina el rostro: “El índice es un ‘function based index’”. Ya está. Eso lo explica todo. Las sonrisas displicentes vuelven a aparecer.
Pero:
system@SIEBELDB> select OWNER,
2 INDEX_NAME,
3 INDEX_TYPE,
4 TABLE_OWNER,
5 TABLE_NAME,
6 TABLE_TYPE,
7 UNIQUENESS,
8 nvl(FUNCIDX_STATUS,'NO FBI')
9 from DBA_INDEXES WHERE INDEX_NAME = 'IND_DOMINIO_DNS'
10 /
OWNER INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME TABLE_TYPE UNIQUENES NVL(FUNC
--------- ---------------- ---------- ----------- ------------ ----------- --------- --------
USERPROP IND_DOMINIO_DNS NORMAL USERPROP DOMINIO_DNS TABLE UNIQUE NO FBI
Así que, de eso, nada. Un índice corriente y moliente.
En la desesperación, otra conjetura: “Hay una ‘check constraint’ que usa el ‘lower()’ sobre la columna y el optimizador, que es muy listo, la está utilizando en el ‘query plan’”.
Brillante, pero tampoco:
system@SIEBELDB> SELECT * FROM DBA_CONSTRAINTS WHERE OWNER='USERPROP' AND TABLE_NAME='DOMINIO_DNS';
OWNER CONSTRAINT_NAME C TABLE_NAME SEARCH_CONDITION
-------- --------------- - ----------- -------------------------------------
USERPROP PK_DOM_DNS_ID P DOMINIO_DNS
USERPROP SYS_C0019839 C DOMINIO_DNS "DOM_DNS_ID" IS NOT NULL
USERPROP SYS_C0019840 C DOMINIO_DNS "DOM_DNS_DOMINIO" IS NOT NULL
USERPROP SYS_C0019841 C DOMINIO_DNS "DOM_DNS_OPERACION" IS NOT NULL
USERPROP SYS_C0019842 C DOMINIO_DNS "DOM_DNS_FECHA_SOLICITUD" IS NOT NULL
USERPROP SYS_C0019843 C DOMINIO_DNS "DOM_DNS_ESTADO" IS NOT NULL
USERPROP SYS_C0019844 C DOMINIO_DNS "DOM_DNS_FECHA_ESTADO" IS NOT NULL
7 filas seleccionadas.
Las sonrisas se han mudado en miradas atónitas.
El quid de la cuestión está en la documentación (en mi opinión errónea) de Oracle. Una función sobre una columna indexada puede deshabilitar el uso de un índice como ‘ACCESS PREDICATE’ (RANGE SCANS), pero el optimizador SÍ PUEDE utilizar el índice como ‘FILTER PREDICATE’.
Los expertos han aprendido una cosa más de la que presumir.
Nota: “No hay que dar nada por sentado”.
(Con agradecimiento a Anurag Varma y ‘gamyers’)
Saludos.
Carlos.
Escrito por carlosal 
Escrito por carlosal 
Escrito por carlosal 
