Índices concatenados

Aunque la base de datos crea de manera automática el índice sobre la llave primaria, puede ser útil, para una afinación manual, crear un índice sobre todo si está compuesto por varias columnas. En este caso, la base de datos crea un índice sobre todas las columnas de la llave primaria, que se llama índice índice multi-columna (también conocido como índice compuesto o índice combinado). Apunten que el orden de las columnas en los índices concatenados tiene un impacto positivo sobre su uso, así que se debe escoger con cuidado.

Por el propósito de la demostración, vamos a suponer que nuestra empresa se fusiona con otra. Al agregar los empleados de la otra empresa a nuestra tabla de EMPLOYEES, esta crecerá 10 veces. Existe un solo problema: el EMPLOYEE_ID no es único entre las dos empresas. Debemos extender la llave primaria con un identificador adicional, un ID secundario. De este modo, la nueva llave primaria tendrá dos columnas: el EMPLOYEE_ID como antes y el SUBSIDIARY_ID para restablecer la unicidad.

El índice para la nueva llave primaria se define de la manera:

CREATE UNIQUE INDEX employees_pk
    ON employees (employee_id, subsidiary_id)

Una sentencia para un empleado particular usa la llave primaria completa dentro de una cuenta, lo que quiere decir que se usará también la columna SUBSIDIARY_ID:

SELECT first_name, last_name
  FROM employees
 WHERE employee_id   = 123
   AND subsidiary_id = 30

Cada vez que una sentencia utiliza la llave primaria completa, la base de datos puede usar un INDEX UNIQUE SCAN sin importar cuántas columnas tenga el índice. Pero, ¿qué pasa cuando se usa solamente una de las columnas de la llave, por ejemplo, cuando se buscan todos los empleados de la llave secundaria?

SELECT first_name, last_name
  FROM employees
 WHERE subsidiary_id = 20

El plan de ejecución revela que la base de datos no usó el índice. En su lugar, se empleó TABLE ACCESS FULL. Como consecuencia, la base de datos lee toda la tabla y evalúa cada registro con el filtro where. El tiempo de ejecución crece con el tamaño de la tabla: si la tabla crece diez veces, TABLE ACCESS FULL tardará diez veces más. El peligro de esta operación es que se ejecute bastante rápidamente en un ambiente pequeño de prueba, pero que genere graves problemas de rendimiento en producción.

La base de datos no usa el índice porque no puede emplear una columna sencilla desde un índice concatenado de forma arbitraria. Hacer un análisis detallado sobre la estructura del índice ayudará a aclarar el proceso.

Un índice concatenado es solamente un índice B-tree como cualquier otro conservando los datos indexados dentro de una lista ordenada. La base de datos considera cada columna de acuerdo con su posición en la definición del índice para ordenar las entradas del mismo. La primera columna es el primer criterio de ordenamiento y la segunda columna determina el orden solamente si dos entradas tienen el mismo valor en la primera columna.

El orden de un índice con dos columnas es por lo tanto como el de una guía telefónica: está ordenado primero por apellidos, y después por nombres. Eso quiere decir que un índice con dos columnas no soporta una búsqueda únicamente sobre la segunda columna; es como si no se pudiera buscar por nombres dentro de una guía telefónica.

El fragmento del índice en la imagen de la Figura 2.1 muestra que las entradas para el ID secundario 20 no están almacenadas unas al lado de otro. Es evidente también que no hay entradas para SUBSIDIARY_ID = 20 dentro del árbol, aunque existe en los nodos hojas. El árbol es inútil para esta sentencia.

SELECT <INDEX COLUMN LIST> 
  FROM <TABLE>  
 ORDER BY <INDEX COLUMN LIST>
 FETCH FIRST 100 ROWS ONLY

Se podría, obviamente, agregar otro índice sobre la columna SUBSIDIARY_ID para mejorar el tiempo de respuesta. Existe, sin embargo, una solución mejor al menos si se supone que buscar solamente sobre la columna EMPLOYEE_ID no tiene sentido.

Se puede tomar ventaja del hecho de que la primera columna del índice es siempre utilizable para buscar. Nuevamente, es como una guía telefónica: no se necesita conocer el nombre para buscar por apellido. El truco es invertir el orden de las columnas del índice y así la columna SUBSIDIARY_ID se encontrará en primer lugar:

CREATE UNIQUE INDEX EMPLOYEES_PK 
    ON EMPLOYEES (SUBSIDIARY_ID, EMPLOYEE_ID)

Ambas columnas son todavía únicas así que para consultar sobre toda la llave primaria se puede usar aún un INDEX UNIQUE SCAN pero la secuencia de las entradas del índice es completamente diferente. La columna SUBSIDIARY_ID ya es el primer criterio de ordenamiento. Eso significa que todas las entradas para el ID secundario son consecutivas dentro del índice, así que la base de datos podrá usar el índice B-tree para encontrar su ubicación.

El plan de ejecución confirma que la base de datos utiliza el índice invertido. La columna SUBSIDIARY_ID sola ya no es única, por lo que la base de datos debe seguir los nodos hojas en orden para encontrar todas las entradas correctas: se realiza usando la operación INDEX RANGE SCAN.

Generalmente, la base de datos puede usar un índice concatenado cuando se busca sobre la columna más destacada (la que está más a la izquierda). Un índice con tres columnas puede utilizarse cuando se busca sobre la primera columna, cuando se busca sobre las dos primeras columnas, y cuando se busca sobre todas las columnas.

Aunque la solución de los dos índices da muy buenos resultados para la operación select, es preferible la solución de un único índice. No es solamente para ahorrar espacio, sino también para el mantenimiento del segundo índice. Cuantos menos índices tenga una tabla, mejor rendimiento darán las instrucciones insert, delete y update.

Para definir un índice óptimo, se debe saber no sólo cómo funcionan los índices; también se tiene que aprender cómo consultan las sentencias los datos de la aplicación. Eso significa que se han de conocer todas las combinaciones posibles de las columnas, cuyas aparecen como filtro en el where.

Por eso, resulta muy difícil definir un índice óptimo para un consultor externo porque no tiene una visión de conjunto de los accesos de la aplicación. Los consultores generalmente pueden considerar una y solamente una sentencia. No pueden explotar el beneficio adicional; cómo podría ayudar el índice a las demás sentencias. El administrador de base de datos (DBA) está en la misma posición porque aunque conoce el esquema de la base de datos no tiene un conocimiento profundo acerca de los caminos de acceso.

El único lugar donde el conocimiento técnico de la base de datos y el conocimiento funcional del negocio convergen es en el departamento de desarrollo. Los desarrolladores tienen el conocimiento de los datos y conocen los caminos de acceso a los mismos. Son quienes pueden indexar correctamente para obtener el máximo beneficio de la aplicación y además lograrlo sin mucho esfuerzo.

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