 |
 |
Archives of the TeradataForum
Message Posted: Fri, 18 Jun 2004 @ 20:27:07 GMT
| Subj: | | Re: Can we check stats automatically |
| |
| From: | | Dieter Noeth |
Stubbs, Donald wrote:
| | Really, here's the sql this time! | |
And here's a version using dbc views, if access rights to dbc tables are missing...
Btw, the SampleSize column sometimes shows strange values when run on pre-V2R5 stats...
Dieter
SELECT
DatabaseName,
TableName,
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)) --sign
* (2**((NumRowsw1/16 mod 2048) - 1023)) --exponent
* (1 + ((NumRowsw1 mod 16) * 2**-4) + (NumRowsw2 * 2**-20)
+ (NumRowsw3 * 2**-36) + (NumRowsw4 * 2**-52)) --fraction
as NumRows,
/** Distinct Values
Estimated when SampleSize < 100
**/
(-1**(NumValuesw1 / 32768)) --sign
* (2**((NumValuesw1/16 mod 2048) - 1023)) --exponent
* (1 + ((NumValuesw1 mod 16) * 2**-4) + (NumValuesw2 * 2**-20)
+ (NumValuesw3 * 2**-36) + (NumValuesw4 * 2**-52)) --fraction
as NumValues,
/** Number of NULLs
Estimated when SampleSize < 100
**/
(-1**(NumNullsw1 / 32768)) --sign
* (2**((NumNullsw1/16 mod 2048) - 1023)) --exponent
* (1 + ((NumNullsw1 mod 16) * 2**-4) + (NumNullsw2 * 2**-20)
+ (NumNullsw3 * 2**-36) + (NumNullsw4 * 2**-52)) --fraction
as NumNulls,
/** Maximum number of rows / value,
Estimated when SampleSize < 100
**/
(-1**(ModeFreqw1 / 32768)) --sign
* (2**((ModeFreqw1/16 mod 2048) - 1023)) --exponent
* (1 + ((ModeFreqw1 mod 16) * 2**-4) + (ModeFreqw2 * 2**-20)
+ (ModeFreqw3 * 2**-36) + (ModeFreqw4 * 2**-52)) --fraction
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
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,
'I' AS StatsType,
/** Floats are stored after the data and data may be 16 or 32 bytes **/
/** depending on byte[23]**/
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,
'M' AS StatsType,
/** Floats are stored after the data and data may be 16 or 32 bytes **/
/** depending on byte[23]**/
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,
'C' AS StatsType,
/** Floats are stored after the data and data may be 16 or 32 bytes **/
/** depending on byte[23]**/
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
ORDER BY
DatabaseName,
TableName,
ColumnName
;
| |
