sábado, 24 de noviembre de 2007

De FULL TABLE SCAN a INDEX RANGE SCAN

Necesitamos reducir el tiempo de ejecución de un SELECT que accede a una tabla filtrando filas por la columna definida PRIMIRY KEY que posee un índice único.
La tabla ESTADO_APLICACION posee una fila por cada medición de disponibilidad de una aplicación. Cada fila de la tabla detalla la fecha y hora de medición (columna FECHA) y el valor "ACTIVA" o "INACTIVA" (columna ESTADO) según correponda al resultado del proceso de verificación de disponibilidad de la aplicación.
Veamos un ejemplo del contenido de esta tabla y las disponibilidades registradas:
SQL> RUN
1 SELECT TO_CHAR(FECHA,'DD/MM/YYYY HH24:MI:SS') AS FECHA,
2 ESTADO
3 FROM ESTADO_APLICACION
4* WHERE ROWNUM < 11

FECHA ESTADO
------------------- ----------------
23/11/2007 20:24:34 ACTIVA
23/11/2007 20:23:34 INACTIVA
23/11/2007 20:22:34 INACTIVA
23/11/2007 20:21:34 INACTIVA
23/11/2007 20:20:34 ACTIVA
23/11/2007 20:19:34 ACTIVA
23/11/2007 20:18:34 ACTIVA
23/11/2007 20:17:34 ACTIVA
23/11/2007 20:16:34 ACTIVA
23/11/2007 20:15:34 ACTIVA

10 filas seleccionadas.
El objetivo de la sentencia SELECT es contar la cantidad de filas con fecha 23/11/2007. La columna FECHA posee un índice único y la sentencia SELECT filtra las filas seleccionadas por esta misma columna. Sin embargo el optimizador de ejecución de consultas decide recorrer toda la tabla para recuperar las filas seleccionadas. Ejecutamos la sentencia SELECT sin recuperar las filas pero mostrando el plan de ejecución del optimizador para recuperarlas configurando SET AUTOTRACE TRACEONLY EXPLAIN. Podemos observar que se accederá de manera TABLE ACCESS FULL al la tabla ESTADO_APLICACION para ejecutar la sentencia SELECT. Es decir, se leerán en forma secuencial todos los bloques de datos asociados a las filas de la tabla y se evaluará el criterio de selección a cada fila. Estas lecturas pueden representar un gran volumen de transferencias de datos de entrada aumentando el tiempo de ejecución de la sentencia SELECT.
SQL>  SET AUTOTRACE TRACEONLY EXPLAIN

SQL> RUN
1 SELECT COUNT(*)
2 FROM ESTADO_APLICACION
3* WHERE TO_CHAR(FECHA,'DD/MM/YYYY') = '23/11/2007'

Plan de Ejecucion
----------------------------------------------------------
Plan hash value: 111243894

-----------------------------------------------------------------------------
| Id|Operation | Name |Rows|Bytes|Cost (%CPU)|Time |
-----------------------------------------------------------------------------
| 0|SELECT STATEMENT | | 1| 9| 24 (5)|00:00:01|
| 1| SORT AGGREGATE | | 1| 9| | |
|* 2| TABLE ACCESS FULL|ESTADO_APLICACION |1829|16461| 24 (5)|00:00:01|
-----------------------------------------------------------------------------
El optimizador de ejecución de sentencias no puede utilizar el índice sobre la columna FECHA evaluada por el criterio de selección porque esta columna es convertida del tipo de dato original de la tabla DATE a VARCHAR2 con una función TO_CHAR para comparar con la cadena de caracteres '23/11/2007' y el índice almacena los valores en el tipo DATE.
Modificamos la sentencia para no utilizar una función sobre la columna FECHA y asi poder evaluar el criterio de seleccion sobre los valores contenidos en el índice de la PRIMARY KEY. Para esto filtramos las filas con valores entre el 23 de noviembre de 2007 a la hora 00:00:00 y el 23 de noviembre a la hora 23:59:59 ambos límites definidos en tipos de dato DATE y comparados con una columna de tipo de dato DATE. Mostramos el plan de ejecución de la sentencia modificada y podemos ver que la forma de acceso ahora es INDEX RANGE SCAN al índice ESTADO_APLICACION_PK.
SQL> SET AUTOTRACE TRACEONLY EXPLAIN
SQL> RUN
1 SELECT COUNT(*)
2 FROM ESTADO_APLICACION
3 WHERE FECHA BETWEEN TO_DATE('23/11/2007 00:00:00','DD/MM/YYYY HH24:MI:SS')
4* AND TO_DATE('23/11/2007 23:59:59','DD/MM/YYYY HH24:MI:SS')

Plan de EjecuciOn
----------------------------------------------------------
Plan hash value: 4117644086

-----------------------------------------------------------------------------
|Id|Operation | Name |Rows|Bytes|Cost (%CPU)|Time |
-----------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 1| 8| 8 (0)|00:00:01|
| 1| SORT AGGREGATE | | 1| 8| | |
|*2| INDEX RANGE SCAN|ESTADO_APLICACION_PK|1441|11528| 8 (0)|00:00:01|
-----------------------------------------------------------------------------