Manejo de subconsultas en la cláusula SELECT. Parte I.
El motor SQL de Oracle es muy flexible. Se permite el uso de subconsultas en la cláusula WHERE, y HAVING, así como directamente en la cláusula FROM (se tratan como vistas) y también como forma de resolver una columna en el conjunto de resultados en la cláusula SELECT.
Lo curioso es que, dado que el formateo de filas se hace al final, de algún modo el optimizador ignora el coste de combinación de las tablas de la subconsulta, pues las trata una vez ha procesado la sentencia.
Por ejemplo, la consulta de clientes con su identificación de sexo y estado civil.
SQL> set autotrace traceonly
SQL> select nif, cn_sexo sexo, cn_ec estado
2 from clientes, sexos, estadosciviles
3 where clientes.ec_id_ec=estadosciviles.id_ec
4 and clientes.sex_id_sexo=sexos.id_sexo;
9999 filas seleccionadas.
Plan de Ejecución
———————————————————-
Plan hash value: 3779357754
————————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————————————
| 0 | SELECT STATEMENT | | 10001 | 341K| 34 (6)| 00:00:01 |
|* 1 | HASH JOIN | | 10001 | 341K| 34 (6)| 00:00:01 |
| 2 | TABLE ACCESS FULL | ESTADOSCIVILES | 5 | 55 | 3 (0)| 00:00:01 |
| 3 | MERGE JOIN | | 10000 | 234K| 31 (7)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| SEXOS | 2 | 16 | 2 (0)| 00:00:01 |
| 5 | INDEX FULL SCAN | SEX_PK | 2 | | 1 (0)| 00:00:01 |
|* 6 | SORT JOIN | | 9999 | 156K| 29 (7)| 00:00:01 |
| 7 | TABLE ACCESS FULL | CLIENTES | 9999 | 156K| 27 (0)| 00:00:01 |
————————————————————————————————
Predicate Information (identified by operation id):
—————————————————
1 – access(«CLIENTES».»EC_ID_EC»=»ESTADOSCIVILES».»ID_EC»)
6 – access(«CLIENTES».»SEX_ID_SEXO»=»SEXOS».»ID_SEXO»)
filter(«CLIENTES».»SEX_ID_SEXO»=»SEXOS».»ID_SEXO»)
Estadísticas
———————————————————-
1 recursive calls
0 db block gets
108 consistent gets
1 physical reads
0 redo size
346739 bytes sent via SQL*Net to client
7846 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
9999 rows processed
La lectura de 108 bloques en memoria corresponde mayoritariamente a los bloques de la tabla CLIENTES, al índice de clave primaria de la tabla SEXOS y al acceso completo por FULL SCAN de la tabla ESTADOSCIVILES. El coste se tasa en 34.
Esta misma consulta, expresando la resolución del valor de sexo y estado civil del cliente directamente en las columnas de resultado, suponen un plan de ejecución similar, pero tasado ligeramente inferior.
SQL> select nif,
2 (select cn_sexo from sexos where id_sexo=clientes.sex_id_sexo) sexo,
3 (select cn_ec from estadosciviles where id_ec=clientes.ec_id_ec) estado
4 from clientes;
9999 filas seleccionadas.
Transcurrido: 00:00:00.07
Plan de Ejecución
———————————————————-
Plan hash value: 3745735041
———————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————————-
| 0 | SELECT STATEMENT | | 9999 | 156K| 27 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| SEXOS | 1 | 8 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | SEX_PK | 1 | | 0 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| ESTADOSCIVILES | 1 | 11 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | EC_PK | 1 | | 0 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | CLIENTES | 9999 | 156K| 27 (0)| 00:00:01 |
———————————————————————————————-
Predicate Information (identified by operation id):
—————————————————
2 – access(«ID_SEXO»=:B1)
4 – access(«ID_EC»=:B1)
Estadísticas
———————————————————-
1 recursive calls
0 db block gets
766 consistent gets
0 physical reads
0 redo size
380260 bytes sent via SQL*Net to client
7846 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9999 rows processed
Prácticamente, los costes de acceso y gestión de la tabla SEXOS y ESTADOSCIVILES parecen omitirse en el cálculo (el anterior plan medía 34, menos 3 de acceso a cada tabla y 1 de acceso al índice), pero el volumen total de bloques leídos en memoria se multiplica por siete.
Traza de ejecución con uso de joins
select nif, cn_sexo sexo, cn_ec estado
from clientes, sexos, estadosciviles
where clientes.ec_id_ec=estadosciviles.id_ec
and clientes.sex_id_sexo=sexos.id_sexo
call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 668 0.01 0.04 1 108 0 9999
——- —— ——– ———- ———- ———- ———- ———-
total 670 0.01 0.04 1 108 0 9999
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91 (VUELOS)
Number of plan statistics captured: 1
Rows Execution Plan
——- —————————————————
0 SELECT STATEMENT MODE: ALL_ROWS
9999 HASH JOIN
5 TABLE ACCESS MODE: ANALYZED (FULL) OF ‘ESTADOSCIVILES’ (TABLE)
9999 MERGE JOIN
2 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF ‘SEXOS’ (TABLE)
2 INDEX MODE: ANALYZED (FULL SCAN) OF ‘SEX_PK’ (INDEX (UNIQUE))
9999 SORT (JOIN)
9999 TABLE ACCESS MODE: ANALYZED (FULL) OF ‘CLIENTES’ (TABLE)
Traza de ejecución con subconsultas en la cláusula SELECT
select nif,
(select cn_sexo from sexos where id_sexo=clientes.sex_id_sexo) sexo,
(select cn_ec from estadosciviles where id_ec=clientes.ec_id_ec) estado
from clientes
call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 668 0.00 0.01 0 766 0 9999
——- —— ——– ———- ———- ———- ———- ———-
total 670 0.00 0.01 0 766 0 9999
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91 (VUELOS)
Number of plan statistics captured: 1
Rows Execution Plan
——- —————————————————
0 SELECT STATEMENT MODE: ALL_ROWS
2 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF ‘SEXOS’ (TABLE)
2 INDEX MODE: ANALYZED (UNIQUE SCAN) OF ‘SEX_PK’ (INDEX (UNIQUE))
5 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF ‘ESTADOSCIVILES’ (TABLE)
5 INDEX MODE: ANALYZED (UNIQUE SCAN) OF ‘EC_PK’ (INDEX (UNIQUE) )
9999 TABLE ACCESS MODE: ANALYZED (FULL) OF ‘CLIENTES’ (TABLE)
La ejecución mediante subconsultas parece indicar al optimizador que no es necesario ordenar la tabla CLIENTES para facilitar las joins, y que el obtener por cada fila desde memoria las filas correspondientes al sexo y al estado civil resulta óptimo.
Es algo muy parecido a una ejecución en estrella. La diferencia está en el cálculo de un hash para resolver los valores de sexo y estado civil, o el acceso directo a los bloques. Aunque la segunda ejecución parezca ejecutarse en menos tiempo, hay que tener en cuenta el alto consumo de bloques en memoria, cosa que no sucedería en una ejecución en estrella.
En este caso, la ejecución con subconsultas en la cláusula SELECT sustituyendo las joins ha supuesto una mejora del rendimiento. En la parte II publicaré un caso completamente opuesto. Mientras que los costes en el plan de ejecución aparentemente van siendo cada vez menores, el rendimiento real de las ejecuciones resulta cada vez peor.