Hace unos días tuve que mejorar una consulta como esta que demoraba varios minutos y generó una tranca en el acceso a disco, con el evento 'db file scattered read':
select *
from SMALL
where DESTINO2 = 2 and solicitud2 not in
(select solicitud from TINY
where DESTINO=2 and solicitud not in
(select solicitud2 from BIGTABLE where estado='F'));
El plan de ejecución es:
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2833 | 376K| 220K (2)| 00:44:08 |
|* 1 | FILTER | | | | | |
|* 2 | TABLE ACCESS FULL | SMALL | 2833 | 376K| 173 (1)| 00:00:03 |
|* 3 | FILTER | | | | | |
|* 4 | TABLE ACCESS FULL| TINY | 1 | 8 | 122 (2)| 00:00:02 |
|* 5 | TABLE ACCESS FULL| BIGTABLE | 1 | 5 | 34 (3)| 00:00:01 |
--------------------------------------------------------------------------------------------
El primer paso obvio es crear índices, ya que estas tablas solo tienen PK y no son estas columnas. Revisando el tamaño de las tablas, hay una muy grande:
TABLE_NAME NUM_ROWS
------------------------------ ----------
SMALL 51602
BIGTABLE 10762233
TINY 26882
Sobre las dos tablas chicas se pueden crear sin problemas los indices faltantes, pero sobre la grande conviene analizar un poco más:
create index i_TINY on TINY (DESTINO, solicitud);
create index i_SMALL on SMALL (DESTINO2, solicitud2);
En BIGTABLE revisamos la distribución de datos en la columna usada para filtrar, para ver que tan selectivo es el filtro usado por la consulta:
select estado, count(1)
from BIGTABLE
group by estado;
E COUNT(1)
- ----------
H 13154
P 10321688
D 1740
A 288
E 13349
L 2659
S 1829
G 4831
F 2552
En este caso, para el valor F hay muy pocos registros. Habría que revisar el código de la aplicación para ver si este filtro es estático.
En caso que pueda usarse cualquier valor para filtrar, hay que evaluar la frecuencia de sentencias DML (insert/update/delete) sobre la tabla, ya que un nuevo índice puede hacer que estas operaciones sean más lentas, y además se debe evaluar el espacio que ocupa el mismo.
En caso que siempre se filtre por F es donde se pone interesante, ya que podemos indexar usando una función de forma que el índice incluya sólo los registros que nos interesa. Esto hace que el índice sea mucho más chico, por lo tanto ocupa muy poco espacio, involucra menos operaciones de IO, y no enlentece tanto las operaciones DML.
Solo debe hacerse un pequeño cambio en la forma de escribir la consulta para que sea usado por Oracle.
El índice quedaría:
create index i_BIGTABLE_est on BIGTABLE(decode(estado,'F','F',null),solicitud2);
Y la nueva consulta :
select *
from SMALL
where DESTINO2 = 2 and solicitud2 not in
(select solicitud from TINY
where DESTINO=2 and solicitud not in
(select solicitud2 from BIGTABLE where decode(estado,'F','F',null)='F'));
El plan ahora es mucho más lindo, y lo que es mejor, involucra mucho menos operaciones de IO, al utilizar índices que son mucho más chicos que la tabla original:
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2833 | 376K| 35522 (4)| 00:07:07 |
|* 1 | FILTER | | | | | |
|* 2 | TABLE ACCESS FULL | SMALL | 2833 | 376K| 173 (1)| 00:00:03 |
|* 3 | FILTER | | | | | |
|* 4 | INDEX FAST FULL SCAN| I_TINY | 1 | 8 | 22 (5)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | I_BIGTABLE_EST | 1 | 5 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
