Filtros de predicados usados intencionalmente sobre índices

A menudo, los predicados de filtro sobre el índice indican un uso incorrecto del índice causado por un orden de columnas incorrecto en un índice concatenado. Sin embargo, los predicados de filtro sobre el índice pueden utilizarse para una buena causa: no para mejorar el rendimiento del rango escaneado, sino para agrupar el acceso de forma consecutiva a los datos.

Los predicados sobre el filtro where que no pueden servir como predicado de acceso son buenos candidatos para esta técnica:

SELECT first_name, last_name, subsidiary_id, phone_number
  FROM employees
 WHERE subsidiary_id = ?
   AND UPPER(last_name) LIKE '%INA%'

Recuerda que las expresiones LIKE empezando por un comodín no pueden utilizar el árbol del índice. Eso significa que indexar LAST_NAME no reduce el rango escaneado del índice; no importa si se indexa por LAST_NAME o UPPER(last_name). Por lo tanto, esta condición no es un buen candidato para indexar.

Sin embargo, la condición sobre SUBSIDIARY_ID es apropiada para indexar. Así que no se necesita agregar un nuevo índice porque la columna SUBSIDIARY_ID es todavía la primera columna en el índice (clave primaria).

En el plan de ejecución anterior, el valor del costo se incrementó cien veces desde el INDEX RANGE SCAN a la subsiguiente operación TABLE ACCESS BY INDEX ROWID. En otras palabras, el acceso a la tabla es lo que causa más trabajo. En realidad, es un patrón común y no un problema por sí mismo. Sin embargo, es el factor más significativo sobre el tiempo global de ejecución de esta sentencia.

El acceso a la tabla no es necesariamente un cuello de botella si las filas leídas están almacenadas en un sólo bloque de la tabla porque la base de datos puede buscar todas las filas con una sola operación de lectura. En cambio, si las mismas filas están extendidas a través muchos bloques, el acceso a la tabla puede llegar a ser un grave problema de rendimiento porque la base de datos tiene que buscar muchos bloques con el fin de recuperar todas las filas. Eso significa que el rendimiento depende de la distribución física de las filas leídas: en otras palabras, eso depende de la agrupación de las filas.

De hecho, es posible mejorar el rendimiento de las sentencias volviendo a ordenar las filas de la tabla para que correspondan al orden del índice. Sin embargo, este método es aplicable sólo de manera puntual, porque las filas de la tabla sólo tienen un único orden posible. Eso significa que se puede optimizar la tabla para un solo índice. Aunque se podría escoger un solo índice por el cual se optimizara la tabla, se trata de una tarea complicada porque la mayoría de las bases de datos ofrecen únicamente herramientas básicas para llevarla a cabo. A fin de cuentas, la denominada secuencia de fila conlleva tener un enfoque poco factible.

Y es aquí donde el poder de la indexación (agrupación de los datos) cobra sentido. Se pueden agregar muchas columnas en un índice así que automá­ticamente están almacenadas en un orden bien definido. Gracias a eso, el índice ya es una herramienta sencilla para agrupar los datos.

Para aplicar este concepto sobre la sentencia anterior, se debe extender el índice para cubrir todas las columnas del filtro where, incluso aunque el rango escaneado del índice no se reduzca:

CREATE INDEX empsubupnam ON employees
       (subsidiary_id, UPPER(last_name))

La columna SUBSIDIARY_ID es la primera columna del índice así que se puede usar como predicado de acceso. La expresión UPPER(last_name) cubre el filtro LIKE como predicado de filtro del índice. Indexando los datos con mayúsculas ahorraría algunos ciclos de CPU durante la ejecución, pero un índice “directo” sobre LAST_NAME funcionaría también bien. Se explicará con más detalle en la siguiente sección.

El nuevo plan de ejecución muestra casi las mismas operaciones que antes. Sin embargo, el valor del costo bajó de forma significativa. En la infor­mación de los predicados, se puede observar que el filtro LIKE aún se aplica durante el INDEX RANGE SCAN. Las filas que no satisfacen el filtro LIKE son descartadas de inmediato. El acceso a la tabla ya no tendrá cualquier predicado de filtro. Eso significa que no se cargan las filas que no cumplen con el filtro where.

La diferencia entre los dos planes de ejecuciones es claramente visible en la columna “Rows”. De acuerdo con la estimación del optimizador, la última sentencia devolverá 17 filas. Sin embargo, en el primer plan de ejecución el escaneo del índice devuelve 333 filas. La base de datos debe después cargar esas 333 filas desde la tabla para aplicar el filtro LIKE y así reducir el resultado final a 17 filas. En el segundo plan de ejecución, en primer lugar el acceso al índice no devuelve esas filas así que la base de datos sólo necesita ejecutar 17 veces el TABLE ACCESS BY INDEX ROWID.

Se debe señalar también que el valor del costo de la operación INDEX RANGE SCAN crece de dos a tres veces debido a la columna adicional, lo que hace el índice más grande. Desde el punto de vista del incremento de rendimiento, este compromiso resulta aceptable.

La siguiente animación muestra la diferencia entre los dos planes de ejecución:

Figura 5.1 Predicados de filtro intencional sobre índice

Este ejemplo banal parece confirmar el sentido común de indexar cada columna que compone el filtro where. Sin embargo, este “sentido común” olvida la importancia del orden de las columnas que determinan qué condición puede usarse como predicado de acceso, lo que tiene un impacto enorme sobre el rendimiento. La decisión a cerca del orden de las columnas nunca debe dejarse al azar.

El tamaño del índice crece con el número de columnas, especialmente cuando se agregan columnas de texto. Por supuesto, el rendimiento no mejora con un índice mayor aunque la escabilidad logarítmica limita considerablemente el impacto. Bajo ningún concepto se deberán agregar todas las columnas mencionadas en el filtro where a un índice, sino usar solamente los predicados de filtro intencionales para reducir el volumen de datos durante la etapa de ejecución anterior.

Si te gusta mi manera de explicar, te encantará mi libro.