Hola, hoy vamos a compartir un caso reciente de optimización. Mientras revisábamos temas de rendimiento para la optimización de un entorno Oracle detectamos una query que tardaba 29 minutos en ejecutar, enseguida nos pusimos a revisar su plan de ejecución.
Plan de ejecución inicial
La query en cuestión es:
SELECT codprs, codnum, tipdcmide, numd, nombre, apell, apel2, estado, fecha FROM ident WHERE estado IN ('1', '2', '3', '5') AND numd IS NOT NULL;
Aparentemente, tiene un plan de ejecución correcto: existe un índice por las dos columnas que forman parte del WHERE.
El plan de ejecución es:
A pesar de que se entra por el índice, el coste de la consulta es extremadamente elevado. Este hecho provoca que tarde mucho tiempo en resolver la misma.
El índice por el que entra, IX_IDENT_CODNUMDO, tiene la siguiente sintaxis:
CREATE INDEX "USER"."IX_IDENT_CODNUMDO" ON "USER"."IDENT" ("ESTADO", "NUMD")
Cardinalidad
Para tener la foto completa del escenario, es necesario comentar que la columna ESTADO tiene una cardinalidad muy pequeña, y que los valores que filtra en el where recuperan casi todas las filas de la tabla.
La cardinalidad en una tabla es el número de valores distintos que tiene dicha tabla. Una cardinalidad baja indica que hay pocos valores distintos, y con muchas filas para cada valor, y una cardinalidad alta, significa que hay muchos valores distintos con pocas filas para cada valor. El mejor ejemplo de cardinalidad alta en una tabla sería la Primary Key, que implica que todas las filas deben ser distintas, con lo que hay el máximo número de valores distintos para la columna, y cada valor solamente tiene 1 fila.
En este caso concreto, nos encontramos una tabla con baja cardinalidad, es decir, pocos valores distintos para cada valor. Ejecutando una query para conocer el número de filas por valor en la tabla IDENT devuelve el siguiente resultado:
Índice BITMAP
En el resultado de la query se ve claramente que intenta devolver la mayor parte de las filas de la tabla con los valores que aparecen en el filtro del WHERE. En este escenario, en un primer momento, parece claro que una mejora de la ejecución de la query puede venir por la sustitución del índice por un índice BITMAP, para aprovechar las ventajas de este tipo de índices en columnas de cardinalidad baja con respecto a los índices B*tree que se crean por defecto. Tenéis más información de los índices BITMAP en esta página.
Pero después de crear el índice BITMAP, comprobamos que no había una mejora sustancial importante, ya que la query todavía tardaba más de 20 minutos.
Encontramos una solución cambiando las columnas del índice de orden, creando el índice con las columnas en el sentido contrario a como están en la clausula WHERE.
Creamos el siguiente índice:
CREATE BITMAP INDEX "USER"."IX_IDENT_CODNUMDO" ON "USER"."IDENT" ("NUMD", "ESTADO" )
Con este nuevo índice, cambia radicalmente el plan de ejecución de la consulta. Pasa a tardar menos de 4 minutos.
¿Por qué mejora la query?
La mejora viene dada porque los índices de Oracle no guardan los valores nulos. De esta forma, al evaluar la parte WHERE, se evalua primero la columna NUMD. Para cuando se va a evaluar la segunda columna, el optimizador encuentra ya los valores colocados para devolver el resultado.
El plan de ejecución queda así:
Esperamos que os haya servido, nos vemos en próximas entradas. Podéis mirar las entradas de Oracle que hemos publicado en este enlace.
Equipo de Base de datos
