¿Cuándo recopilaste estadísticas por última vez (en Teradata)?

En Teradata las estadísticas -como en cualquier otra base de datos, o incluso más- son fundamentales para el rendimiento, ya que el optimizador vive de ellas para poder generar ‘query plans’ que sean eso: óptimos. Además, aquí no hay ‘hints’ ni ‘tweaks’ que hagan que el optimizador pueda pasarse sin ellas para desarrollar su tarea correctamente.

Quizá la forma más fácil de ver cuándo se recopilaron las estadísticas de una tabla por última vez sea ejecutando el comando HELP STATISTICS (o HELP STATS):

 BTEQ -- Enter your SQL request or BTEQ command:
HELP STATISTICS MY_DB.PRUEBA01;

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

Date     Time     Unique Values Column Names
-------- -------- ------------- ------------
11/12/20 18:53:00             4 ID_N1
11/12/20 18:53:13             4 C_TXT1

También se puede utilizar el ‘Teradata Statistics Wizard‘, que es una utilidad cliente que hace eso y mucho más…

Pero si por cualquier motivo no se quiere o no se puede utilizar ninguno de estos dos métodos Teradata recomienda una feísima ‘query‘ (SQL Data Definition Language – Detailed Topics. Chapter 1. pág 109: COLLECT STATISTICS (Optimizer Form) ) que además está mal:

SELECT c.databasenamei AS DatabaseName,
b.tvmnamei AS TableName,
a.fieldname AS ColumnName,
CAST((CASE WHEN SUBSTR(fieldstatistics,1,1) = ’07’XB
THEN ‘2007-‘
WHEN SUBSTR(fieldstatistics,1,1) = ’06’XB
THEN ‘2006-‘
WHEN SUBSTR(fieldstatistics,1,1) = ’05’XB
THEN ‘2005-‘
WHEN SUBSTR(fieldstatistics,1,1) = ’04’XB
THEN ‘2004-‘
WHEN SUBSTR(fieldstatistics,1,1) = ’03’XB
THEN ‘2003-‘
ELSE NULL
END)|| …

Y está mal porque:
·Utiliza un CASE con valores erróneos (debería ser ‘D7’XB, ‘D6’XB, ‘D5’XB…)
·Los valores del CASE están metidos ‘a mano’ (‘hard-coded’) y sin ninguna explicación acerca del porqué.
·Es una solución parcial: sólo funciona desde 2003 a 2007 (¡Hey! ¡Estamos en 2011! ¿no?).

Vamos a investigar un poco más a ver si podemos dar con una mejor solución para saber cuándo fue la última vez que recopilamos estadísticas para nuestra tabla PRUEBA01…

Para empezar, hay que decir que las estadísticas se guardan en una columna llamada ‘fieldstatistics’ de la tabla DBC.TVFIELDS. Además el tipo de la columna es un VARBYTE(16383). Es decir, Teradata guarda la información de las estadísticas de una columna en un formato un tanto ‘particular’.

Si miramos un poco más de cerca el contenido de ‘fieldstatistics’:

 BTEQ -- Enter your SQL request or BTEQ command:
SELECT
   c.databasenamei (VARCHAR(30)) "DatabaseName",
   b.tvmnamei      (VARCHAR(30)) "TableName",
   a.fieldname     (VARCHAR(30)) "ColumnName",
   a.fieldstatistics
  FROM
   dbc.tvfields a,
   dbc.tvm b,
   dbc.dbase c
 WHERE a.tableid = b.tvmid
   AND b.tablekind = 'T'
   AND b.databaseid = c.databaseid
   AND UPPER(TRIM(c.databasenamei)) = ('MY_DB')
   AND UPPER(TRIM(b.tvmnamei)) = ('PRUEBA01')
;

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

DatabaseName TableName ColumnName FieldStatistics
------------ --------- ---------- --------------------
MY_DB        PRUEBA01  ID_N1      DB070C141235002C0300...
MY_DB        PRUEBA01  C_TXT1     DB070C1412350D140300...

…vemos que la primera parte su contenido corresponde (un poco) con lo que la ‘query’ de la documentación de Teradata decía. Pero si nos fijamos más en detalle vemos que los dos primeros bytes son la representación hexadecimal del año (con los bytes cambiados de orden):

DB07 -> 0x07DB = 2011

y los siguientes bytes:

0x0C = 12
0x14 = 20
0x12 = 18
0x35 = 53
0x00 = 00

Y claro, comprendemos que corresponden a mes, día, hora, minuto, segundo… (con los mismos valores que salían en el ‘HELP STATS’).

Ahora ya lo vamos viendo claro. Sólamente con conocer como funciona SUBSTR() con datos tipo ‘BYTE’ y cómo convertir estos datos a ‘INTEGERs’ podemos hacer la magia:

 BTEQ -- Enter your SQL request or BTEQ command:
SELECT
   c.databasenamei (VARCHAR(30)) "DatabaseName",
   b.tvmnamei      (VARCHAR(30)) "TableName",
   a.fieldname     (VARCHAR(30)) "ColumnName",
   HASHBUCKET( CAST(SUBSTR(a.fieldstatistics,2,1) ||
                    SUBSTR(a.fieldstatistics,1,1) ||
                    '0000'XB AS BYTE(4)) ) (FORMAT '9999') "AÑO",
   HASHBUCKET( CAST('00'XB ||
                    SUBSTR(a.fieldstatistics,3,1) ||
                    '0000'XB AS BYTE(4)) ) (FORMAT '99') "MES",
   HASHBUCKET( CAST('00'XB ||
                    SUBSTR(a.fieldstatistics,4,1) ||
                    '0000'XB AS BYTE(4)) ) (FORMAT '99') "DÍA",
   HASHBUCKET( CAST('00'XB ||
                    SUBSTR(a.fieldstatistics,5,1) ||
                    '0000'XB AS BYTE(4)) ) (FORMAT '99') "HORA",
   HASHBUCKET( CAST('00'XB ||
                    SUBSTR(a.fieldstatistics,6,1) ||
                    '0000'XB AS BYTE(4)) ) (FORMAT '99') "MINUTO",
   HASHBUCKET( CAST('00'XB ||
                    SUBSTR(a.fieldstatistics,7,1) ||
                    '0000'XB AS BYTE(4)) ) (FORMAT '99') "SEGUNDO"
  FROM
   dbc.tvfields a,
   dbc.tvm b,
   dbc.dbase c
 WHERE a.tableid = b.tvmid
   AND b.tablekind = 'T'
   AND b.databaseid = c.databaseid
   AND UPPER(TRIM(c.databasenamei)) = ('MY_DB')
   AND UPPER(TRIM(b.tvmnamei)) = ('PRUEBA01')
   AND a.fieldstatistics IS NOT NULL
;

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

DatabaseName TableName ColumnName  AÑO MES DÍA HORA MINUTO SEGUNDO
------------ --------- ---------- ---- --- --- ---- ------ -------
MY_DB        PRUEBA01  ID_N1      2011  12  20   18     53      00
MY_DB        PRUEBA01  C_TXT1     2011  12  20   18     53      13

Y esta ‘SELECT’ si que es ‘universal’. Sin CASE’s, valores ‘hard-coded’ ni nada de eso.

A partir de aquí, el infinito es el límite:

 BTEQ -- Enter your SQL request or BTEQ command:
SELECT TRIM(DatabaseName) || '.' ||
       TRIM(TableName) || '.' ||
       TRIM(ColumnName)(VARCHAR(64)) "COLUMNA",
       TRIM("AÑO")    || '/' ||
       TRIM("MES")    || '/' ||
       TRIM("DÍA")    || ' ' ||
       TRIM("HORA")   || ':' ||
       TRIM("MINUTO") || ':' ||
       TRIM("SEGUNDO")   "FECHA"
  FROM (
         SELECT
            c.databasenamei (VARCHAR(30)) "DatabaseName",
            b.tvmnamei      (VARCHAR(30)) "TableName",
            a.fieldname     (VARCHAR(30)) "ColumnName",
            HASHBUCKET( CAST(SUBSTR(a.fieldstatistics,2,1) ||
                             SUBSTR(a.fieldstatistics,1,1) ||
                             '0000'XB AS BYTE(4)) ) (FORMAT '9999') "AÑO",
            HASHBUCKET( CAST('00'XB ||
                             SUBSTR(a.fieldstatistics,3,1) ||
                             '0000'XB AS BYTE(4)) ) (FORMAT '99') "MES",
            HASHBUCKET( CAST('00'XB ||
                             SUBSTR(a.fieldstatistics,4,1) ||
                             '0000'XB AS BYTE(4)) ) (FORMAT '99') "DÍA",
            HASHBUCKET( CAST('00'XB ||
                             SUBSTR(a.fieldstatistics,5,1) ||
                             '0000'XB AS BYTE(4)) ) (FORMAT '99') "HORA",
            HASHBUCKET( CAST('00'XB ||
                             SUBSTR(a.fieldstatistics,6,1) ||
                             '0000'XB AS BYTE(4)) ) (FORMAT '99') "MINUTO",
            HASHBUCKET( CAST('00'XB ||
                             SUBSTR(a.fieldstatistics,7,1) ||
                             '0000'XB AS BYTE(4)) ) (FORMAT '99') "SEGUNDO"
           FROM
            dbc.tvfields a,
            dbc.tvm b,
            dbc.dbase c
          WHERE a.tableid = b.tvmid
            AND b.tablekind = 'T'
            AND b.databaseid = c.databaseid
            AND UPPER(TRIM(c.databasenamei)) = ('MY_DB')
            AND UPPER(TRIM(b.tvmnamei)) = ('PRUEBA01')
            AND a.fieldstatistics IS NOT NULL
       ) PRE
ORDER BY 1,2
;

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

COLUMNA                    FECHA
-------------------------- -------------------
MY_DB.PRUEBA01.C_TXT1      2011/12/20 18:53:13
MY_DB.PRUEBA01.ID_N1       2011/12/20 18:53:00

Y la creación de vistas, macros y demás utilidades basados en lo que hemos visto aquí será ya un juego de niños:

--
-- LAST_STATS. View that shows the DatabaseName, TableName, ColumnName, Date & Time of the
--             last COLLECT STATS executed.
--
-- Author: Carlos Álvarez.
--
REPLACE VIEW MY_DB.LAST_STATS
AS
SELECT DatabaseName,
       TableName   ,
       ColumnName  ,
       CAST(TRIM("AÑO") || '-' ||
            TRIM("MES") || '-' ||
            TRIM("DÍA") AS DATE) Fecha,
       CAST(TRIM("HORA")   || ':' ||
            TRIM("MINUTO") || ':' ||
            TRIM("SEGUNDO") AS TIME(0)) Hora
  FROM (
         SELECT
                c.databasenamei (VARCHAR(30)) "DatabaseName",
                b.tvmnamei      (VARCHAR(30)) "TableName",
                a.fieldname     (VARCHAR(30)) "ColumnName",
                HASHBUCKET( CAST(SUBSTR(a.fieldstatistics,2,1) ||
                                 SUBSTR(a.fieldstatistics,1,1) ||
                                 '0000'XB AS BYTE(4)) ) (FORMAT '9999') "AÑO",
                HASHBUCKET( CAST('00'XB ||
                                 SUBSTR(a.fieldstatistics,3,1) ||
                                 '0000'XB AS BYTE(4)) ) (FORMAT '99') "MES",
                HASHBUCKET( CAST('00'XB ||
                                 SUBSTR(a.fieldstatistics,4,1) ||
                                 '0000'XB AS BYTE(4)) ) (FORMAT '99') "DÍA",
                HASHBUCKET( CAST('00'XB ||
                                 SUBSTR(a.fieldstatistics,5,1) ||
                                 '0000'XB AS BYTE(4)) ) (FORMAT '99') "HORA",
                HASHBUCKET( CAST('00'XB ||
                                 SUBSTR(a.fieldstatistics,6,1) ||
                                 '0000'XB AS BYTE(4)) ) (FORMAT '99') "MINUTO",
                HASHBUCKET( CAST('00'XB ||
                                 SUBSTR(a.fieldstatistics,7,1) ||
                                 '0000'XB AS BYTE(4)) ) (FORMAT '99') "SEGUNDO"
           FROM
              dbc.tvfields a,
              dbc.tvm b,
              dbc.dbase c
          WHERE a.tableid = b.tvmid
            AND b.tablekind = 'T'
            AND b.databaseid = c.databaseid
            AND a.fieldstatistics IS NOT NULL
      ) PRE
;

y así:

BTEQ -- Enter your SQL request or BTEQ command:
SELECT * FROM MY_DB.LAST_STATS WHERE TABLENAME='PRUEBA01';

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

DatabaseName TableName ColumnName Fecha      Hora
------------ --------- ---------- ---------- --------
MY_DB        PRUEBA01  ID_N1      2011-12-20 18:53:00
MY_DB        PRUEBA01  C_TXT1     2011-12-20 18:53:13

Por supuesto, podríamos seguir escarbando en el contenido de ‘fieldstatistics’ para sacar más información (unique values, por ejemplo), pero eso ya es otra historia…

Saludos.

Carlos.

Una respuesta a ¿Cuándo recopilaste estadísticas por última vez (en Teradata)?

  1. […] capítulos anteriores vimos cómo interpretar los datos de fecha y hora de la última recopilación de estadísticas para […]

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: