Funciones en columnas indexadas en cláusulas ‘WHERE’

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.

8 respuestas a Funciones en columnas indexadas en cláusulas ‘WHERE’

  1. Rheicoss dice:

    Interesante, pero igual no me consta mucho, hay muchos factores que determinan el coste y el camino que usa el optimizador de oracle para decidir si usa o no tal objeto (ej. datos en las tablas, etc) Igual si tengo un tiempo lo analizare en detalle, lo cual no creo que pase 😛

    De igual manera se agradece el dato.

    Saludos.

  2. carlosal dice:

    No entiendo muy bien eso de ‘no me consta mucho’.

    El asunto no era discutir cómo calcula el coste el optimizador ni su ‘camino’. Es más fácil que todo eso: Oracle dice en su documentación oficial que no se utilizan los índices cuando se usan funciones en predicadois y cláusulas ‘WHERE’ sobre las columnas indexadas. Y hemos visto que no es así.

    Un saludo.

    Carlos.

  3. Jose Gonzales dice:

    Primero si el optimizador esta en choose, quiere decir que esta en costos, si usted lo cambia a firtsrows no lo esta cambiando a costos, sigue en costos solo le dice al optimizador que escoja el mejor camino para sacar el primer registro no para resolver la consulta en volumen. Y para sacar el primer registro asi sea que se use una funcion siempre es mejor el indices, debe ver las cosas en su contexto general y no en una simple particularidad.

  4. carlosal dice:

    “Primero si el optimizador esta en choose, quiere decir que esta en costos”

    No. En absoluto.
    Si el optimizador está en CHOOSE… está en CHOOSE (si sabes lo que esto significa). Como se puede ver en el primer query plan NO HAY CIFRAS DE COSTES, por lo que el optimizador ha escogido RULES. Cuando pasamos a FIRST_ROWS estamos obligando al optimizador a pasar a COSTES (como se ve muy bien en el segundo query plan con sus cifras de costes y cardinalidades). Aunque lo mismo habríamos conseguido utilizando cualquier otro ‘hint’ que obligue al optimizador a pasar a costes (ALL_ROWS, por ejemplo).

    “Y para sacar el primer registro asi sea que se use una funcion siempre es mejor el indices”

    Tampoco. El uso del índice será mejor… cuando lo sea. No siempre. (Piensa en una tabla con un solo registro: ¿Qué es mejor: ir al índice y del índice ir a la tabla o leer los datos de la tabla -pequeña- directamente?)

    Una vez más: El objeto del comentario no era discutir el optimizador. El objeto del comentario es poner de manifiesto que Oracle en su documentación oficial dice que el optimizador ignorará el uso de índices si en los predicados y ‘WHEREs’ se utilizan funciones sobre columnas indexadas. Pero el optimizador SÍ las usa (si lo estima conveniente).
    Esto ha llevado a mucha gente a dar por sentado ese funcionamiento, que resulta no ser cierto como hemos visto.

    Saludos.

    Carlos.

  5. Leonardo dice:

    No podes hablar asi de los que saben de Oracle (que por cierto, un experto jamas diria lo que dice en esta página) cuando en la documentación dice claramente que si tenes una función en una columna indexada (indice comun), Oracle no usa ningun indice para esa columna; eso no quiere decir que no se pueda crear un indice de una función (que también está documentado). Por eso, no se de que te sorprendes… primero tendrias que haber leidos la documentación y despues (en todo caso) criticar a los que “saben”.
    Espero que no te consideres un experto en Oracle.

  6. carlosal dice:

    Leonardo:
    Primero: no me considero un ‘experto’ en Oracle.
    Segundo: la documentación es muy clara al respecto… y el optimizador no hace lo que dice la documentación que hace:
    La documentación dice que si utilizamos una función (en nuestro caso lower()) sobre una columna indexada, esto hará que el optimizador ignore la posibilidad de utilizar un índice a no ser que exista un ‘function based index’ (en nuestro caso NO existía) que pueda ser utilizado.
    Hemos visto que el optimizador SÍ utiliza un índice (IND_DOMINIO_DNS) a pesar de que existe una función en el ‘WHERE’ de la consulta. Esto es JUSTO LO CONTRARIO de lo que dice la documentación.
    Tercero: creo que tienes algún problema con el idioma castellano, porque al parecer no has entendido muy bien el texto. Tú mismo lo dices: “cuando en la documentación dice claramente que si tenes una función en una columna indexada (indice comun), Oracle no usa ningun indice para esa columna”. Pues justo eso es lo que NO está haciendo: está utilizando el índice IND_DOMINIO_DNS.
    Cuarto: antes de escribir leí la documentación (como siempre hago) y por eso LA CITÉ LITERALMENTE.
    Quinto: es un honor para mí que alguien de Oracle Corp. en California lea este ‘blog’ y publique comentarios en él. Aunque los modos y maneras que utilice disten mucho de ser lo que se dice ‘educados’. Pero en fin, como dijo el torero Rafael Guerra “Guerrita” (aunque otros dicen que fue Rafael Gómez Ortega, “El Gallo”): “Hay gente pa tó”.
    Saludos.
    Carlos.

  7. Jesús dice:

    Querido Carlos,

    este post es muy viejo, y siento no haberlo visto en su día. Aún así, me resisto a pasar por encima de él sin quitarme el sombrero: “Chapó”.

    Tienes más razón que un santo, lo que ocurre que “no hay peor ciego que el que no quiere ver”, y de ahí la cantidad de “demostraciones” que te han colocado de que estabas equivocado.

    No cambies. Se siempre analítico, y no des nunca nada por sentado.

    Enhorabuena.

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: