 |
 |
Archives of the TeradataForum
Message Posted: Tue, 20 Dec 2005 @ 19:15:00 GMT
| Subj: | | Re: Copying Stats Definition |
| |
| From: | | Diehl, Robert |
Here is query you can use to see the stats already collected. Also below is a view that shows lots of good information about the stats.
I cannot take credit for creating this query or view. I got them from others and might have made simple changes. I have not verified they
work for V2R6.0 yet. But they are valid for V2R5.1
SELECT
'collect stats ' || case when samplesize = 0 then ' ' else 'using sample
' end|| ' on ' || trim(DatabaseName) || '.' || TableName || case when
statstype = 'I' then ' Index ( ' else ' column ( ' end || ColumnName ||
');',
StatsType,
(
(HASHBUCKET
(SUBSTRING(StatsData FROM 2 FOR 1) ||
SUBSTRING(StatsData FROM 1 FOR 1) (BYTE(4))
) - 1900
) * 10000
+
(HASHBUCKET
('00'xb || SUBSTRING(StatsData FROM 3 FOR 1) (BYTE(4))
)
) * 100
+
(HASHBUCKET
(
'00'xb || SUBSTRING(StatsData FROM 4 FOR 1) (BYTE(4))
)
)
) (DATE) AS CollectDate,
(CAST(
(HASHBUCKET
(CAST('00'xb || SUBSTRING(StatsData FROM 5 FOR 1) AS BYTE(4))
) (FORMAT '99:')
) ||
(HASHBUCKET
(CAST('00'xb || SUBSTRING(StatsData FROM 6 FOR 1) AS BYTE(4))
) (FORMAT '99:')
) ||
(HASHBUCKET
(CAST('00'xb || SUBSTRING(StatsData FROM 7 FOR 1) AS BYTE(4))
) (FORMAT '99')
)
AS TIME(0))) AS CollectTime
FROM
(
SELECT
d.Databaseid as DATABASE_ID,
t.tvmid as TVM_ID,
d.databasename AS DatabaseName,
t.tvmname AS TableName,
MAX(CASE WHEN i.FieldPosition = 1 THEN TRIM(c.FieldName) ELSE ''
END) ||
MAX(CASE WHEN i.FieldPosition = 2 THEN ', ' || TRIM(c.FieldName)
ELSE '' END) ||
MAX(CASE WHEN i.FieldPosition = 3 THEN ', ' || TRIM(c.FieldName)
ELSE '' END) ||
MAX(CASE WHEN i.FieldPosition = 4 THEN ', ' || TRIM(c.FieldName)
ELSE '' END) ||
MAX(CASE WHEN i.FieldPosition = 5 THEN ', ' || TRIM(c.FieldName)
ELSE '' END) ||
MAX(CASE WHEN i.FieldPosition = 6 THEN ', ' || TRIM(c.FieldName)
ELSE '' END) ||
MAX(CASE WHEN i.FieldPosition = 7 THEN ', ' || TRIM(c.FieldName)
ELSE '' END) ||
MAX(CASE WHEN i.FieldPosition = 8 THEN ', ' || TRIM(c.FieldName)
ELSE '' END) ||
MAX(CASE WHEN i.FieldPosition = 9 THEN ', ' || TRIM(c.FieldName)
ELSE '' END) ||
MAX(CASE WHEN i.FieldPosition = 10 THEN ', ' || TRIM(c.FieldName)
ELSE '' END) ||
MAX(CASE WHEN i.FieldPosition = 11 THEN ', ' || TRIM(c.FieldName)
ELSE '' END) ||
MAX(CASE WHEN i.FieldPosition = 12 THEN ', ' || TRIM(c.FieldName)
ELSE '' END) ||
MAX(CASE WHEN i.FieldPosition = 13 THEN ', ' || TRIM(c.FieldName)
ELSE '' END) ||
MAX(CASE WHEN i.FieldPosition = 14 THEN ', ' || TRIM(c.FieldName)
ELSE '' END) ||
MAX(CASE WHEN i.FieldPosition = 15 THEN ', ' || TRIM(c.FieldName)
ELSE '' END) ||
MAX(CASE WHEN i.FieldPosition = 16 THEN ', ' || TRIM(c.FieldName)
ELSE '' END) ||
MAX(CASE WHEN i.FieldPosition > 16 THEN ',...' ELSE '' END) AS
columnname,
max( HASHBUCKET ('00'xb || SUBSTR(i.IndexStatistics, 12, 1)
(BYTE(4)))) AS SampleSize,
CASE WHEN IndexType = 'M' THEN 'M' ELSE 'I' END AS StatsType,
MAX(SUBSTRING(i.IndexStatistics FROM 1 FOR 7)) AS StatsData
FROM
dbc.dbase d
JOIN dbc.tvm t
ON d.databaseid = t.databaseid
JOIN dbc.tvfields c
ON t.tvmid = c.tableid
JOIN dbc.Indexes i
ON c.tableid = i.tableid
AND c.fieldid = i.fieldid
GROUP BY
Database_id,
TVM_ID,
DatabaseName,
TableName,
StatsType,
i.IndexNumber
UNION ALL
SELECT
d.Databaseid as DATABASE_ID,
t.tvmid as TVM_ID,
d.databasename AS DatabaseName,
t.tvmname AS TableName,
c.fieldname AS ColumnName,
HASHBUCKET
('00'xb || SUBSTR(c.fieldStatistics, 12, 1) (BYTE(4))) AS
SampleSize,
'C' AS StatsType,
SUBSTRING(c.fieldstatistics FROM 1 FOR 7) AS StatsData
FROM
dbc.dbase d
JOIN dbc.tvm t
ON d.databaseid = t.databaseid
JOIN dbc.tvfields c
ON t.tvmid = c.tableid
) dt
WHERE
StatsData IS NOT NULL
order by databasename, tablename
View
REPLACE VIEW Stats_Info AS
SELECT
DatabaseName AS Database_Name,
TableName AS Table_Name,
ColumnName AS Column_Name,
ColumnCount AS Column_Count,
StatsType AS Stats_Type,
Collect_Date,
Collect_Time,
CAST(CAST((Collect_Date (FORMAT 'YYYY-MM-DD')) AS CHAR(10)) || ' '
|| CAST(Collect_Time AS CHAR(11)) AS TIMESTAMP) AS Collect_DateTime,
CASE SampleSize WHEN 0 THEN 100 ELSE SampleSize END AS Sample_Size,
(-1**(NumRowsw1 / 32768))
* (2**((NumRowsw1/16 mod 2048) - 1023))
* (1 + ((NumRowsw1 mod 16) * 2**-4) + (NumRowsw2 * 2**-20)
+ (NumRowsw3 * 2**-36) + (NumRowsw4 * 2**-52))
AS Row_Count,
(-1**(NumValuesw1 / 32768))
* (2**((NumValuesw1/16 mod 2048) - 1023))
* (1 + ((NumValuesw1 mod 16) * 2**-4) + (NumValuesw2 * 2**-20)
+ (NumValuesw3 * 2**-36) + (NumValuesw4 * 2**-52))
AS Num_Values,
CASE WHEN NumNullsw1 > 0 THEN
(-1**(NumNullsw1 / 32768))
* (2**((NumNullsw1/16 mod 2048) - 1023))
* (1 + ((NumNullsw1 mod 16) * 2**-4) + (NumNullsw2 * 2**-20)
+ (NumNullsw3 * 2**-36) + (NumNullsw4 * 2**-52))
ELSE 0 END AS Num_Nulls,
(-1**(ModeFreqw1 / 32768))
* (2**((ModeFreqw1/16 mod 2048) - 1023))
* (1 + ((ModeFreqw1 mod 16) * 2**-4) + (ModeFreqw2 * 2**-20)
+ (ModeFreqw3 * 2**-36) + (ModeFreqw4 * 2**-52))
as Max_Freq
FROM
(
SELECT
DatabaseName,
TableName,
ColumnName,
ColumnCount,
Stats,
StatsType,
(
(HASHBUCKET
(SUBSTR(Stats, 2, 1) ||
SUBSTR(Stats, 1, 1) (BYTE(4))
) - 1900
) * 10000
+
(HASHBUCKET
('00'xb || SUBSTR(Stats, 3, 1) (BYTE(4))
)
) * 100
+
(HASHBUCKET
(
'00'xb || SUBSTR(Stats, 4, 1) (BYTE(4))
)
)
) (DATE) AS Collect_Date,
(CAST(
(HASHBUCKET
(CAST('00'xb || SUBSTR(Stats, 5, 1) AS BYTE(4))
) (FORMAT '99:')
) ||
(HASHBUCKET
(CAST('00'xb || SUBSTR(Stats, 6, 1) AS BYTE(4))
) (FORMAT '99:')
) ||
(HASHBUCKET
(CAST('00'xb || SUBSTR(Stats, 7, 1) AS BYTE(4))
) (FORMAT '99.')
) ||
(HASHBUCKET
(CAST('00'xb || SUBSTR(Stats, 8, 1) AS BYTE(4))
) (FORMAT '99')
) AS TIME(2))
) AS Collect_Time,
HASHBUCKET
('00'xb || SUBSTR(Stats, 12, 1) (BYTE(4))) AS SampleSize,
HASHBUCKET(substr(Stats, 12+8, 1)
|| substr(Stats, 12+7, 1) (byte(4))) as NumNullsw1,
HASHBUCKET(substr(Stats, 12+6, 1)
|| substr(Stats, 12+5, 1) (byte(4))) as NumNullsw2,
HASHBUCKET(substr(Stats, 12+4, 1)
|| substr(Stats, 12+3, 1) (byte(4))) as NumNullsw3,
HASHBUCKET(substr(Stats, 12+2, 1)
|| substr(Stats, 12+1, 1) (byte(4))) as NumNullsw4,
HASHBUCKET(substr(Stats, 40+Offset+8, 1)
|| substr(Stats, 40+Offset+7, 1) (byte(4))) as ModeFreqw1,
HASHBUCKET(substr(Stats, 40+Offset+6, 1)
|| substr(Stats, 40+Offset+5, 1) (byte(4))) as ModeFreqw2,
HASHBUCKET(substr(Stats, 40+Offset+4, 1)
|| substr(Stats, 40+Offset+3, 1) (byte(4))) as ModeFreqw3,
HASHBUCKET(substr(Stats, 40+Offset+2, 1)
|| substr(Stats, 40+Offset+1, 1) (byte(4))) as ModeFreqw4,
HASHBUCKET(substr(Stats, 48+Offset+8, 1)
|| substr(Stats, 48+Offset+7, 1) (byte(4))) as NumValuesw1,
HASHBUCKET(substr(Stats, 48+Offset+6, 1)
|| substr(Stats, 48+Offset+5, 1) (byte(4))) as NumValuesw2,
HASHBUCKET(substr(Stats, 48+Offset+4, 1)
|| substr(Stats, 48+Offset+3, 1) (byte(4))) as NumValuesw3,
HASHBUCKET(substr(Stats, 48+Offset+2, 1)
|| substr(Stats, 48+Offset+1, 1) (byte(4))) as NumValuesw4,
HASHBUCKET(substr(Stats, 56+Offset+8, 1)
|| substr(Stats, 56+Offset+7, 1) (byte(4))) as NumRowsw1,
HASHBUCKET(substr(Stats, 56+Offset+6, 1)
|| substr(Stats, 56+Offset+5, 1) (byte(4))) as NumRowsw2,
HASHBUCKET(substr(Stats, 56+Offset+4, 1)
|| substr(Stats, 56+Offset+3, 1) (byte(4))) as NumRowsw3,
HASHBUCKET(substr(Stats, 56+Offset+2, 1)
|| substr(Stats, 56+Offset+1, 1) (byte(4))) as NumRowsw4
FROM
(
SELECT
DatabaseName,
TableName,
MAX(CASE WHEN ColumnPosition = 1 THEN TRIM(ColumnName) ELSE ''
END) ||
MAX(CASE WHEN ColumnPosition = 2 THEN ',' || TRIM(ColumnName)
ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 3 THEN ',' || TRIM(ColumnName)
ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 4 THEN ',' || TRIM(ColumnName)
ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 5 THEN ',' || TRIM(ColumnName)
ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 6 THEN ',' || TRIM(ColumnName)
ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 7 THEN ',' || TRIM(ColumnName)
ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 8 THEN ',' || TRIM(ColumnName)
ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 9 THEN ',' || TRIM(ColumnName)
ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 10 THEN ',' || TRIM(ColumnName)
ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 11 THEN ',' || TRIM(ColumnName)
ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 12 THEN ',' || TRIM(ColumnName)
ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 13 THEN ',' || TRIM(ColumnName)
ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 14 THEN ',' || TRIM(ColumnName)
ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 15 THEN ',' || TRIM(ColumnName)
ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 16 THEN ',' || TRIM(ColumnName)
ELSE '' END) ||
MAX(CASE WHEN ColumnPosition > 16 THEN ',...' ELSE '' END) AS
ColumnName,
COUNT(*) AS ColumnCount,
'I' AS StatsType,
MAX(CASE
WHEN SUBSTR(IndexStatistics, 23, 1) = '00'XB THEN 16
ELSE 0
END) AS Offset,
MAX(SUBSTR(IndexStatistics, 1, 80)) AS Stats
FROM
dbc.indexstats
GROUP BY
DatabaseName,
TableName,
StatsType,
IndexNumber
UNION ALL
SELECT
DatabaseName,
TableName,
MAX(CASE WHEN ColumnPosition = 1 THEN TRIM(ColumnName) ELSE ''
END) ||
MAX(CASE WHEN ColumnPosition = 2 THEN ',' || TRIM(ColumnName)
ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 3 THEN ',' || TRIM(ColumnName)
ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 4 THEN ',' || TRIM(ColumnName)
ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 5 THEN ',' || TRIM(ColumnName)
ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 6 THEN ',' || TRIM(ColumnName)
ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 7 THEN ',' || TRIM(ColumnName)
ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 8 THEN ',' || TRIM(ColumnName)
ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 9 THEN ',' || TRIM(ColumnName)
ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 10 THEN ',' || TRIM(ColumnName)
ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 11 THEN ',' || TRIM(ColumnName)
ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 12 THEN ',' || TRIM(ColumnName)
ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 13 THEN ',' || TRIM(ColumnName)
ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 14 THEN ',' || TRIM(ColumnName)
ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 15 THEN ',' || TRIM(ColumnName)
ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 16 THEN ',' || TRIM(ColumnName)
ELSE '' END) ||
MAX(CASE WHEN ColumnPosition > 16 THEN ',...' ELSE '' END) AS
ColumnName,
COUNT(*) AS ColumnCount,
'M' AS StatsType,
MAX(CASE
WHEN SUBSTR(ColumnsStatistics, 23, 1) = '00'XB THEN 16
ELSE 0
END) AS Offset,
MAX(SUBSTR(ColumnsStatistics, 1, 80)) AS Stats
FROM
dbc.MultiColumnStats
GROUP BY
DatabaseName,
TableName,
StatsType,
StatisticsID
UNION ALL
SELECT
DatabaseName,
TableName,
ColumnName,
1 AS ColumnCount,
'C' AS StatsType,
CASE
WHEN SUBSTR(fieldStatistics, 23, 1) = '00'XB THEN 16
ELSE 0
END AS Offset,
SUBSTR(fieldstatistics, 1, 80) AS Stats
FROM
dbc.columnstats
) dt
WHERE Stats IS NOT NULL
) dt;
Thanks,
Bob Diehl
| |
