Escaneado limitado al índice: Evitar el acceso a la tabla

El escaneado limitado a índice es una de las técnicas de optimización más importantes. No solamente evita tener acceso a la tabla para evaluar el filtro where, sino que evita tener acceso a la tabla por completo si la base de datos puede encontrar todas las columnas seleccionadas dentro el índice.

Para cubrir una sentencia completa, un índice debe contener todas las columnas de la sentencia SQL; especialmente las columnas de la cláusula select como se muestra en el siguiente ejemplo:

CREATE INDEX sales_sub_eur
    ON sales
     ( subsidiary_id, eur_value )

SELECT SUM(eur_value)
  FROM sales
 WHERE subsidiary_id = ?

Por supuesto, indexar el filtro where prevalece sobre las otras cláusulas. Por lo tanto, la columna SUBSIDIARY_ID está en primera posición así que cumple los requisitos para realizar un predicado de acceso.

El plan de ejecución muestra el escaneo del índice sin un acceso posterior a la tabla (TABLE ACCESS BY INDEX ROWID).

El índice cubre la sentencia completa así que también se le llama índice de cobertura (en inglés, covering index).

El índice tiene una copia de la columna EUR_VALUE, asi que la base de datos puede usar el valor almacenado dentro del índice. No se requiere tener acceso a la tabla porque el índice tiene toda la información necesaria para satisfacer la sentencia.

Escanear solamente el índice puede mejorar muchísimo el rendimiento. Únicamente viendo la estimación del número de registros en el plan de ejecución, el optimizador espera combinar más de 40.000 registros. Eso significa que el escaneo de sólo índice evita 40.000 búsquedas en la tabla en el caso de que cada registro esté en un bloque diferente de la tabla. Si la tabla tiene un factor de agrupación bueno, esto es si las filas correspondientes están agrupadas en pocos bloques de la tabla, la ventaja puede ser considerablemente menor.

A parte del factor de agrupación, el número de registros seleccionados limita el beneficio potencial de rendimiento del escaneo de sólo índice. Por ejemplo, si se selecciona un único registro, se puede ahorrar únicamente un solo acceso a la tabla. Considerando que el recorrido del árbol necesita recuperar varios bloques, el ahorro del acceso a la tabla puede llegar a ser insignificante.

El escaneo de sólo índice es una estrategia de indexación agresiva. No se diseña un índice para “sólo escanear el índice” únicamente “por si acaso”, porque sino se usaría sin necesidad la memoria y se incrementaría el esfuerzo de mantenimiento necesario para las sentencias update. Ver en el Capítulo 8, “Modificando los datos”. En la práctica, se podría indexar primero sin considerar la cláusula select y solamente extender el índice si se necesita.

También causar sorpresas desagradables, por ejemplo, si se quiere limitar la sentencia a las ventas recientes:

SELECT SUM(eur_value)
  FROM sales
 WHERE subsidiary_id = ?
   AND sale_date > ?

Sin ver el plan de ejecución, uno esperaría que la sentencia se ejecuta más rápido porque se seleccionan pocas filas. Sin embargo, el filtro where hace referencia a una columna que no está dentro del índice, así que la base de datos debe tener acceso a la tabla para cargar esta columna.

El acceso a la tabla incrementa el tiempo de respuesta a pesar de que la sentencia selecciona pocas filas. El factor pertinente no es cuántas filas devolverá la sentencia, sino cuántas filas deberá examinar la base de datos para encontrar lo que se busca.

Si un índice ya no puede ser usado para un escaneado de sólo índice, el optimizador escogerá el siguiente mejor plan de ejecución. Eso significa que el optimizador podría seleccionar un plan de ejecución completamente diferente, o, como anteriormente, un plan de ejecución similar con otro índice. En este caso, se usa un índice sobre SALE_DATE, que es un “resto” del capítulo anterior.

Desde la perspectiva del optimizador, este índice tiene dos ventajas sobre SALES_SUB_EUR. El optimizador cree que el filtro sobre SALE_DATE es más selectivo que el filtro sobre SUBSIDIARY_ID. Se puede observar en la columna correspondiente “Rows” de los últimos dos planes de ejecución (desde 10,000 frente a 40,000). Sin embargo, estas estimaciones son meramente arbitrarias porque la sentencia usa variables bind. Por ejemplo, la condición SALE_DATE podría seleccionar la tabla entera, si se provee la fecha de la primera venta.

La segunda ventaja del índice sobre SALES_DATE es que tiene un mejor factor de agrupación. Esa es una razón válida porque la tabla SALES solamente crece en orden cronológico. Siempre se agregan nuevas filas al final de la tabla mientras no se eliminen filas. Por lo tanto, el orden de la tabla corresponde al orden del índice porque ambos están ordenados aproximadamenteen orden cronológico; por eso el índice tiene un factor de agrupación bueno.

Cuando se usa un índice con un factor de agrupación bueno, las filas seleccionadas de la tabla se almacenan casi juntas así que la base de datos sólo necesita leer pocos bloques de la tabla para obtenerlas todas. Usando este índice, la sentencia podría ser lo suficiente rápida sin usar un “escaneado de sólo índice”. En este caso, nuevamente se deberían eliminar las columnas innecesarias del otro índice.

En este caso particular, no existe una feliz coincidencia. El nuevo filtro sobre SALE_DATE no solamente impide usar el escaneado limitado al índice, sino que al mismo tiempo abre un nuevo camino de acceso. El optimizador es capaz de limitar el impacto de rendimiento para este cambio. Sin embargo, también es posible evitar el escaneado limitado al índice agregando columnas sobre las otras cláusulas. De hecho, añadiendo una columna sobre la cláusula del select nunca se abrirá un nuevo camino de acceso que limite el impacto de la pérdida del “escaneado de sólo índice”.

Los índices sobre expresiones también pueden causar sorpresas desagradables con el “escaneado limitado al índice”. Un índice sobre UPPER(last_name) no puede ser usado por un “escaneado limitado al índice” cuando se selecciona la columna LAST_NAME. Según se vio en la sección anterior, se podría tener un índice sobre la columna LAST_NAME para soportar el filtro LIKE y permitir que sólo se escanee el índice cuando se selecciona sobre la columna LAST_NAME.

Combinar las sentencias LIKE en una sola, como se ha visto anteriormente, genera buenos candidatos para el escaneado limitado al índice. De este modo, se consultan muchas filas pero pocas columnas, lo que genera un índice pequeño y suficiente para soportar esta técnica. Cuantas más columnas se consultan, más columnas se tienen que agregar al índice para soportar el escaneado de sólo índice índice. Como desarrollador, se deben seleccionar solamente las columnas que sean realmente necesarias.

Indexar muchas filas requiere mucho espacio y, además, se puede alcanzar el límite de la base de datos. La mayoría de las bases de datos imponen un límite estático para el número de columnas dentro de un índice y para el tamaño total de entradas dentro de un índice. Eso significa que no pueden indexar un número caprichoso de columnas ni columnas de tamaño arbitrario. A pesar de todo, existen índices que cubren una tabla entera como veremos en la siguiente sección.

 CREATE INDEX empsubupnam
     ON employees
       (subsidiary_id, last_name)
INCLUDE(phone_number, first_name)

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