 |
 |
Archives of the TeradataForum
Message Posted: Fri, 18 Jun 2004 @ 15:11:31 GMT
| Subj: | | Re: Can we check stats automatically |
| |
| From: | | Stubbs, Donald |
Here's a piece of SUPER SQL I got via this site. Written by Dieter Noth (thank you, thank you, thank you Dieter). I have used many variations
of this for various purposes. You might consider keeping a history of statistics in a table in your administrative database.
Really, here's the sql this time!
SELECT
trim(DatabaseName) || '.' || trim(TableName),
trim(ColumnName),
/** stats collected on:
'C' --> Column
'I' --> Index
'M' --> Multiple columns (V2R5)
**/
StatsType,
/** collect stats date **/
CollectDate,
/** collect stats time **/
CollectTime,
/** V2R5: sample size used for collect stats**/
CASE SampleSize WHEN 0 THEN 100 ELSE SampleSize END AS SampleSize,
/** Row Count
Estimated when SampleSize < 100
**/
(-1**(NumRowsw1 / 32768))
* (2**((NumRowsw1 / 16 mod 2048) - 1023))
* (1 + ((NumRowsw1 mod 16) * 2**-4) + (NumRowsw2 * 2**-20)
+ (NumRowsw3 * 2**-36) + (NumRowsw4 * 2**-52)) (decimal(18,0))
AS NumRows,
/** Distinct Values
Estimated when SampleSize < 100
**/
(-1**(NumValuesw1 / 32768))
* (2**((NumValuesw1 / 16 mod 2048) - 1023))
* (1 + ((NumValuesw1 mod 16) * 2**-4) + (NumValuesw2 * 2**-20)
+ (NumValuesw3 * 2**-36) + (NumValuesw4 * 2**-52)) (decimal(18,0))
AS NumValues,
/** Number of NULLs
Estimated when SampleSize < 100
**/
(-1**(NumNullsw1 / 32768))
* (2**((NumNullsw1 / 16 mod 2048) - 1023))
* (1 + ((NumNullsw1 mod 16) * 2**-4) + (NumNullsw2 * 2**-20)
+ (NumNullsw3 * 2**-36) + (NumNullsw4 * 2**-52)) (decimal(18,0))
AS NumNulls,
/** Maximum number of rows / value,
Estimated when SampleSize < 100
**/
(-1**(ModeFreqw1 / 32768))
* (2**((ModeFreqw1 / 16 mod 2048) - 1023))
* (1 + ((ModeFreqw1 mod 16) * 2**-4) + (ModeFreqw2 * 2**-20)
+ (ModeFreqw3 * 2**-36) + (ModeFreqw4 * 2**-52)) (decimal(18,0))
AS ModeFreq
FROM
(
SELECT
DatabaseName,
TableName,
ColumnName,
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 CollectDate,
(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 CollectTime,
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
d.databasename AS DatabaseName,
t.tvmname AS TableName,
/** Concat up to 16 columnnames into a comma delimited list **/
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,
CASE WHEN IndexType = 'M' THEN 'M' ELSE 'I' END AS StatsType,
/** Floats are stored after the data and data may be 16 or 32 BYTEs **/
/** depending on BYTE[23]**/
MAX(CASE
WHEN SUBSTR(i.indexStatistics, 23, 1) = '00'XB THEN 16
ELSE 0
END)
AS Offset,
MAX(SUBSTR(i.indexstatistics, 1, 80)) AS Stats
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
DatabaseName,
TableName,
StatsType,
i.IndexNumber
UNION ALL
SELECT
d.databasename AS DatabaseName,
t.tvmname AS TableName,
c.fieldname AS ColumnName,
'C' AS StatsType,
/** Floats are stored after the data and data may be 16 or 32 BYTEs **/
/** depending on BYTE[23]**/
CASE
WHEN SUBSTR(c.fieldStatistics, 23, 1) = '00'XB THEN 16
ELSE 0
END AS Offset,
SUBSTR(c.fieldstatistics, 1, 80) AS Stats
FROM
dbc.dbase d
JOIN dbc.tvm t
ON d.databaseid = t.databaseid
JOIN dbc.tvfields c
ON t.tvmid = c.tableid
) dt
) dt
WHERE Stats IS NOT NULL
ORDER BY
1,2 ;
| |
