Backup/Restore: P2V the hard way.

El asunto parece sencillo:  coges un equipo, haces un ‘backup‘ del sistema completo y lo restauras en otra parte. En la web hay mil teorías de cómo hacerlo. Es muy sencillo -dicen-. Hasta que realmente TIENES que hacerlo.

La realidad: Tenemos un equipo (un viejo PIV con 512 MB) en el que hay instalado un Windows 2003 Server con varias aplicaciones imprescindibles (aplicaciones ‘web’ que corren en el IIS y varias transferencias de ficheros que dependen del ‘FTP Server‘) del que nadie se ha acordado -ni preocupado- de hacer un ‘backup’… hasta que un día la fuente de alimentación se muere repentinamente, a consecuencia de un pico de tensión.

Como quiera que estamos tabajando para la Administración, esto supone una llamada al CAU, que vengan dos técnicos a ver el equipo, que llamen al fabricante (pues resulta que el vejestorio todavía está en garantía), que el fabricante se digne a venir a ver el equipo, valore los daños y -por fin- cambien la fuente de alimentación. Resultado: más de una semana sin los imprescindibles servicios que nos prestaba el cacharrillo. Visto lo visto, mi jefe decide que hay que hacer algo por si la contingencia se vuelve a producir. Mi compañero S. y yo nos ponemos manos a la obra.

OK. Lo primero es buscar una herramienta de ‘backup‘. Miramos herramientas gratuitas y probamos Paragon Backup & Recovery 10 Free Edition. Después de instalarlo, nos dice que no funciona con Windows 2003 Server. La siguiente opción es EASEUS Todo Backup 1.0 que promete hacer ‘backups‘ fiables y generar CD’s de recuperación (boot-CD) para el caso de que el vejestorio sufra un percance mayor que su fuente de alimentación inoperativa.

Bajamos e instalamos el software y realizamos una copia de seguridad del sistema en un disco USB externo. El EASEUS Todo Backup genera dicho backup en ficheros de algo más de 4GB (creemos que para que quepan en DVD’s) sin mayores problemas. Hacemos una verificación del backup, por si las moscas. Todo OK, siempre según el EASEUS Todo Backup.

También generamos un CD de recuperación, que el software se encarga de crear y grabar en un CD.

Todo Backup 10

Hasta aquí la cosa va como la seda. Coser y cantar. Pero ahora hay que probar que podemos recuperar el backup obtenido. El asunto no tendría mayor importancia… a no ser que no fuera la Administración: no hay equipos donde recuperar (restore) el ‘backup‘ realizado. Y ni de coña lo vamos a intentar en el propio equipo del que hicimos el ‘backup‘: si algo falla, nos quedamos en bragas.

Así que sopesamos las opciones. Obviamente, iremos a alguna opción de virtualización: el ‘restore‘ en una máquina virtual nos aseguraría un ‘restore‘ en una máquina real, e incluso nos permitiría tener el Windows 2003 Server corriendo virtualizado hasta disponer de un equipo de repuesto para nuestro matusalén. Comenzamos con VirtualBox, que es gratis.

Corremos VirtualBox en un equipo con Windows XP. Generamos una máquina virtual con un disco de espacio suficiente para recuperar el backup. Introducimos el CD de recuperación generado con EASEUS Todo Backup y arrancamos. Vemos que se trata de algún Linux que intenta arrancar… hasta que un pantallazo hace que la máquina virtual se reinicie. Así una y otra vez. Estamos casi seguros de que es algo relacionado con la resolución de la pantalla de la máquina virtual, pero aunque probamos diferentes configuraciones con “VBoxManage” siempre llegamos al mismo resultado. Así que VirtualBox fuera.

La siguiente: la siempre fiable VMWare Workstation. Como sólo queremos ver que el ‘backup’ es recuperable, nos valdrá con una versión de evaluación. Mismo procedimiento: Generamos una máquina virtual con un disco de espacio suficiente para recuperar el backup. Introducimos el CD de recuperación generado con EASEUS Todo Backup y arrancamos. Aquí las cosas empiezan a mejorar: aunque el pantallazo es el mismo que en el VirtualBox, VMWare se da cuenta y nos ofrece cambiar la resolución de la pantalla. Elegimos 800×600, volvemos a arrancar y… voìlá. Tenemos el Linux arrancado con el EASEUS Todo Backup en orden de revista.

Comenzamos con el proceso de recupeación del ‘backup‘. Aquí pasan cosas un poco raras: las pantallas para elegir de dónde seleccionar los ficheros de ‘backup‘ para la recuperación y el disco de destino a recuperar, a veces vemos dos unidades (el DVD y el disco USB) y a veces tres (el DVD, el disco USB y el disco duro virtual). Creemos que es porque el proceso necesita un tiempo para reconocer todas las unidades, y nosotros estamos ya un poco impacientes.

Finalmente elegimos ficheros origen, disco de destino y ejecutamos la recuperación, que lógicamente se toma su tiempo. Al terminar tenemos -en teoría- un sistema virtual idéntico al sistema real del que hicimos el backup.

“Lo tenemos casi”, pensamos. Sólo hay que arrancar el sistema y ver el Windows 2003 Server ‘alive and kicking’.

Así que arrancamos la máquina virtual. Aparece la pantalla de Windows 2003 Server con el ‘ascensor’ horizontal. “Ya está, ya está” pero:

Blue Screen of Death

El “Blue Screen of Death“. Con el STOP: 0X0000007B. Las maldiciones se deben de oir hasta en la China.

El msdn dice que esos errores son de problemas para encontrar un medio de arranque. Así que la respuesta está en el disco duro virtual. Probamos todas las opciones que nos permite VMWare, pero siempre con el mismo resultado: BSD.

Ya no sabemos qué hacer. Como la respuesta no aparece del lado de Microsoft, buscamos en el lado de VMWare.

En la base de conocimientos damos con algo que nos pone las orejas tiesas:

For all versions of GSX Server, Workstation and VMware Server, this problem can also be caused by the use of a third party IDE controller driver on your
native Windows 2000, Windows XP or Windows Server 2003 partition.

“A ver si va a ser esto…” Nos vamos corriendo al equipo con el Windows 2003 Server. Miramos el Administrador de dispositivos y ¡ahí está!: hay controladores SATA ‘intel’ instalados (suponemos que con algún CD de instalación del equipo). Esto hace que, al no ser controladores ‘genéricos Windows’, cuando el sistema intenta arrancar en la máquina virtual con estos ‘drivers‘ no reconoce el disco duro virtual, ya que espera uno específico para el ‘driver‘.

¿Cómo solventarlo? Con un nuevo perfil ‘hardware‘. Creamos un nuevo perfil ‘hardware‘ y le sustituimos todos los ‘drivers‘ de ‘third party‘ por controladores genéricos IDE de Windows.
Arrancamos el sistema con este nuevo perfil y vuelta a empezar: nueva copia de seguridad del sistema.

Tras otro buen rato esperando el ‘backup‘ al USB y un nuevo ‘restore‘ en la máquina virtual arrancanda con el CD de recuperación de EASEUS Todo Backup, tenemos todo a punto de caramelo…

Cruzamos los dedos. Arrancamos la máquina virtual con el disco restaurado. Aparece la pantalla de Windows 2003 Server con el ‘ascensor’ horizontal… ¡y arranca!.

Tenemos un sistema Windows 2003 Server virtualizado que es una copia exacta del que hay en el cacharro renqueante. Y puede funcionar (si configuramos la red virtual como ‘bridged‘) en sustitución del sistema físico original en el caso de una avería permanente o irrecuperable.

Todo esto sólo nos ha llevado tres días…

Saludos.

Carlos.

Skew Factor en Teradata

Uno de los conceptos más importantes en Teradata es el ‘skew factor‘ (que podríamos traducir algo así como ‘factor de sesgo’) en la distribución de los datos de las tablas entre los AMP’s.

Y es tan importante porque está directamente ligado al concepto de paralelismo y al funcionamiento de Teradata. Como hemos dicho otras veces, el paralelismo tiene sus -casi siempre- cosas buenas (muchos procesos trabajando a la vez) y sus -a veces- cosas malas (la tarea tarda tanto como el último de los procesos…). Es aquí donde entra el ‘skew factor‘ en la distribución de datos entre los AMP’s: si todos los AMP’s tienen un volumen de datos similar la tarea se realizará en paralelo de la forma más eficiente. Si por el contrario hay unos AMP’s que tienen poco volumen de datos y otros que tienen mucho volumen, estos últimos tendrán que hacer casi todo el trabajo y la tarea no terminará hasta que no acabe el último de ellos (aquí se abre un universo de metáforas con el mundo laboral y tal…)

Por ello hay quien define el ‘skew factor‘ como la función inversa de la ‘Parallel Efficiency‘ (eficiencia paralela): cuanto menor sea el ‘skew factor‘ mayor será la ‘Parallel Efficiency‘.

La fórmula para calcular el ‘skew factor‘ es:

100-((Media de CurrentPerm/Máximo de CurrentPerm) X 100).

Por regla general se considera aceptable un ‘skew factor‘ menor o igual que 10, mientras que si el ‘skew factor‘ es superior a 10 se recomienda revisar la definición del ‘primary index‘ para que el algoritmo ‘hash‘ distribuya los datos de la tabla de una forma más equitativa entre los AMP’s.

Otra cosa a tener en cuenta es que si la tabla es muy pequeña el ‘skew factor‘ deja de ser significativo.

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
SELECT TABLENAME,
       SUM(CURRENTPERM) CURRENTPERM,
       SUM(PEAKPERM) PEAKPERM,
       CAST((100-(AVG(CURRENTPERM)/MAX(CURRENTPERM)*100)) AS DECIMAL(5,2))
       "SKEWFACTOR(%)"
  FROM DBC.TABLESIZE
 WHERE DATABASENAME = 'MY_DB'
 GROUP BY TABLENAME
 ORDER BY TABLENAME
;

 *** Query completed. 24 rows found. 4 columns returned.
 *** Total elapsed time was 1 second.

TableName  CURRENTPERM    PEAKPERM SKEWFACTOR(%)
---------- ----------- ----------- -------------
TABLA01      3,911,680   3,911,680          6.60
TABLA02         61,952      61,952         24.38
TABLA03        467,456     467,456         10.49
TABLA04      3,987,968   3,987,968          7.49
TABLA05      1,980,928   1,980,928          7.00
TABLA06         38,400      38,400          6.25
TABLA07        913,920     913,920          7.03
TABLA08         21,504      21,504         30.00
TABLA09         76,800      76,800         31.82
TABLA10      1,320,448   1,320,448          7.23
TABLA11        557,056     557,056         12.26
TABLA12        748,032     748,032          7.53
TABLA13      1,263,616   1,263,616          7.22
TABLA14    128,400,896 128,400,896          8.71
TABLA15    121,289,728 121,289,728         13.52
TABLA16     23,704,064  23,704,064          7.52
TABLA17     67,391,488  67,391,488          8.35
TABLA18         34,304      34,304         16.25
TABLA19        748,032     748,032          9.81
TABLA20         76,288      76,288         17.22
TABLA21         72,192      72,192         21.67
TABLA22         55,808      55,808          9.17
TABLA23         35,840      35,840         12.50
TABLA24        860,672     860,672          4.49

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

Saludos.

Carlos.

Importando fechas julianas a Teradata.

Actualmente estoy trabajando en un proceso de incorporación de datos de una Base de datos OLTP Oracle a un Data Warehouse Teradata. La cosa no parecía muy complicada hasta que nos hemos dado cuenta que dicha base de datos Oracle es a su vez resultado de una migración -y no muy bien hecha- de un DB2. Entre otras particularidades nos hemos encontrado con que hay tablas sin ‘primary key‘ y que las tablas y columnas tienen nombres restringidos a 8 dígitos (herencia DB2, suponemos). También que las tablas presentan cláusulas de almacenamiento (‘STORAGE’) con ‘INITIAL’ 1K, 2K, 3K,… (¡Hey! ¡Estamos en 2009! ¿alguien ha oído hablar de los LMT’s?) lo que para un ‘datablock’ de 8K (suponemos) resulta ser un tanto ’surrealista’.

Pero lo mejor es que todas las fechas aparecen como NUMBER(7). Al preguntar, nos dicen que son fechas ‘en juliano’ (‘julian date‘).

El calendario juliano es un calendario en el que se cuentan los días desde el 1 de enero del 4713 antes de Cristo a mediodía. Así el 14/07/2009 será el 2455027.

Oracle maneja las fechas julianas sin problemas mediante el formato ‘J’ de las funciones TO_CHAR y TO_DATE:

CARLOS@XE.localhost> SELECT TO_CHAR(SYSDATE, 'J') FROM DUAL;

TO_CHAR
-------
2455027

CARLOS@XE.localhost> SELECT TO_DATE('2455027', 'J') FROM DUAL;

TO_DATE(
--------
14/07/09

Con lo que la incorporación de enteros (NUMBER(7)) como fechas julianas a Oracle no presentaría mayor problema que el uso de un TO_DATE().

Pero Teradata es otra cuestión. Teradata no presenta algo análogo al formato ‘J’ de Oracle y hay que pensar cómo convertir esos NUMBER(7) a fechas.

El primer intento es obvio, pero fallido:

SELECT CAST('-4713/01/01' AS DATE FORMAT 'YYYY/MM/DD') + 2455027;
 *** Failure 2665 Invalid date.
                Statement# 1, Info =0
 *** Total elapsed time was 1 second.

De hecho, Teradata no soporta fechas anteriores al 1 de enero de 01 (al menos directamente):

Internally, Teradata Database stores each DATE value as a four-byte signed integer using the following formula:

(YEAR – 1900) * 10000 + (MONTH * 100) + DAY

where the YEAR, MONTH, and DAY components, defined appropriately for the Gregorian calendar, have the following range of values:

Range of values
Component Minimum Maximum
YEAR 1 9999
MONTH 1 12
DAY 1 28, 29, 30, or 31 (depending on the month and year)

En efecto:

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
SELECT CAST((1 - 1900) * 10000 + (1 * 100) + 1 AS DATE);

SELECT CAST((1 - 1900) * 10000 + (1 * 100) + 1 AS DATE);

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

((((1-1900)*10000)+(1*100))+1)
------------------------------
                      01/01/01

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
SELECT CAST((1 - 1900) * 10000 + (1 * 100) + 1 AS DATE) - 1;

SELECT CAST((1 - 1900) * 10000 + (1 * 100) + 1 AS DATE) - 1;
 *** Failure 2665 Invalid date.
                Statement# 1, Info =0
 *** Total elapsed time was 1 second.

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
SELECT CAST('0001/01/01' AS DATE FORMAT 'YYYY/MM/DD');

SELECT CAST('0001/01/01' AS DATE FORMAT 'YYYY/MM/DD');

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

'0001/01/01'
------------
  0001/01/01

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
SELECT CAST('0001/01/01' AS DATE FORMAT 'YYYY/MM/DD') - 1;

SELECT CAST('0001/01/01' AS DATE FORMAT 'YYYY/MM/DD') - 1;
 *** Failure 2665 Invalid date.
                Statement# 1, Info =0
 *** Total elapsed time was 1 second.

Entonces ¿cómo nos las ingeniamos? La respuesta está en algo llamado traslación. En vez de irnos al 1 de enero de 4713 antes de Cristo como origen de la aritmética de fechas, podemos hacer una traslación a -digamos- el 1 de enero de 1900: tomamos la fecha juliana de ese día, que resulta ser el número 2415021, y sacamos la diferencia con la fecha juliana recibida sabiendo la fecha base tomada. (También es bueno recordar que Teradata guarda este 1 de enero de 1900 como 101).

Así para nuestro 14/07/2009 (2455027 en juliano) tendremos que:

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
SELECT CAST(101 AS DATE) + (CAST(2455027 AS INTEGER) - 2415021) (FORMAT 'DD/MM/YYYY');

SELECT CAST(101 AS DATE) + (CAST(2455027 AS INTEGER) - 2415021) (FORMAT 'DD/MM/YYYY');

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

(101+(2455027-2415021))
-----------------------
             14/07/2009

Y con esta técnica podemos incorporar las enrevesadas fechas julianas NUMBER(7) de origen a simples fechas DATE que todo el mundo entiende.

Saludos.

Carlos.

Tablas SET y MULTISET en Teradata (II).

Como vimos en Tablas SET y MULTISET en Teradata, la verificación de duplicados en tablas SET y MULTISET es un poco rara para los INSERT…SELECT’s (eliminación silenciosa de duplicados). Ha habido gente que me ha pedido que explique un poco más el comportamiento de Teradata respecto a los duplicados, y lo cierto es que es un tema un tanto confuso, ya que el comportamiento que se presenta a la hora de la verificación de duplicados en sentencias INSERT INTO…VALUES es completamente distinto al de las sentencias INSERT…SELECT.

Lo vemos con un ejemplo que utiliza las mismas tablas PRUEBA03_S_UPI y PRUEBA05_S_NUPI(COL01) usadas en el artículo anterior:

 Teradata BTEQ 08.02.03.03 for WIN32.
 Copyright 1984-2006, NCR Corporation. ALL RIGHTS RESERVED.
 Enter your logon or BTEQ command:
.LOGON MY_TD/MY_DB

.LOGON MY_TD/MY_DB
Password:

 *** Logon successfully completed.
 *** Teradata Database Release is V2R.06.02.01.18
 *** Teradata Database Version is 06.02.01.17
 *** Transaction Semantics are BTET.
 *** Character Set Name is 'ASCII'.

 *** Total elapsed time was 3 seconds.
 BTEQ -- Enter your DBC/SQL request or BTEQ command:

CREATE SET TABLE MY_DB.PRUEBA03_S_UPI (COL01 INTEGER NOT NULL)
UNIQUE PRIMARY INDEX (COL01);

CREATE SET TABLE MY_DB.PRUEBA03_S_UPI (COL01 INTEGER NOT NULL)
UNIQUE PRIMARY INDEX (COL01);

 *** Table has been created.
 *** Total elapsed time was 1 second.

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT INTO MY_DB.PRUEBA03_S_UPI(COL01) VALUES(1);

INSERT INTO MY_DB.PRUEBA03_S_UPI(COL01) VALUES(1);

 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT INTO MY_DB.PRUEBA03_S_UPI(COL01) VALUES(1);

INSERT INTO MY_DB.PRUEBA03_S_UPI(COL01) VALUES(1);
 *** Failure 2801 Duplicate unique prime key error in MY_DB.PRUEBA03_S_UPI.
                Statement# 1, Info =0
 *** Total elapsed time was 1 second.

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT INTO MY_DB.PRUEBA05_S_NUPI(COL01) VALUES(1);

INSERT INTO MY_DB.PRUEBA05_S_NUPI(COL01) VALUES(1);

 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT INTO MY_DB.PRUEBA05_S_NUPI(COL01) VALUES(1);

INSERT INTO MY_DB.PRUEBA05_S_NUPI(COL01) VALUES(1);
 *** Failure 2802 Duplicate row error in MY_DB.PRUEBA05_S_NUPI.
                Statement# 1, Info =0
 *** Total elapsed time was 1 second.

Como podemos ver en el ejemplo, la verificación de duplicados es muy diferente al caso de las sentencias INSERT…SELECT. Aquí no hay eliminación silenciosa de duplicados, sino que la verificación de duplicados ocurre mediante un mecanismo de errores.

En el primer ejemplo (tabla MY_DB.PRUEBA03_S_UPI) la verificación se lleva a cabo lanzando el error 2801 Duplicate unique prime key error in MY_DB.PRUEBA03_S_UPI, es decir, la validación de unicidad del índice PRUEBA03_S_UPI.

En cambio, en el segundo ejemplo (tabla MY_DB.PRUEBA05_S_NUPI) la verificación se lleva a cabo lanzando el error 2802 Duplicate row error in MY_DB.PRUEBA05_S_NUPI. Aquí la validación de duplicados es la propia de la tabla definida ‘SET’.

Por último indicar que Teradata realiza la verificación de duplicados para tablas ‘SET’ siempre y cuando no haya otra restricción de unicidad (UPI, USI…) en cuyo caso verifica la unicidad de la restricción y se ahorra la de la definición ‘SET’ (como hemos visto en el ejemplo con MY_DB.PRUEBA03_S_UPI):

It is also true that if you create a SET NUPI table, the system performs duplicate row checks by default unless you place a uniqueness constraint on the table. Unique constraint enforcement is often less a less costly method of enforcing row uniqueness than system duplicate row checks.

Además, la verificación de restricciones de unicidad es menos costosa que la de unicidad de filas en tablas ‘SET’.

Saludos.

Carlos.

Heterogeneous Services: SQL a Teradata desde Oracle.

Oracle presenta unos productos de acceso a bases de datos no-Oracle que básicamente se pueden clasificar en dos grupos: Transparent Gateways y Heterogeneous Services.

Transparent Gateways son módulos de acceso a determinadas bases de datos (SQLServer, Sybase, Teradata, DB2…) Estos módulos están optimizados y ofrecen un mayor rendimiento, por lo que consisten en licencias que se compran por separado.

Por otra parte, los Heterogeneous Services (o Generic Connectivity) están basados en accesos por ODBC/OLEDB y son gratis, aunque el rendimiento es menor.

Ambos persiguen un mismo objetivo: acceder desde Oracle a bases de datos no-Oracle y que sea el propio Oracle el que se encargue de los aspectos de mapeo de tipos, transferencias, etc, etc…

Vamos a ver como podemos hacer para que desde una ventana sqlplus conectada a una instancia Oracle veamos los datos de tablas de una base de datos Teradata con simples ‘SELECTS’. Por supuesto, utilizaremos la segunda solucion (ODBC/OLEDB y GRATIS).

Vamos a ello.

¿Que vamos a necesitar?

Lo primero, que la infraestructura de los Heterogeneous Services (Tablas, ‘packages’, sinónimos, etc…) esté en la base de datos Oracle. Para ello ejecutamos como SYS el ’script’ caths.sql:

SQL*Plus: Release 10.2.0.1.0 - Production on Jue Jun 18 13:22:55 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Introduzca el nombre de usuario: sys@XE.localhost as sysdba
Introduzca la contraseña:

Conectado a:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SYS@XE.localhost> @C:\bla\bla\...\10.2.0\server\RDBMS\ADMIN\caths.sql
create role hs_admin_role

...

Cuerpo del paquete creado.

Procedimiento PL/SQL terminado correctamente.

Confirmación terminada.

Lo siguiente en configurar es un origen de datos ODBC. Es muy importante que configuremos el origen de datos como DE SISTEMA (no de usuario).

El nuestro se va a llamar TD_HS:

Una vez con el origen de datos ODBC listo comenzamos con la configuración de Oracle:

Para que se entere el ‘listener’ de la conexión debemos informarle. Así en el listener.ora añadiremos una entrada bajo SID_LIST_LISTENER:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\bla\bla\...\server)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\bla\bla\...\server)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (ORACLE_HOME = C:\bla\bla\...\server)
      (SID_NAME = TD_HS)
      (PROGRAM = hsodbc)
    )
  )

SID_NAME debe corresponder con nuestro orígen de datos ODBC. PROGRAM es el nombre del módulo de Heterogeneous Services para ODBC (hsodbc.exe)

También necesitaremos una entrada en el tnsnames.ora:

teradata.remote=
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)
              (HOST = XXX.XXX.XXX.XXX)
              (PORT = 1521)
     )
     (CONNECT_DATA =
         (SERVICE_NAME = TD_HS)
         (SID = TD_HS)
     )
     (HS = OK)
)

Como bien de ve en la configuración será el propio ‘listener’ de Oracle el que se encargue de gestionar las conexiones al ODBC.
HS = OK identifica que será un origen Heterogeneous Services. El SID / SERVICE_NAME debe ser una vez más el nombre del origen de datos ODBC. El HOST y el PORT son los del equipo local (donde está Oracle).

Por último, necesitamos un fichero para la configuración de Heterogeneous Services para el origen de datos. Este fichero se llamará init<SID name>.ora y estará en el directorio \hs\admin de la instalación: C:\bla\bla\…\server\hs\admin\initTD_HS.ora

#
# HS init parameters
#

HS_FDS_CONNECT_INFO = TD_HS
HS_FDS_TRACE_LEVEL = 1
HS_FDS_TRACE_FILE_NAME = C:\bla\bla\...\server\hs\admin\initTD_HS.trc

Lo importante aquí es la línea HS_FDS_CONNECT_INFO = TD_HS (una vez más el SID). Los otros son para establecer el nivel de las trazas y su fichero.

Si hemos llegado hasta aquí, podemos probar cómo están las cosas con un simple ‘tnsping’:

C:\>tnsping teradata.remote

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 18-JUN-2009 13:46:53

Copyright (c) 1997, 2005, Oracle.  All rights reserved.

Archivos de parámetros utilizados:
C:\bla\bla\...\server\network\admin\sqlnet.ora

Adaptador TNSNAMES utilizado para resolver el alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = XXX.XXX.XXX.XXX) (PORT = 1521))
(CONNECT_DATA = (SERVICE_NAME = TD_HS) (SID = TD_HS)) (HS = OK))
Realizado correctamente (10 mseg)

C:\>

El ‘listener‘ escucha para nuestro TD_HS.

Lo más duro ya está hecho. Ahora sólo nos queda crear un simple dblink en Oracle:

CARLOS@XE.localhost> CREATE DATABASE LINK teradata.remote
CONNECT TO carlos IDENTIFIED BY xxxxxxxx
USING 'teradata.remote';

Enlace con la base de datos creado.

Si hemos hecho todo bien (y parece que sí), podríamos acceder a la base de datos Teradata con SQL Oracle desde sqlplus:

CARLOS@XE.localhost> SELECT COD_PROVINCIA,
  2                         DESC_PROVINCIA
  3                    FROM MY_DB.PROVINCIA@teradata.remote
  4                ORDER BY COD_PROVINCIA;

COD_PROVINCIA DESC_PROVINCIA
------------- --------------------
            1 Álava
            2 Albacete
            3 Alicante/Alacant
            4 Almería
            5 Ávila
            6 Badajoz
            7 Balears (Illes)
            8 Barcelona
            9 Burgos
           10 Cáceres
           11 Cádiz
           12 Castellón/Castelló
           13 Ciudad Real
           14 Córdoba
           15 Coruña (A)
           16 Cuenca
           17 Girona
           18 Granada
           19 Guadalajara
           20 Guipúzcoa
           21 Huelva
           22 Huesca
           23 Jaén
           24 León
           25 Lleida
           26 Rioja (La)
           27 Lugo
           28 Madrid
           29 Málaga
           30 Murcia
           31 Navarra
           32 Ourense
           33 Asturias
           34 Palencia
           35 Palmas (Las)
           36 Pontevedra
           37 Salamanca
           38 Santa Cruz de Tenerife
           39 Cantabria
           40 Segovia
           41 Sevilla
           42 Soria
           43 Tarragona
           44 Teruel
           45 Toledo
           46 Valencia/València
           47 Valladolid
           48 Vizcaya
           49 Zamora
           50 Zaragoza
           51 Ceuta
           52 Melilla
           99 Extranjero
          100 Desconocido

54 filas seleccionadas.

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

----------------------------------------------------------------------------------------------------------
| Id  | Operation        | Name          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Inst   |IN-OUT|
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |               |  2000 |   126K|       |    87   (3)| 00:00:02 |        |   |
|   1 |  SORT ORDER BY   |               |  2000 |   126K|   312K|    87   (3)| 00:00:02 |        |   |
|   2 |   REMOTE         | PROVINCIA     |  2000 |   126K|       |    52   (0)| 00:00:01 | TERAD~ | R->S |
----------------------------------------------------------------------------------------------------------

Remote SQL Information (identified by operation id):
----------------------------------------------------

   2 - SELECT "COD_PROVINCIA","DESC_PROVINCIA" FROM "MY_DB"."PROVINCIA" (accessing
       'TERADATA.REMOTE' )

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

CARLOS@XE.localhost>

¡Sí señor! Y el autotrace muestra que la ejecución ha sido ‘REMOTE’, como era de esperar.

Y lo que es mejor: podemos utilizar funciones nativas Oracle en el ‘SELECT’ a los datos Teradata:

CARLOS@XE.localhost> SELECT REPLACE(DESC_PROVINCIA,'M','#')
  2    FROM MY_DB.PROVINCIA@teradata.remote
  3   WHERE COD_PROVINCIA = 28;

REPLACE(DESC_PROVINCIA,'M','#')
---------------------------------------------------------------------------------------------------
#adrid

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

--------------------------------------------------------------------------------------------------
| Id  | Operation        | Name          | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |               |    20 |  1300 |    52   (0)| 00:00:01 |        |      |
|   1 |  REMOTE          | PROVINCIA     |    20 |  1300 |    52   (0)| 00:00:01 | ALMAC~ | R->S |
--------------------------------------------------------------------------------------------------

Remote SQL Information (identified by operation id):
----------------------------------------------------

   1 - SELECT "COD_PROVINCIA","DESC_PROVINCIA" FROM "MY_DB"."PROVINCIA" WHERE
       "COD_PROVINCIA"=28 (accessing 'TERADATA.REMOTE' )

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

CARLOS@XE.localhost>

Y por supuesto, con los ‘GRANTS’ necesarios, no tenemos por qué limitarnos a ‘SELECTS’…

Saludos.

Carlos.

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.
   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.

Cadenas a filas (‘tokenizer’) en Teradata.

Una y otra vez encuentro usuarios de foros preguntando por funcionalidades que de una u otra forma tienen como origen el problema de convertir cadenas de caracteres en filas (lo que algunos llaman ‘tokenizer‘). En pocas palabras, se trata de convertir una cadena de caracteres en filas que contienen los fragmentos de dicha cadena (palabras, campos, etc…) a partir de un caracter separador (‘token‘).

Ya escribí sobre cómo hacerlo en Oracle, pero en Teradata la cosa es un poco más complicada. Partiremos aquí también de una solución basada en ‘queries’ recursivas.

Hay que decir una vez más que por la arquitectura de Teradata la recursividad es algo que no le sienta demasiado bien, así que es posible encontrar problemas de rendimiento si se utiliza esta técnica en según que ‘queries‘, pero puede ser una solución de la que partir para muchos problemas cotidianos relacionados con el tratamiento de cadenas y de conversión de ‘queries’ desde otros SGBDR’s (Oracle, por ejemplo).

Vamos a utilizar cadenas separadas con guiones (‘-’) por claridad, pero valdría cualquier ‘token’ (incluso el espacio ‘ ‘).

Primero, como siempre, creamos una tabla de prueba y la cargamos con datos de ejemplo:

DROP TABLE MY_DB.PRUEBA01;

DROP TABLE MY_DB.PRUEBA01;

 *** Table has been dropped.
 *** Total elapsed time was 1 second.

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

CREATE SET TABLE MY_DB.PRUEBA01 ,
     NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT
     (
      ID INTEGER NOT NULL,
      CTXT VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC NULL)
PRIMARY INDEX ( ID );

CREATE SET TABLE MY_DB.PRUEBA01 ,
     NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT
     (
      ID INTEGER NOT NULL,
      CTXT VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC NULL)
PRIMARY INDEX ( ID );

 *** Table has been created.
 *** Total elapsed time was 1 second.

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT INTO MY_DB.PRUEBA01 (ID, CTXT) VALUES (0,'');

INSERT INTO MY_DB.PRUEBA01 (ID, CTXT) VALUES (0,'');

 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT INTO MY_DB.PRUEBA01 (ID, CTXT) VALUES (1,'1-2-3-4-5');

INSERT INTO MY_DB.PRUEBA01 (ID, CTXT) VALUES (1,'1-2-3-4-5');

 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT INTO MY_DB.PRUEBA01 (ID, CTXT) VALUES (10,'10-200-3000-40000');

INSERT INTO MY_DB.PRUEBA01 (ID, CTXT) VALUES (10,'10-200-3000-40000');

 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT INTO MY_DB.PRUEBA01 (ID, CTXT) VALUES (100,'100-20-3');

INSERT INTO MY_DB.PRUEBA01 (ID, CTXT) VALUES (100,'100-20-3');

 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT INTO MY_DB.PRUEBA01 (ID, CTXT) VALUES (1000,'1000-200000');

INSERT INTO MY_DB.PRUEBA01 (ID, CTXT) VALUES (1000,'1000-200000');

 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT INTO MY_DB.PRUEBA01 (ID, CTXT) VALUES (30,'En-un-lugar-de-La-Mancha');

INSERT INTO MY_DB.PRUEBA01 (ID, CTXT) VALUES (30,'En-un-lugar-de-La-Mancha');

 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT INTO MY_DB.PRUEBA01 (ID, CTXT) VALUES (9999,NULL);

INSERT INTO MY_DB.PRUEBA01 (ID, CTXT) VALUES (9999,NULL);

 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.

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

SELECT ID, CTXT
  FROM MY_DB.PRUEBA01
 ORDER BY ID, CTXT
;

SELECT ID, CTXT
  FROM MY_DB.PRUEBA01
 ORDER BY ID, CTXT
;

 *** Query completed. 7 rows found. 2 columns returned.
 *** Total elapsed time was 1 second.

         ID  CTXT
-----------  ---------------------------------------------------------------------
          0
          1  1-2-3-4-5
         10  10-200-3000-40000
         30  En-un-lugar-de-La-Mancha
        100  100-20-3
       1000  1000-200000
       9999  (null)

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

La técnica que utilizaremos es ésta: iremos recorriendo las cadenas de forma recursiva sacando cada parte (cada fragmento entre ‘tokens‘) a una fila. Necesitaremos también el resto de la cadena para ir avanzando a la siguiente parte y tratarla. Así hasta terminarlo todo. Una vez entendida la estrategia, no hay más que un poco de INDEX() por aquí y un poco de SUBSTR() por allá. SQL puro y duro. Nada de magia, vamos.

Una cosa más: deberemos tener cuidado con los nulos (‘NULL’) y las cadenas vacías (”), que como sabemos (y a diferencia de Oracle) no son lo mismo.

Así pues, manos a la obra:

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
WITH RECURSIVE RECPRUEBA(ID,
                         CTXT,
                         PARTNO,
                         PART,
                         POST)
AS
(
SELECT ID,
       CTXT,
       1 PARTNO,
       CASE WHEN INDEX(CTXT,'-') > 0
               THEN SUBSTR(CTXT, 1, INDEX(CTXT,'-') - 1 )
            ELSE CTXT END PART,
       CASE WHEN INDEX(CTXT,'-') > 0
               THEN SUBSTR(CTXT, INDEX(CTXT,'-') + 1 )
            ELSE '' END POST
  FROM MY_DB.PRUEBA01
 WHERE CHARACTERS(COALESCE(CTXT,'')) > 0
 UNION ALL
SELECT a.ID,
       a.CTXT,
       b.PARTNO + 1 PARTNO,
       CASE WHEN INDEX(b.POST,'-') > 0
               THEN SUBSTR(b.POST, 1, INDEX(b.POST,'-') - 1 )
            ELSE b.POST END PART,
       CASE WHEN INDEX(b.POST,'-') > 0
               THEN SUBSTR(b.POST, INDEX(b.POST,'-') + 1 )
            ELSE '' END POST
  FROM MY_DB.PRUEBA01 a,
       RECPRUEBA b
 WHERE a.ID = b.ID
   AND CHARACTERS(b.POST) > 0
)
SELECT *
  FROM RECPRUEBA
  ORDER BY ID, PARTNO;

WITH RECURSIVE RECPRUEBA(ID,
                         CTXT,
                         PARTNO,
                         PART,
                         POST)
AS
(
SELECT ID,
       CTXT,
       1 PARTNO,
       CASE WHEN INDEX(CTXT,'-') > 0
               THEN SUBSTR(CTXT, 1, INDEX(CTXT,'-') - 1 )
            ELSE CTXT END PART,
       CASE WHEN INDEX(CTXT,'-') > 0
               THEN SUBSTR(CTXT, INDEX(CTXT,'-') + 1 )
            ELSE '' END POST
  FROM MY_DB.PRUEBA01
 WHERE CHARACTERS(COALESCE(CTXT,'')) > 0
 UNION ALL
SELECT a.ID,
       a.CTXT,
       b.PARTNO + 1 PARTNO,
       CASE WHEN INDEX(b.POST,'-') > 0
               THEN SUBSTR(b.POST, 1, INDEX(b.POST,'-') - 1 )
            ELSE b.POST END PART,
       CASE WHEN INDEX(b.POST,'-') > 0
               THEN SUBSTR(b.POST, INDEX(b.POST,'-') + 1 )
            ELSE '' END POST
  FROM MY_DB.PRUEBA01 a,
       RECPRUEBA b
 WHERE a.ID = b.ID
   AND CHARACTERS(b.POST) > 0
)
SELECT *
  FROM RECPRUEBA
  ORDER BY ID, PARTNO;

 *** Query completed. 20 rows found. 5 columns returned.
 *** Total elapsed time was 1 second.

   ID  CTXT                      PARTNO  PART    POST
-----  ------------------------- ------  ------- ---------------------
    1  1-2-3-4-5                      1  1       2-3-4-5
    1  1-2-3-4-5                      2  2       3-4-5
    1  1-2-3-4-5                      3  3       4-5
    1  1-2-3-4-5                      4  4       5
    1  1-2-3-4-5                      5  5
   10  10-200-3000-40000              1  10      200-3000-40000
   10  10-200-3000-40000              2  200     3000-40000
   10  10-200-3000-40000              3  3000    40000
   10  10-200-3000-40000              4  40000
   30  En-un-lugar-de-La-Mancha       1  En      un-lugar-de-La-Mancha
   30  En-un-lugar-de-La-Mancha       2  un      lugar-de-La-Mancha
   30  En-un-lugar-de-La-Mancha       3  lugar   de-La-Mancha
   30  En-un-lugar-de-La-Mancha       4  de      La-Mancha
   30  En-un-lugar-de-La-Mancha       5  La      Mancha
   30  En-un-lugar-de-La-Mancha       6  Mancha
  100  100-20-3                       1  100     20-3
  100  100-20-3                       2  20      3
  100  100-20-3                       3  3
 1000  1000-200000                    1  1000    200000
 1000  1000-200000                    2  200000 

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

Y vòilá.

Con esto podríamos buscar, por ejemplo, todas las segundas partículas de las cadenas (campo número 2 en un ‘registro’ de texto):

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
WITH RECURSIVE RECPRUEBA(ID,
                         CTXT,
                         PARTNO,
                         PART,
                         POST)
AS
(
SELECT ID,
       CTXT,
       1 PARTNO,
       CASE WHEN INDEX(CTXT,'-') > 0
               THEN SUBSTR(CTXT, 1, INDEX(CTXT,'-') - 1 )
            ELSE CTXT END PART,
       CASE WHEN INDEX(CTXT,'-') > 0
               THEN SUBSTR(CTXT, INDEX(CTXT,'-') + 1 )
            ELSE '' END POST
  FROM MY_DB.PRUEBA01
 WHERE CHARACTERS(COALESCE(CTXT,'')) > 0
 UNION ALL
SELECT a.ID,
       a.CTXT,
       b.PARTNO + 1 PARTNO,
       CASE WHEN INDEX(b.POST,'-') > 0
               THEN SUBSTR(b.POST, 1, INDEX(b.POST,'-') - 1 )
            ELSE b.POST END PART,
       CASE WHEN INDEX(b.POST,'-') > 0
               THEN SUBSTR(b.POST, INDEX(b.POST,'-') + 1 )
            ELSE '' END POST
  FROM MY_DB.PRUEBA01 a,
       RECPRUEBA b
 WHERE a.ID = b.ID
   AND CHARACTERS(b.POST) > 0
)
SELECT ID,
       CTXT,
       PART
  FROM RECPRUEBA
 WHERE PARTNO = 2
  ORDER BY ID;

WITH RECURSIVE RECPRUEBA(ID,
                         CTXT,
                         PARTNO,
                         PART,
                         POST)
AS
(
SELECT ID,
       CTXT,
       1 PARTNO,
       CASE WHEN INDEX(CTXT,'-') > 0
               THEN SUBSTR(CTXT, 1, INDEX(CTXT,'-') - 1 )
            ELSE CTXT END PART,
       CASE WHEN INDEX(CTXT,'-') > 0
               THEN SUBSTR(CTXT, INDEX(CTXT,'-') + 1 )
            ELSE '' END POST
  FROM MY_DB.PRUEBA01
 WHERE CHARACTERS(COALESCE(CTXT,'')) > 0
 UNION ALL
SELECT a.ID,
       a.CTXT,
       b.PARTNO + 1 PARTNO,
       CASE WHEN INDEX(b.POST,'-') > 0
               THEN SUBSTR(b.POST, 1, INDEX(b.POST,'-') - 1 )
            ELSE b.POST END PART,
       CASE WHEN INDEX(b.POST,'-') > 0
               THEN SUBSTR(b.POST, INDEX(b.POST,'-') + 1 )
            ELSE '' END POST
  FROM MY_DB.PRUEBA01 a,
       RECPRUEBA b
 WHERE a.ID = b.ID
   AND CHARACTERS(b.POST) > 0
)
SELECT ID,
       CTXT,
       PART
  FROM RECPRUEBA
 WHERE PARTNO = 2
  ORDER BY ID;

 *** Query completed. 5 rows found. 3 columns returned.
 *** Total elapsed time was 1 second.

   ID  CTXT                     PART
-----  ------------------------ -------
    1  1-2-3-4-5                2
   10  10-200-3000-40000        200
   30  En-un-lugar-de-La-Mancha un
  100  100-20-3                 20
 1000  1000-200000              200000

Otros usos podrían ser indexar partículas de un texto, contar palabras, contar repeticiones, componer el texto al revés, etc, etc… En fin, el cielo es el límite.

Saludos.

Carlos.

ANSI OUTER JOINS: parecido no es igual.

¿Qué es un ‘outer join‘? Si estáis leyendo esto, seguramente ya lo sabeis. Un ‘outer join’ es un ‘join’ en el que las filas de la tabla principal son mantenidas en el resultado aunque no existan filas que les correspondan en la tabla con la que se combina.

La sintaxis ANSI es:

SELECT …
FROM Tabla1
(LEFT) OUTER JOIN Tabla2 ON Tabla1.Col1 = Tabla2.Col1
[AND Tabla1.Col2 = Tabla2.Col2 ...]

La teoría es fácil y la sintaxis también, pero a veces sentencias ‘muy parecidas’ que en un primer vistazo asumimos como idénticas pueden no serlo tanto y meternos en problemas al devolver resultados inesperados.

Vamos a verlo en Teradata, pero primero las tablas de rigor:

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
CREATE SET TABLE MY_DB.PRUEBA01 ,
     NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT
     (
      ID    INTEGER     NOT NULL,
      CTXT  VARCHAR(10) NOT NULL )
PRIMARY INDEX ( ID );

CREATE SET TABLE MY_DB.PRUEBA01 ,
     NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT
     (
      ID    INTEGER     NOT NULL,
      CTXT  VARCHAR(10) NOT NULL )
PRIMARY INDEX ( ID );

 *** Table has been created.
 *** Total elapsed time was 1 second.

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

CREATE SET TABLE MY_DB.PRUEBA02 ,
     NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT
     (
      ID    INTEGER     NOT NULL,
      CTXT  VARCHAR(10) NOT NULL )
PRIMARY INDEX ( ID );

CREATE SET TABLE MY_DB.PRUEBA02 ,
     NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT
     (
      ID    INTEGER     NOT NULL,
      CTXT  VARCHAR(10) NOT NULL )
PRIMARY INDEX ( ID );

 *** Table has been created.
 *** Total elapsed time was 1 second.

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

INSERT INTO MY_DB.PRUEBA01 (ID, CTXT) VALUES (1, 'UNO');

INSERT INTO MY_DB.PRUEBA01 (ID, CTXT) VALUES (1, 'UNO');

 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT INTO MY_DB.PRUEBA01 (ID, CTXT) VALUES (2, 'DOS');

INSERT INTO MY_DB.PRUEBA01 (ID, CTXT) VALUES (2, 'DOS');

 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT INTO MY_DB.PRUEBA01 (ID, CTXT) VALUES (3, 'TRES');

INSERT INTO MY_DB.PRUEBA01 (ID, CTXT) VALUES (3, 'TRES');

 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.

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

INSERT INTO MY_DB.PRUEBA02 (ID, CTXT) VALUES (1, 'ONE');

INSERT INTO MY_DB.PRUEBA02 (ID, CTXT) VALUES (1, 'ONE');

 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT INTO MY_DB.PRUEBA02 (ID, CTXT) VALUES (2, 'TWO');

INSERT INTO MY_DB.PRUEBA02 (ID, CTXT) VALUES (2, 'TWO');

 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT INTO MY_DB.PRUEBA02 (ID, CTXT) VALUES (3, 'THREE');

INSERT INTO MY_DB.PRUEBA02 (ID, CTXT) VALUES (3, 'THREE');

 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.

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

Las tablas quedan tal que así:

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
SELECT *
  FROM MY_DB.PRUEBA01;

SELECT *
  FROM MY_DB.PRUEBA01;

 *** Query completed. 3 rows found. 2 columns returned.
 *** Total elapsed time was 1 second.

         ID  CTXT
-----------  ----------
          2  DOS
          3  TRES
          1  UNO

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
SELECT *
  FROM MY_DB.PRUEBA02;

SELECT *
  FROM MY_DB.PRUEBA02;

 *** Query completed. 3 rows found. 2 columns returned.
 *** Total elapsed time was 1 second.

         ID  CTXT
-----------  ----------
          2  TWO
          3  THREE
          1  ONE

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

Ahora vamos con la query: un ‘OUTER JOIN’ ANSI con una condición ‘WHERE’:

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
SELECT a.ID,
       a.CTXT,
       b.CTXT
  FROM MY_DB.PRUEBA01 a
  LEFT OUTER JOIN MY_DB.PRUEBA02 b
             ON a.ID = b.ID
 WHERE a.ID > 1;

SELECT a.ID,
       a.CTXT,
       b.CTXT
  FROM MY_DB.PRUEBA01 a
  LEFT OUTER JOIN MY_DB.PRUEBA02 b
             ON a.ID = b.ID
 WHERE a.ID > 1;

 *** Query completed. 2 rows found. 3 columns returned.
 *** Total elapsed time was 1 second.

         ID  CTXT        CTXT
-----------  ----------  ----------
          2  DOS         TWO
          3  TRES        THREE

OK. Ahora vamos a escribir la misma ‘query’ de otra manera para obtener los mismos resultados:

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
SELECT a.ID,
       a.CTXT,
       b.CTXT
  FROM MY_DB.PRUEBA01 a
  LEFT OUTER JOIN MY_DB.PRUEBA02 b
             ON a.ID = b.ID
   AND a.ID > 1;

SELECT a.ID,
       a.CTXT,
       b.CTXT
  FROM MY_DB.PRUEBA01 a
  LEFT OUTER JOIN MY_DB.PRUEBA02 b
             ON a.ID = b.ID
   AND a.ID > 1;

 *** Query completed. 3 rows found. 3 columns returned.
 *** Total elapsed time was 1 second.

         ID  CTXT        CTXT
-----------  ----------  ----------
          2  DOS         TWO
          3  TRES        THREE
          1  UNO         (null)

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

¿Cómo? ¡Aparece una última línea que no debería aparecer! ¿Qué es esto? ¿Acaso un ‘bug’ de Teradata? No. No hay nada errónero en el resultado, lo erróneo está en nuestra cabeza y lo que esperamos ver. Quizá apereca más claro si lo escribimos así:

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
SELECT a.ID,
       a.CTXT,
       b.CTXT
  FROM MY_DB.PRUEBA01 a
  LEFT OUTER JOIN MY_DB.PRUEBA02 b
             ON ( a.ID = b.ID
                  AND
                  a.ID > 1 )
;

SELECT a.ID,
       a.CTXT,
       b.CTXT
  FROM MY_DB.PRUEBA01 a
  LEFT OUTER JOIN MY_DB.PRUEBA02 b
             ON ( a.ID = b.ID
                  AND
                  a.ID > 1 )
;

 *** Query completed. 3 rows found. 3 columns returned.
 *** Total elapsed time was 1 second.

         ID  CTXT        CTXT
-----------  ----------  ----------
          2  DOS         TWO
          3  TRES        THREE
          1  UNO         (null)

Ahora se ilumina una lucecilla y empezamos a ver qué es lo que está pasando: Teradata tiene razón y está simplemente siguiendo las reglas y mecanismos de los ‘outer joins’. En el primer ejemplo efectúa un ‘outer join’ con la condición ‘ON a.ID = b.ID’ y a este resultado le aplica el filtro del ‘where’: ‘WHERE a.ID > 1′, donde se elimina la fila correspondiente a a.ID = 1.

En el segundo ejemplo combina todas las filas de la tabla MY_DB.PRUEBA01 a con las de la tabla MY_DB.PRUEBA02 b y las que no cumplen la condición (aquí es donde está la trampa) ‘AND a.ID > 1′ les asigna filas ‘nulas’ de MY_DB.PRUEBA02 b. De ahí que aparezca la fila “1, ‘UNO’, (null)”: No hay filtro que elimine luego esa fila.

Así que las partículas ‘WHERE a.ID > 1′ y ‘AND a.ID > 1′ aunque parezcan iguales, no lo son.

Esto no es algo propio de Teradata. Oracle funciona de la misma manera:

SQL*Plus: Release 10.2.0.1.0 - Production on Mié Jun 3 11:33:20 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Introduzca el nombre de usuario: carlos@XE.localhost
Introduzca la contraseña:

Conectado a:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

CARLOS@XE.localhost> CREATE TABLE PRUEBA01(IDN INTEGER NOT NULL,
  2                        CTXT  VARCHAR2(10) NOT NULL,
  3                        CONSTRAINT PRUEBA01_PK PRIMARY KEY(IDN)
  4                        )
  5  ;

Tabla creada.

CARLOS@XE.localhost> CREATE TABLE PRUEBA02(IDN INTEGER NOT NULL,
  2                        CTXT  VARCHAR2(10) NOT NULL,
  3                        CONSTRAINT PRUEBA02_PK PRIMARY KEY(IDN)
  4                        )
  5  ;

Tabla creada.

CARLOS@XE.localhost> INSERT INTO PRUEBA01(IDN, CTXT) VALUES (1,'UNO');

1 fila creada.

CARLOS@XE.localhost> INSERT INTO PRUEBA01(IDN, CTXT) VALUES (2,'DOS');

1 fila creada.

CARLOS@XE.localhost> INSERT INTO PRUEBA01(IDN, CTXT) VALUES (3,'TRES');

1 fila creada.

CARLOS@XE.localhost>
CARLOS@XE.localhost> INSERT INTO PRUEBA02(IDN, CTXT) VALUES (1,'ONE');

1 fila creada.

CARLOS@XE.localhost> INSERT INTO PRUEBA02(IDN, CTXT) VALUES (2,'TWO');

1 fila creada.

CARLOS@XE.localhost> INSERT INTO PRUEBA02(IDN, CTXT) VALUES (3,'THREE');

1 fila creada.

CARLOS@XE.localhost> commit;

Confirmación terminada.

CARLOS@XE.localhost> SELECT a.IDN,
  2         a.CTXT,
  3         b.CTXT
  4    FROM PRUEBA01 a
  5    LEFT OUTER JOIN PRUEBA02 b
  6               ON a.IDN = b.IDN
  7   WHERE a.IDN > 1;

       IDN CTXT       CTXT
---------- ---------- ----------
         2 DOS        TWO
         3 TRES       THREE

CARLOS@XE.localhost> SELECT a.IDN,
  2         a.CTXT,
  3         b.CTXT
  4    FROM PRUEBA01 a
  5    LEFT OUTER JOIN PRUEBA02 b
  6               ON a.IDN = b.IDN
  7     AND a.IDN > 1;

       IDN CTXT       CTXT
---------- ---------- ----------
         1 UNO
         2 DOS        TWO
         3 TRES       THREE

Por último, los ‘oracleros’ pueden ver a cual de las dos sintaxis ANSI corresponde su sintaxis nativa:

CARLOS@XE.localhost> SELECT a.IDN,
  2         a.CTXT,
  3         b.CTXT
  4    FROM PRUEBA01 a,
  5         PRUEBA02 b
  6   WHERE a.IDN = b.IDN(+)
  7     AND a.IDN > 1;

       IDN CTXT       CTXT
---------- ---------- ----------
         2 DOS        TWO
         3 TRES       THREE

CARLOS@XE.localhost>

En efecto: con la ‘buena’: aquí el ‘AND’ se considera en el ‘WHERE’.

Saludos.

Carlos.

DISTINCT vs. GROUP BY en Teradata

En cualquier base de datos es muy común tener que contar los elementos diferentes (columnas de filas) que hay en una tabla.

En Teradata, como en cualquier otro SGBDR, se puede hacer muy fácilmente mediante cláusulas ‘DISTINCT’ o ‘GROUP BY’. No obstante los mecanismos de implementación que Teradata utiliza para una y otra son muy diferentes. Ante eso, llega la inevitable pregunta “¿Y cuál de los dos es mejor?” Y la respuesta es, como de costumbre, ‘”Depende.”

La arquitectura de Teradata está pensada por y para sacar partido del paralelismo. Este paralelismo tiene su lado bueno (hacer una tarea en paralelo hace que el trabajo se reparta y sea más efectivo) y su negativo (el trabajo terminará cuando el último de los procesos en paralelo termine). En general, suele darse el primero de ellos, pero a veces puede ocurrir que el paralelismo (o la falta de él) juegue en nuestra contra. Es por eso que la máxima de las bases de datos “Conoce tus datos” en Teradata tiene un añadido: “Y su distribución”.

Respecto a esto, hay muchas ideas preconcebidas y bastante desconocimiento de como Teradata implementa el paralelismo, como por ejemplo, aquí.

Pero sigamos con el asunto.

Veamos cómo funciona DISTINCT:

‘DISTINCT’ hace que todos los ‘AMPs’ en los que haya datos los manden a un ’spool’ que se distribuye entre los ‘AMPs’ por el valor elegido (es por eso que no importa si la columna es ‘primary index’ o no, como también se decía aquí ). Una vez en el ’spool’ y distribuido según sus valores se hace un ‘SORT’ con eliminación de duplicados y se devuelve el resultado total al usuario.

Veamos ahora cómo funciona ‘GROUP BY’:

‘GROUP BY’ realiza un ‘SUM’ local a cada ‘AMP’ por el valor elegido (GROUP BY). Utilizando los resultados de cada ‘AMP’ se computan los totales y se envían a un ’spool’ (que también estará distribuido). De ahí se hará un ‘retrieve’ a otro ’spool’ local a cada AMP, desde donde se devuelve el resultado al usuario.

Visto esto nos damos cuenta de que cuando hay muchos valores distintos un ‘DISTINCT’ debe ir mejor, ya que nos ahorramos los ‘SUMS’ que harían los ‘AMPs’ si hiciéramos un ‘GROUP BY’ (estaríamos intentando sumar valores cuasi-únicos: mucho trabajo para poco resultado) y además el resultado en el ’spool’ estará muy distribuido. Luego sólo queda el ‘SORT’ y la devolución de los datos.

Ocurre justo lo contrario cuando hay pocos valores distintos: ahí un ‘DISTINCT’ mandaría todos los datos a unos pocos ‘AMPs’ y además habría que efectuar un ‘SORT’ con eliminación de gran cantidad de filas. ‘GROUP BY’ saca aquí partido del paralelismo: los ‘AMPs’ hacen ‘SUMS’ locales, encargándose de lo más duro del trabajo para tener un ’spool’ final ya listo para devolverse.

Vamos a ver si lo que decimos es cierto.

Nos creamos una tabla de pruebas:

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

CREATE SET TABLE MY_DB.PRUEBA11 ,
     NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT
     (
      ID1 INTEGER NOT NULL,
      ID2 INTEGER NOT NULL,
      ID3 INTEGER NOT NULL )
PRIMARY INDEX ( ID1 );

CREATE SET TABLE MY_DB.PRUEBA11 ,
     NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT
     (
      ID1 INTEGER NOT NULL,
      ID2 INTEGER NOT NULL,
      ID3 INTEGER NOT NULL )
PRIMARY INDEX ( ID1 );

 *** Table has been created.
 *** Total elapsed time was 1 second.

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

Ahora insertamos varios millones de filas de una tabla ‘fuente’. Para nuestro ejemplo cargaremos los datos (además de en el ‘PRIMARY INDEX’) en dos columnas sin indexar, para obligar a que se efectúen ‘FULL SCANS’ y para ver que el paralelismo también funciona cuando accedemos a columnas sin ‘hashing‘ (al contrario de lo que erróneamente se decía aquí ). Necesitaremos dos distribuciones diferentes de los datos: una muy distribuída (muchos valores distintos) y otra poco distribuida (pocos valores distintos):

INSERT INTO MY_DB.PRUEBA11 (ID1,
                            ID2,
                            ID3)
                     SELECT ID,
                            ID,
                            ID MOD 10
                       FROM MY_DB.TABLA_FUENTE;

INSERT INTO MY_DB.PRUEBA11 (ID1,
                            ID2,
                            ID3)
                     SELECT ID,
                            ID,
                            ID MOD 10
                       FROM MY_DB.TABLA_FUENTE;

 *** Insert completed. 26658715 rows added.
 *** Total elapsed time was 20 seconds.

26.658.715 parecen suficientes para una prueba.

Ahora vamos con las ‘queries’ y veremos cuál tarda más.

Empezamos con la columna con muchos valores distintos:

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
SELECT COUNT(1) FROM (
SELECT DISTINCT ID2 FROM MY_DB.PRUEBA11
) a
;

SELECT COUNT(1) FROM (
SELECT DISTINCT ID2 FROM MY_DB.PRUEBA11
) a
;

 *** Query completed. One row found. One column returned.
 *** Total elapsed time was 28 seconds.

   Count(1)
-----------
   26658715

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
SELECT COUNT(1) FROM (
SELECT ID2 FROM MY_DB.PRUEBA11 GROUP BY ID2
) a
;

SELECT COUNT(1) FROM (
SELECT ID2 FROM MY_DB.PRUEBA11 GROUP BY ID2
) a
;

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

   Count(1)
-----------
   26658715

En efecto: El ‘DISTINCT’ lo hemos hecho en 28 segundos, mientras que el ‘GROUP BY’ nos ha llevado 2 minutos y 20 segundos (casi el tiempo anterior por cinco).

Vamos ahora con la columna con pocos valores distintos:

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
SELECT COUNT(1) FROM (
SELECT DISTINCT ID3 FROM MY_DB.PRUEBA11
) a
;

SELECT COUNT(1) FROM (
SELECT DISTINCT ID3 FROM MY_DB.PRUEBA11
) a
;

 *** Query completed. One row found. One column returned.
 *** Total elapsed time was 23 seconds.

   Count(1)
-----------
         10

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
SELECT COUNT(1) FROM (
SELECT ID3 FROM MY_DB.PRUEBA11 GROUP BY ID3
) a;

SELECT COUNT(1) FROM (
SELECT ID3 FROM MY_DB.PRUEBA11 GROUP BY ID3
) a;

 *** Query completed. One row found. One column returned.
 *** Total elapsed time was 5 seconds.

   Count(1)
-----------
         10

Aquí ocurre lo contrario: el ‘GROUP BY’ sólo tarda 5 segundos, mientras que el ‘DISTINCT’ se va a 23 (también casi el tiempo anterior por cinco). Estamos sacando partido del paralelismo del ‘SUM’ local de los AMPs. La diferencia sería aún mayor si en vez de 10 valores distintos hubiera sólo dos, tres o cuatro… ya que estaríamos penalizando aún más el ‘DISTINCT’.

Así pues, la conclusión es que hay que usar ‘DISTINCT’ con columnas con muchos valores distintos y ‘GROUP BY’ con columnas con un pequeño número de valores distintos.

En cualquier caso, los valores obtenidos -incluso los peores- no están mal para 26.658.715 filas. ( Y ni mucho menos se ‘cuelgan’ como también se decía aquí ):

Otra cosa que vemos es que un ‘DISTINCT’ siempre dará los resultados EN ORDEN (ya que el último paso efectuado es un ‘SORT-MERGE’), mientras un ‘GROUP BY’ los devolverá en orden aleatorio.

Finalmente, un consejo: ante la duda (sin conocer los datos) yo intentaría siempre un ‘GROUP BY’.

Saludos.

Carlos.

Cambiando NULL a NOT NULL (o viceversa) en Teradata

La sintaxis para cambiar de NULL a NOT NULL una columna en Teradata tiene truco.

Digamos que tenemos una tabla:

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
SHOW TABLE MY_DATABASE.PRUEBA01;

SHOW TABLE MY_DATABASE.PRUEBA01;

 *** Text of DDL statement returned.
 *** Total elapsed time was 1 second.

---------------------------------------------------------------

CREATE SET TABLE MY_DATABASE.PRUEBA01 ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT
     (
      ID_N1 INTEGER NOT NULL,
      TEXTO VARCHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC)
PRIMARY INDEX ( ID_N1 );

Supongamos que queremos cambiar la columna TEXTO de NULL (como está) a NOT NULL.
Lo normal -sobre todo si venimos de Oracle- es que nos pase esto:

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
ALTER TABLE MY_DATABASE.PRUEBA01 MODIFY TEXTO NOT NULL;

ALTER TABLE MY_DATABASE.PRUEBA01 MODIFY TEXTO NOT NULL;
 *** Failure 3707 Syntax error, expected something like a 'CHECK' keyword between the word 'TEXTO' and the 'NULL' keyword.
                Statement# 1, Info =52
 *** Total elapsed time was 1 second.

En efecto, en Teradata MODIFY es sólo para ‘check constraints’.

La solución es un poquillo rara:

ALTER TABLE MY_DATABASE.PRUEBA01 ADD TEXTO NOT NULL;

ALTER TABLE MY_DATABASE.PRUEBA01 ADD TEXTO NOT NULL;

 *** Table has been modified.
 *** Total elapsed time was 1 second.

SHOW TABLE MY_DATABASE.PRUEBA01;

 *** Text of DDL statement returned.
 *** Total elapsed time was 1 second.

----------------------------------------------------------------------

CREATE SET TABLE MY_DATABASE.PRUEBA01 ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT
     (
      ID_N1 INTEGER NOT NULL,
      TEXTO VARCHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL)
PRIMARY INDEX ( ID_N1 );

Así es. Aunque utilizar ADD para modificar una columna pueda repugnar un poco, es así como se hace en Teradata.

En realidad ADD sirve para añadir o cambiar columnas en Teradata:

ADD column_name
data_type_declaration
data_type_attributes
column_storage_attributes
column_constraint_attributes

to add or change the specified column and its specified attributes.

Nota: Si la columna en cuestión está indexada no se puede cambiar de NULL a NOT NULL (o viceversa).

Saludos.

Carlos.