Manejo de subconsultas en la cláusula SELECT. Parte II

(Continúa de Parte I)

Este post podría llamarse «La paradoja del increíble coste menguante» como si de un relato de G. K. Chesterton se tratara.

Si alguien pensó por la lectura de la parte I de este post que las subconsultas en la cláusula SELECT mejoraban el rendimiento, pues permitían reproducir consultas en estrella sin necesidad de tener un modelo en estrella, ni dimensiones ni jerarquías, está al borde de cometer un grave error.
El optimizador ignora los costes de combinación de las subconsultas en la cláusula SELECT, contando únicamente con el coste de acceso a los objetos de esa subconsulta. Esto sucede incluso en versión Oracle11gR2.
Como ejemplo sirva la siguiente consulta formulada sobre VUELOS (57.711 filas), RESERVAS (171.113 filas) y CLIENTES (9999 filas).

Consulta de reservas, con datos de vuelos y clientes expresado con dos joins

select reservas.id_reserva, reservas.importe, vuelos.detalles, clientes.apellidos
    from vuelos, reservas, clientes
    where vuelos.id_vuelo=reservas.vue_id_vuelo
      and reservas.cli_nif=clientes.nif;

Consulta de reservas, con datos de vuelos y clientes expresado con una join y una subconsulta en la cláusula SELECT

select reservas.id_reserva, reservas.importe,
     (select vuelos.detalles from vuelos
       where vuelos.id_vuelo=reservas.vue_id_vuelo) vuelo,
    clientes.apellidos
    from reservas, clientes
    where reservas.cli_nif=clientes.nif;

Consulta de reservas, con datos de vuelos y clientes expresado con dos subconsultas en la cláusula SELECT

select reservas.id_reserva, reservas.importe,
     (select vuelos.detalles from vuelos   
       where reservas.vue_id_vuelo=vuelos.id_vuelo) vuelo,
     (select clientes.apellidos    from clientes 
       where reservas.cli_nif=clientes.nif) cliente
    from reservas;
Los correspondientes planes de ejecución parecen evidenciar lo mencionado anteriormente: el optimizador de costes no es capaz de evaluar el impacto de la combinación de elementos de la consulta principal con los de las subconsultas en la cláusula SELECT. Por este motivo, los costes de los planes de ejecución cada vez son inferiores.

Ejecución de la consulta de reservas, con datos de vuelos y clientes expresado con dos joins con el plan de ejecución asociado y la traza de AUTOTRACE

SQL> select reservas.id_reserva, reservas.importe, vuelos.detalles, clientes.apellidos
  2      from vuelos, reservas, clientes
  3      where vuelos.id_vuelo=reservas.vue_id_vuelo
  4        and reservas.cli_nif=clientes.nif;
171113 filas seleccionadas.
Transcurrido: 00:00:01.54
Plan de Ejecución
———————————————————-
Plan hash value: 858327892
—————————————————————————————-
| Id  | Operation           | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
—————————————————————————————-
|   0 | SELECT STATEMENT    |          |   171K|    13M|       |   904   (2)| 00:00:11 |
|*  1 |  HASH JOIN          |          |   171K|    13M|       |   904   (2)| 00:00:11 |
|   2 |   TABLE ACCESS FULL | CLIENTES |  9999 |   361K|       |    27   (0)| 00:00:01 |
|*  3 |   HASH JOIN         |          |   171K|  7686K|  1528K|   875   (2)| 00:00:11 |
|   4 |    TABLE ACCESS FULL| VUELOS   | 57711 |   845K|       |   137   (1)| 00:00:02 |
|   5 |    TABLE ACCESS FULL| RESERVAS |   171K|  5180K|       |   311   (2)| 00:00:04 |
—————————————————————————————-
Predicate Information (identified by operation id):
—————————————————
   1 – access(«RESERVAS».»CLI_NIF»=»CLIENTES».»NIF»)
   3 – access(«VUELOS».»ID_VUELO»=»RESERVAS».»VUE_ID_VUELO»)
Estadísticas
———————————————————-
         15  recursive calls
          0  db block gets
      13013  consistent gets
         96  physical reads
          0  redo size
    7835592  bytes sent via SQL*Net to client
     125996  bytes received via SQL*Net from client
      11409  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     171113  rows processed
A grandes rasgos, el resumen de la ejecución puede ser una lectura de 13.013 bloques en memoria, un tiempo de ejecución de 1 minuto y 54 segundos y un coste de 904.

Ejecución de la consulta de reservas, con datos de vuelos y clientes expresado con una join y una subconsulta en la cláusula SELECT con el plan de ejecución asociado y la traza de AUTOTRACE

SQL> select reservas.id_reserva, reservas.importe,
  2     (select vuelos.detalles from vuelos
  3         where vuelos.id_vuelo=reservas.vue_id_vuelo) vuelo,
  4      clientes.apellidos
  5      from reservas, clientes
  6      where reservas.cli_nif=clientes.nif;
171113 filas seleccionadas.
Transcurrido: 00:00:02.40
Plan de Ejecución
———————————————————-
Plan hash value: 402988295
—————————————————————————————-
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————————-
|   0 | SELECT STATEMENT            |          |   171K|    11M|   340   (2)| 00:00:05 |
|   1 |  TABLE ACCESS BY INDEX ROWID| VUELOS   |     1 |    15 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | VUE_PK   |     1 |       |     1   (0)| 00:00:01 |
|*  3 |  HASH JOIN                  |          |   171K|    11M|   340   (2)| 00:00:05 |
|   4 |   TABLE ACCESS FULL         | CLIENTES |  9999 |   361K|    27   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL         | RESERVAS |   171K|  5180K|   311   (2)| 00:00:04 |
—————————————————————————————-
Predicate Information (identified by operation id):
—————————————————
   2 – access(«VUELOS».»ID_VUELO»=:B1)
   3 – access(«RESERVAS».»CLI_NIF»=»CLIENTES».»NIF»)
Estadísticas
———————————————————-
         15  recursive calls
          0  db block gets
     374003  consistent gets
          0  physical reads
          0  redo size
    7835589  bytes sent via SQL*Net to client
     125996  bytes received via SQL*Net from client
      11409  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     171113  rows processed
En esta ejecución, el número de bloques leídos en memoria ha aumentado a 374.003 y el tiempo de ejecución ha aumentado a 2 minutos 40 segundos. Sin embargo el coste de la ejecución se ha reducido a 340 (menos de la mitad). El número de bytes estimado como total de la ejecución también se estima mejorado: de 13 millones a 11 millones.
Ejecución de la consulta de reservas, con datos de vuelos y clientes expresado con dos subconsultas en la cláusula SELECT con el plan de ejecución asociado y la traza de AUTOTRACE
SQL> select reservas.id_reserva, reservas.importe,
  2       (select vuelos.detalles from vuelos   
  3          where reservas.vue_id_vuelo=vuelos.id_vuelo) vuelo,
  4       (select clientes.apellidos    from clientes 
  5          where reservas.cli_nif=clientes.nif) cliente
  6      from reservas;
171113 filas seleccionadas.
Transcurrido: 00:00:02.39
Plan de Ejecución
———————————————————-
Plan hash value: 465102819
—————————————————————————————-
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————————-
|   0 | SELECT STATEMENT            |          |   171K|  5180K|   311   (2)| 00:00:04 |
|   1 |  TABLE ACCESS BY INDEX ROWID| VUELOS   |     1 |    15 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | VUE_PK   |     1 |       |     1   (0)| 00:00:01 |
|   3 |  TABLE ACCESS BY INDEX ROWID| CLIENTES |     1 |    37 |     2   (0)| 00:00:01 |
|*  4 |   INDEX UNIQUE SCAN         | CLI_PK   |     1 |       |     1   (0)| 00:00:01 |
|   5 |  TABLE ACCESS FULL          | RESERVAS |   171K|  5180K|   311   (2)| 00:00:04 |
—————————————————————————————-
Predicate Information (identified by operation id):
—————————————————
   2 – access(«VUELOS».»ID_VUELO»=:B1)
   4 – access(«CLIENTES».»NIF»=:B1)
Estadísticas
———————————————————-
         15  recursive calls
          0  db block gets
     406374  consistent gets
          0  physical reads
          0  redo size
    7835587  bytes sent via SQL*Net to client
     125996  bytes received via SQL*Net from client
      11409  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     171113  rows processed
En este caso, el tiempo de ejecución es prácticamente el mismo, mientras que el coste se muestra aun mejor que el de la ejecución anterior (340 anteriores frente a 311) pero el número de bloques leídos en memoria aumenta (374.003 anteriores frente a 406.374).
Las trazas generadas por la utilidad tkprof vienen a confirmar prácticamente lo mostrado en la traza de autotrace.

Traza de la utilidad TKPROF sobre la consulta de reservas, con datos de vuelos y clientes expresada con dos joins

select reservas.id_reserva, reservas.importe, vuelos.detalles, clientes.apellidos
    from vuelos, reservas, clientes
    where vuelos.id_vuelo=reservas.vue_id_vuelo
      and reservas.cli_nif=clientes.nif
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    11409      0.39       0.51         96      13009          0      171113
——- ——  ——– ———- ———- ———- ———-  ———-
total    11411      0.39       0.52         96      13009          0      171113

Traza de la utilidad TKPROF sobre la consulta de reservas, con datos de vuelos y clientes expresada con una join y una subconsulta en la cláusula SELECT

select reservas.id_reserva, reservas.importe,
     (select vuelos.detalles from vuelos where vuelos.id_vuelo=reservas.vue_id_vuelo) vuelo,
    clientes.apellidos
    from reservas, clientes
    where reservas.cli_nif=clientes.nif
call     count       cpu    elapsed       disk      query    current        rows
——- ——  ——– ———- ———- ———- ———-  ———-
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch    11409      1.26       1.27          0     373999          0      171113
——- ——  ——– ———- ———- ———- ———-  ———-
total    11411      1.27       1.27          0     373999          0      171113

Traza de la utilidad TKPROF sobre la consulta de reservas, con datos de vuelos y clientes expresada con dos subconsultas en la cláusula SELECT

select reservas.id_reserva, reservas.importe,
     (select vuelos.detalles from vuelos   
       where reservas.vue_id_vuelo=vuelos.id_vuelo) vuelo,
     (select clientes.apellidos    from clientes 
       where reservas.cli_nif=clientes.nif) cliente
    from reservas
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    11409      1.32       1.24          0     406370          0      171113
——- ——  ——– ———- ———- ———- ———-  ———-
total    11411      1.32       1.24          0     406370          0      171113
En las dos ejecuciones con subconsultas en la cláusula SELECT se aprecia, además, el aumento de tiempo de CPU por el mayor número de bloques a procesar en memoria.
Cuidado, por tanto, con las subconsultas expresadas a ese nivel de ejecución, pues el optimizador no evalua sus pesos correctamente, al quedar fuera del estudio de accesos y combinaciones entre tablas, mediante joins y filtros convencionales. Los resultados expresados por los planes de ejecución de su estimación en coste pueden confundir, ya que muestran costes mejores sobre ejecuciones claramente más ineficientes.
standardjunio 20adminAdministración2 Comment

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.
standardjunio 17adminAdministración