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.
No hay comentarios:
Publicar un comentario