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
0×14 = 20
0×12 = 18
0×35 = 53
0×00 = 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.
Escrito por CarlosAL 
