La sección anterior explica cómo ganar un beneficio adicional desde un índice existente cambiando el orden de las columnas, pero el ejemplo considera solamente dos sentencias. Cambiar un índice, sin embargo, podría afectar todas las sentencias de la tabla indexada. Esta sección explica la manera en que la base de datos escoge un índice y muestra los posibles efectos cuando se cambian índices ya existentes.
El índice EMPLOYEES_PK ya
aceptado mejora el rendimiento de todas las sentencias que buscan
solamente por el ID secundario. Además es útil para las sentencias que
buscan por SUBSIDIARY_ID, a pesar de los criterios
adicionales para la búsqueda. Eso significa que el índice llega a ser
utilizable para las sentencias que son usadas por otros índices con una
otra parte del filtro where. En este
caso, si hay varios caminos de acceso disponibles, el trabajo del
optimizador es escoger el mejor.
Cambiar un índice puede tener un efecto indeseado. En nuestro ejemplo, tenemos una guía telefónica que llegó a ser muy lenta después de la fusión. El primer análisis identifica la siguiente sentencia como causa de la lentitud:
SELECT first_name, last_name, subsidiary_id, phone_number
FROM employees
WHERE last_name = 'WINAND'
AND subsidiary_id = 30El plan de ejecución es el siguiente:
Ejemplo 2.1 El plan de ejecución con la llave primaria ya corregida
---------------------------------------------------------------
|Id |Operation | Name | Rows | Cost |
---------------------------------------------------------------
| 0 |SELECT STATEMENT | | 1 | 30 |
|*1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 30 |
|*2 | INDEX RANGE SCAN | EMPLOYEES_PK | 40 | 2 |
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("LAST_NAME"='WINAND')
2 - access("SUBSIDIARY_ID"=30)El plan de ejecución utiliza un índice y tiene un costo global de
30. Por ahora, todo bien. Sin embargo, es sospechoso porque usa el
índice que apenas ha cambiado, una razón suficiente para presumir que
nuestro índice cambia el problema de rendimiento provocado,
especialmente cuando el índice anterior empezó con la columna
EMPLOYEE_ID que no es parte del filtro where. La sentencia no puede usar el índice
como estaba antes.
Para un análisis más profundo, sería bueno comparar el plan de ejecución antes y después del cambio. Para obtener el primer plan de ejecución, se podría hacer uso nuevamente del índice anterior. Sin embargo, la mayoría de las bases de datos ofrecen un método para evitar que una sentencia use un índice particular. El siguiente ejemplo utiliza una sugerencia (“hint”) con este propósito.
SELECT /*+ NO_INDEX(EMPLOYEES EMPLOYEES_PK) */
first_name, last_name, subsidiary_id, phone_number
FROM employees
WHERE last_name = 'WINAND'
AND subsidiary_id = 30El plan de ejecución que presuntamente se utilizó antes del cambio sobre el índice no usó el índice en absoluto:
----------------------------------------------------
| Id | Operation | Name | Rows | Cost |
----------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 477 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 477 |
----------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("LAST_NAME"='WINAND' AND "SUBSIDIARY_ID"=30)Aunque TABLE ACCESS FULL debe
leer y procesar la tabla entera, en este caso parece ser más rápido usar
un índice. Eso es particularmente inusual porque la sentencia devuelve
solamente un registro. utilizar un índice para encontrar un solo
registro puede ser más rápido que un escaneo entero de la tabla, pero en
este caso no lo es. El índice parece ser lento.
En estos casos, es mejor ir por cada etapa problemática del plan
de ejecución. La primera etapa es INDEX RANGE SCAN sobre el
índice EMPLOYEES_PK. Este índice no cubre la columna
LAST_NAME. INDEX RANGE SCAN puede considerar
que la columna SUBSIDIARY_ID es solamente un filtro; la
base de datos Oracle lo muestra en las informaciones de predicados
(“predicate information”) y tiene la entrada “2” en el plan de ejecución. Ahí es
donde se pueden ver las condiciones que se aplicaron por cada
operación.
Sugerencia
Apéndice A, “Planes de ejecución”, explica como encontrar la información de los predicados para las otras bases de datos
El INDEX RANGE SCAN con el ID de operación “2” (Ejemplo 2.1) aplica
solamente el filtro SUBSIDIARY_ID=30. Eso significa que
atraviesa el árbol del índice para encontrar la primera entrada de
SUBSIDIARY_ID con 30. Luego, sigue el nodo hoja para
encontrar las otras entradas por este ID secundario. El resultado del
INDEX RANGE SCAN es una lista de ROWIDs que
cumplen con la condición SUBSIDIARY_ID: dependiendo del
tamaño del ID secundario, es posible que sean solamente algunos o varios
centenares.
La siguiente etapa es la operación TABLE ACCESS BY INDEX
ROWID. Usa los ROWIDs devueltos por la etapa
anterior para traer los registros (todas las columnas) de la tabla. Una
vez que la columna LAST_NAME está disponible, la base de
datos puede evaluar la parte restante del filtro where. Eso significa que la base de datos
tiene que traer todos los registros del SUBSIDIARY_ID=30
antes de aplicar el filtro sobre LAST_NAME.
El tiempo de respuesta de la sentencia no depende del tamaño del
resultado sino del número de empleados con el ID secundario particular.
Si el ID secundario tiene solamente algunos miembros, INDEX RANGE
SCAN provee un mejor rendimiento. Sin embargo, un TABLE
ACCESS FULL puede ser más rápido para una cantidad gigante de ID
secundario porque puede leer una parte amplia de la tabla de una sola
vez (ver “FULL TABLE SCAN”).
La sentencia es lenta porque la búsqueda (“LOOKUP”) del índice
devuelve muchos ROWIDs, uno por cada empleado de la empresa
original, y la base de datos debe traerlos de forma individual. Es la
combinación perfecta para crear un índice lento: la base de datos lee un
rango amplio del índice y tiene que devolver muchos registros de forma
individual.
Escoger la mejor solución depende de la distribución de los datos en la tabla y la manera en que el optimizador usa las estadísticas acerca del contenido de la base de datos. En nuestro ejemplo, se usa un histograma, que contiene la distribución de los empleados por cada ID secundario. Eso permite al optimizador estimar el número de registros devueltos desde la búsqueda (“LOOKUP”) del índice (el resultado está usado para el cálculo del costo).
Si no existen estadísticas disponibles, por ejemplo porque la
información se ha borrado, el optimizador usa valores por defecto. Los
valores por defecto de las estadísticas para la base de datos Oracle
sugieren un índice pequeño con una selectividad mediana. Se guían para
la estimación por el hecho de que INDEX RANGE
SCAN devolverá 40 registros. El plan de ejecución muestra esta
estimación en las columnas de los registros (nuevamente, ver Ejemplo 2.1). Por
supuesto, se trata de una subestimación enorme, porque hay 1 000
empleados trabajando para este ID secundario.
Si proveemos estadísticas correctas, el optimizador hace un
trabajo mejor. El siguiente plan de ejecución muestra una nueva
estimación: 1,000 registros para el INDEX RANGE SCAN. En
este caso, el optimizador calcula un valor del costo mayor para el
acceso a la tabla.
---------------------------------------------------------------
|Id |Operation | Name | Rows | Cost |
---------------------------------------------------------------
| 0 |SELECT STATEMENT | | 1 | 680 |
|*1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 680 |
|*2 | INDEX RANGE SCAN | EMPLOYEES_PK | 1000 | 4 |
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("LAST_NAME"='WINAND')
2 - access("SUBSIDIARY_ID"=30)El costo con valor “680” es más alto que el valor del costo del
plan de ejecución usando el FULL TABLE SCAN (477). Por lo tanto, al optimizador
le gustará el FULL TABLE SCAN.
Este ejemplo de índice lento no oculta el hecho de que el índice
correcto es la mejor solución. Por supuesto, buscar un apellido lo
soporta mejor un índice si se indexa la columna
LAST_NAME:
CREATE INDEX emp_name ON employees (last_name)Al usar el nuevo índice, el optimizador calcula un valor del costo de 3:
Ejemplo 2.2 Plan de ejecución con un índice dedicado
--------------------------------------------------------------
| Id | Operation | Name | Rows | Cost |
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 |
|* 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 3 |
|* 2 | INDEX RANGE SCAN | EMP_NAME | 1 | 1 |
--------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SUBSIDIARY_ID"=30)
2 - access("LAST_NAME"='WINAND')El acceso al índice devuelve (de acuerdo con la estimación del
optimizador) solamente un registro. La base de datos tiene que traer
solamente este registro desde la tabla: es definitivamente más rápido
que FULL TABLE SCAN. Un índice correctamente definido es
aún mejor que el escaneo entero de la tabla propuesto
originalmente.
Los dos planes de ejecuciones propuestos en el Ejemplo 2.1 y Ejemplo 2.2 son casi idénticos: la base de datos
realiza las mismas operaciones y el optimizador calcula valores
similares para el costo. Sin embargo el segundo plan es mejor. La
eficiencia de INDEX RANGE SCAN puede
variar en un amplio rango, especialmente cuando requiere a continuación
un acceso a la tabla. Usar un índice no implica necesariamente que la
sentencia se ejecute de la mejor manera posible.
Si te gusta mi manera de explicar, te encantará mi libro.
