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.
Escrito por CarlosAL 
CarlosAL 