Muchas veces me preguntaron por que una consulta no responde en un tiempo adecuado cuando el plan de ejecución muestra que se esta utilizando un indice. La respuesta en ciertos casos es muy sencilla y se debea a que el indice no es lo mas selectivo posible, no filtra todo lo que podria filtrar. Decir que una consulta usa un plan que accede por un indice no es suficiente para asegurar que se haya encontrado el camino mas eficiente. Para mostrarles un poco de que estoy hablando voy a armar un caso, un tanto trivial pero no por eso menos ilustrativo, para que se entienda la idea.
Voy a crear una tabla T con 3 columnas. La columna COL1 va a tener 1000 valores distintos y la columna COL2 va a tener 10 valores posibles. Además agrego la columna COL3 de relleno
Voy a crear una tabla T con 3 columnas. La columna COL1 va a tener 1000 valores distintos y la columna COL2 va a tener 10 valores posibles. Además agrego la columna COL3 de relleno
create table t
as
select mod(rownum,1000) col1,
mod(rownum,100000) col2,
dbms_random.string('a',50) col3
from dual
connect by rownum <= 1000000
Una vez creada la tabla voy a crear un indice por COL1 y recolecto las estadisticas para la tabla y para el indice:
create index t_idx on t (col1)
begin
dbms_stats.gather_table_stats(ownname => user,tabname => 'T',cascade => true);
end;Ejecuto la siguiente consulta y extraigo el plan usando DBMS_XPLAN.DISPLAY_CURSOR para que el plan sea mas detallado (en una nota futura voy a usar el mismo método para mostrar como ver como se "confunde" el optimizador cuando no se dispone de las estadisticas adecuadas):
select /*+ gather_plan_statistics */ * from t
where col1 = 9
and col2 = 9
select * from table (dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 25p2md7bszhj6, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from t where col1 = 9 and col2
= 9
Plan hash value: 1020776977
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 1006 |
|* 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 1 | 10 |00:00:00.01 | 1006 |
|* 2 | INDEX RANGE SCAN | T_IDX | 1 | 1000 | 1000 |00:00:00.01 | 6 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("COL2"=9)
2 - access("COL1"=9)En el paso 2 del plan se observa que la estimación fue buena (E-Rows=A-Rows) pero la cantidad filtrada fue 1ooo filas, cuando realmente se deberian haber filtrado 10, como se ve en el paso 1 (A-Rows)
(Aclaración: A-Rows es la cantidad de filas reales y E-Rows es la cantidad de filas estimada)
Ahora voy a eliminar el indice T_IDX y voy a crear otro con el mismo nombre pero indexando por COL1 y COL2. Veamos el nuevo plan:
drop index t_idx
create index t_idx on t (col1,col2)
select /*+ gather_plan_statistics 2 */ * from t
where col1 = 9
and col2 = 9
select * from table (dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 25p2md7bszhj6, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from t where col1 = 9 and col2
= 9
Plan hash value: 1020776977
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 14 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 10 | 10 |00:00:00.01 | 14 |
|* 2 | INDEX RANGE SCAN | T_IDX | 1 | 10 | 10 |00:00:00.01 | 4 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("COL1"=9 AND "COL2"=9)Ahora el paso 2 retorna al paso 1 solo 10 filas, que es la cantidad de filas total retornada por la consulta. Observar que la cantidad de buffers utilizado es bastante inferior al caso anterior.
Esto demuestra que el nuevo indice fue 100 veces mas selectivo y por lo tanto se necesitaron menos recursos, en consecuencia menos tiempo de procesamiento, para obtener la misma salida.
La moraleja es que nunca hay que conformarse con solo verificar que la sentencia accede por un indice y profundizar en el análisis sobre el esquema actual de indexación comprobando si es el mejor indice posible o si se puede buscar alguna otra combinación mas eficiente.
.JPG)
No hay comentarios:
Publicar un comentario