De recursividad e iteradores.

Muchas veces tenemos la necesidad de construir ‘resulsets‘ sobre la marcha con el fin de generar iteradores, o vistas ‘inline‘ especficas para combinar (‘join‘) con tablas dentro de queries.

Esto se hace fácilmente en Oracle mediante una ‘query‘ recursiva con ‘CONNECT BY’ desde una ‘SELECT’ a ‘DUAL’. Pero en Teradata las cosas funcionan de otro modo.

Hemos hablado a menudo de que la gran fortaleza de Teradata (su concepción ‘share nothing‘ y su paralelismo ‘natural’) puede también ser su debilidad en ocasiones. Con esto en mente vamos a hacer un pequeño ejercicio comparativo:

Vamos primero con Oracle. Un iterador secursivo se construye fácilmente:

CARLOS@XE.localhost> WITH ITERADOR
  2  AS(
  3     SELECT LEVEL
  4       FROM DUAL
  5       CONNECT BY LEVEL < 11
  6  )
  7  SELECT *
  8    FROM ITERADOR
  9  ;

     LEVEL
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 filas seleccionadas.

En Teradata podemos hacer algo muy parecido (con una tabla MY_DUAL construida al efecto y análoga a la ubícua ‘DUAL’ de Oracle)

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
WITH RECURSIVE ITERADOR (ORDEN)
AS(
   SELECT 1 ORDEN
     FROM MY_DB.MY_DUAL a
UNION ALL
   SELECT b.ORDEN + 1
     FROM MY_DB.MY_DUAL a,
          ITERADOR b
    WHERE b.ORDEN < 10
)
SELECT *
  FROM ITERADOR
;

WITH RECURSIVE ITERADOR (ORDEN)
AS(
   SELECT 1 ORDEN
     FROM MY_DB.MY_DUAL a
UNION ALL
   SELECT b.ORDEN + 1
     FROM MY_DB.MY_DUAL a,
          ITERADOR b
    WHERE b.ORDEN < 10
)
SELECT *
  FROM ITERADOR
;

 *** Query completed. 10 rows found. One column returned.
 *** Total elapsed time was 1 second.

ORDEN
-----
    1
    2
    3
    4
    5
    6
    7
    8
    9
   10

Todo parece funcionar razonablemente bien. El problema comienza cuando necesitamos un iterador un poco más grande:

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
WITH RECURSIVE ITERADOR (ORDEN)
AS(
   SELECT 1 ORDEN
     FROM MY_DB.MY_DUAL a
UNION ALL
   SELECT b.ORDEN + 1
     FROM MY_DB.MY_DUAL a,
          ITERADOR b
    WHERE b.ORDEN < 100
)
SELECT *
  FROM ITERADOR
;

WITH RECURSIVE ITERADOR (ORDEN)
AS(
   SELECT 1 ORDEN
     FROM MY_DB.MY_DUAL a
UNION ALL
   SELECT b.ORDEN + 1
     FROM MY_DB.MY_DUAL a,
          ITERADOR b
    WHERE b.ORDEN < 100
)
SELECT *
  FROM ITERADOR
;

 *** Query completed. 100 rows found. One column returned.
 *** Total elapsed time was 2 seconds.

ORDEN
-----
    1
    2
    3
    4
    5
    6
    7
    8
    9
   10
   11
   12
   .
   .
   .
   97
   98
   99
  100

 BTEQ -- Enter your DBC/SQL request or BTEQ command:

Dos segundos para 100 filas no parece muy ‘óptimo’. Sobre todo si Oracle hace:

CARLOS@XE.localhost> set timing on
CARLOS@XE.localhost> WITH ITERADOR
  2  AS(
  3     SELECT LEVEL
  4       FROM DUAL
  5       CONNECT BY LEVEL
 5       CONNECT BY LEVEL < 101
  6  )
  7  SELECT *
  8    FROM ITERADOR
  9  ;

     LEVEL
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
        11
        12
        .
        .
        .
        97
        98
        99
       100

100 filas seleccionadas.

Transcurrido: 00:00:00.23
CARLOS@XE.localhost>

Si seguimos aumentando las filas del iterador las diferencias aumentan exponencialmente. (Vamos a cambiar la query un poco y poner un COUNT() para que podamos ver los resultados con más facilidad):

Oracle:

CARLOS@XE.localhost> WITH ITERADOR
  2  AS(
  3     SELECT LEVEL
  4       FROM DUAL
  5       CONNECT BY LEVEL < 1001
  6  )
  7  SELECT count(1)
  8    FROM ITERADOR
  9  ;

  COUNT(1)
----------
      1000

Transcurrido: 00:00:00.04

Y Teradata:

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
WITH RECURSIVE ITERADOR (ORDEN)
AS(
   SELECT 1(SMALLINT) ORDEN
     FROM MY_DB.MY_DUAL a
UNION ALL
   SELECT b.ORDEN + 1
     FROM MY_DB.MY_DUAL a,
          ITERADOR b
    WHERE b.ORDEN < 1000
)
SELECT count(1)
  FROM ITERADOR
;

WITH RECURSIVE ITERADOR (ORDEN)
AS(
   SELECT 1(SMALLINT) ORDEN
     FROM MY_DB.MY_DUAL a
UNION ALL
   SELECT b.ORDEN + 1
     FROM MY_DB.MY_DUAL a,
          ITERADOR b
    WHERE b.ORDEN < 1000
)
SELECT count(1)
  FROM ITERADOR
;

 *** Query completed. One row found. One column returned.
 *** Total elapsed time was one minute and 18 seconds.

   Count(1)
-----------
       1000

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
WITH RECURSIVE ITERADOR (ORDEN)
AS(
   SELECT 1(SMALLINT) ORDEN
     FROM MY_DB.MY_DUAL a
UNION ALL
   SELECT b.ORDEN + 1
     FROM MY_DB.MY_DUAL a,
          ITERADOR b
    WHERE b.ORDEN < 10000
)
SELECT count(1)
  FROM ITERADOR
;

WITH RECURSIVE ITERADOR (ORDEN)
AS(
   SELECT 1(SMALLINT) ORDEN
     FROM MY_DB.MY_DUAL a
UNION ALL
   SELECT b.ORDEN + 1
     FROM MY_DB.MY_DUAL a,
          ITERADOR b
    WHERE b.ORDEN < 10000
)
SELECT count(1)
  FROM ITERADOR
;

 *** Query completed. One row found. One column returned.
 *** Total elapsed time was 11 minutes and 4 seconds.

   Count(1)
-----------
      10000

Mientras que a Oracle no le afecta casi nada el aumento del iterador:

 CARLOS@XE.localhost> WITH ITERADOR
  2  AS(
  3     SELECT LEVEL
  4       FROM DUAL
  5       CONNECT BY LEVEL < 10001
  6  )
  7  SELECT count(1)
  8    FROM ITERADOR
  9  ;

  COUNT(1)
----------
     10000

Transcurrido: 00:00:00.14

Como se dijo al principio, a veces el paralelismo en sí puede ser una desventaja. Tom Kyte ponía un ejemplo: si voy a escribir un libro de cientos de páginas, hacerlo en paralelo (varios autores escribiendo capítulos por separado y juntando el resultado final) será más efectivo (más rápido) que que lo escriba una sola persona. Pero si voy a escribir un pequeño documento de diez páginas juntar varios autores para hacerlo (ponerse en contacto con ellos, coordinar y sincronizar el trabajo, etc…) puede llevar mucho más tiempo que lo que le llevaría a una sola persona.

Para ver el jaleo que se forma en Teradata vemos el ‘EXPLAIN’:

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
EXPLAIN
WITH RECURSIVE ITERADOR (ORDEN)
AS(
   SELECT 1(SMALLINT) ORDEN
     FROM MY_DB.MY_DUAL a
UNION ALL
   SELECT b.ORDEN + 1
     FROM MY_DB.MY_DUAL a,
          ITERADOR b
    WHERE b.ORDEN < 10000
)
SELECT *
  FROM ITERADOR
;

EXPLAIN
WITH RECURSIVE ITERADOR (ORDEN)
AS(
   SELECT 1(SMALLINT) ORDEN
     FROM MY_DB.MY_DUAL a
UNION ALL
   SELECT b.ORDEN + 1
     FROM MY_DB.MY_DUAL a,
          ITERADOR b
    WHERE b.ORDEN < 10000
)
SELECT *
  FROM ITERADOR
;

 *** Help information returned. 38 rows.
 *** Total elapsed time was 1 second.

Explanation
-----------------------------------------------------------------------
  1) First, we lock a distinct DW_USUARIO."pseudo table" for read on a
     RowHash to prevent global deadlock for MY_DB.MY_DUAL.
  2) Next, we lock MY_DB.MY_DUAL for read.
  3) We do an all-AMPs RETRIEVE step from MY_DB.MY_DUAL by way of
     an all-rows scan with no residual conditions into Spool 3
     (all_amps), which is built locally on the AMPs.  The size of Spool
     3 is estimated with high confidence to be 1 row.  The estimated
     time for this step is 0.01 seconds.
  4) We do an all-AMPs RETRIEVE step from Spool 3 by way of an all-rows
     scan into Spool 2 (all_amps), which is built locally on the AMPs.
     The size of Spool 2 is estimated with no confidence to be 1 row.
     The estimated time for this step is 0.01 seconds.
  5) We do an all-AMPs RETRIEVE step from MY_DB.MY_DUAL by way of
     an all-rows scan with no residual conditions into Spool 4
     (all_amps), which is duplicated on all AMPs.  The size of Spool 4
     is estimated with high confidence to be 20 rows.  The estimated
     time for this step is 0.01 seconds.
  6) We do an all-AMPs JOIN step from Spool 4 (Last Use) by way of an
     all-rows scan, which is joined to Spool 3 (Last Use) by way of an
     all-rows scan with a condition of ("ORDEN < 10000").  Spool 4 and
     Spool 3 are joined using a product join, with a join condition of
     ("(1=1)").  The result goes into Spool 5 (all_amps), which is
     built locally on the AMPs.  The size of Spool 5 is estimated with
     no confidence to be 1 row.  The estimated time for this step is
     0.01 seconds.
  7) We do an all-AMPs RETRIEVE step from Spool 5 (Last Use) by way of
     an all-rows scan into Spool 3 (all_amps), which is built locally
     on the AMPs.  The size of Spool 3 is estimated with no confidence
     to be 2 rows.  The estimated time for this step is 0.01 seconds.
     If one or more rows are inserted into spool 3, then go to step 4.
  8) We do an all-AMPs RETRIEVE step from Spool 2 (Last Use) by way of
     an all-rows scan into Spool 6 (all_amps), which is built locally
     on the AMPs.  The size of Spool 6 is estimated with no confidence
     to be 51 rows.  The estimated time for this step is 0.01 seconds.
  9) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.
  -> The contents of Spool 6 are sent back to the user as the result of
     statement 1.  The total estimated time is 0.03 seconds.

 BTEQ -- Enter your DBC/SQL request or BTEQ command:

Y aquí la sencillez en Oracle:

CARLOS@XE.localhost> SET AUTOTRACE TRACEONLY;
CARLOS@XE.localhost> WITH ITERADOR
  2  AS(
  3     SELECT LEVEL
  4       FROM DUAL
  5       CONNECT BY LEVEL < 10001
  6  )
  7  SELECT *
  8    FROM ITERADOR
  9  ;

10000 filas seleccionadas.

Transcurrido: 00:00:00.12

Plan de Ejecución
----------------------------------------------------------
Plan hash value: 2403765415

--------------------------------------------------------------------------------------
| Id  | Operation                     | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      |     1 |    13 |     2   (0)| 00:00:01 |
|   1 |  VIEW                         |      |     1 |    13 |     2   (0)| 00:00:01 |
|   2 |   CONNECT BY WITHOUT FILTERING|      |       |       |            |          |
|   3 |    FAST DUAL                  |      |     1 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------


Estadísticas
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
     136894  bytes sent via SQL*Net to client
       7706  bytes received via SQL*Net from client
        668  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      10000  rows processed

CARLOS@XE.localhost>

Y todo esto teniendo en cuenta que las ‘queries’ de Teradata se han ejecutado en un ‘server’ de 20 AMPs y las de Oracle en un XE local en un PC.

Saludos.

Carlos.

Anuncios

One Response to De recursividad e iteradores.

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: