lunes, junio 17, 2013

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.

No hay comentarios: