 |
 |
Archives of the TeradataForum
Message Posted: Mon, 25 Feb 2008 @ 21:36:07 GMT
| Subj: | | Re: Stale Stats query? |
| |
| From: | | Hanson, Robert |
Here are two view that I have created to facilitate working with statistics collection. They are based on Dieters SQL and have been working
great for us.
One is for MPRAS/Windows 32 bit systems and the other is for Linux 64 bit systems:
Change the databasename to meet your needs and enjoy.
32 Bit View:
REPLACE VIEW "admintools"."collectedstats_view" (
DatabaseName
,TableName
,ColumnName
,ColumnCount
,StatsType
,CollectDate
,CollectTime
,CollectTimestamp
,StatsVersion
,SampleSize
,NumOfRows
,NumOfValues
,NumOfNulls
,ModeFrequency
)
AS
SELECT
DatabaseName,
TableName,
ColumnName,
ColumnCount,
StatsType,
CollectDate,
CollectTime,
CollectTimestamp,
StatsVersion,
SampleSize,
NumRows,
NumValues,
NumNulls,
ModeFreq
FROM
(
SELECT
0 AS PadBytes,
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 CollectDate,
(
(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')
) (TIME(2))
) AS CollectTime,
CAST((CollectDate (FORMAT 'yyyy-mm-ddB'))
|| CAST(CollectTime AS CHAR(11)) AS TIMESTAMP(2)) AS CollectTimestamp,
HASHBUCKET (SUBSTR(STATS, 9+1, 1)
|| SUBSTR(STATS, 9+0, 1) (BYTE(4)))
AS StatsVersion,
CASE
WHEN HASHBUCKET ('00'xb || SUBSTR(STATS, 11, 1) (BYTE(4))) = 1
THEN HASHBUCKET ('00'xb || SUBSTR(STATS, 12, 1) (BYTE(4)))
ELSE 100
END AS SampleSize,
HASHBUCKET(SUBSTR(STATS, 13+PadBytes+7, 1)
|| SUBSTR(STATS, 13+PadBytes+6, 1) (BYTE(4))) AS NumNullsw1,
HASHBUCKET(SUBSTR(STATS, 13+PadBytes+5, 1)
|| SUBSTR(STATS, 13+PadBytes+4, 1) (BYTE(4))) AS NumNullsw2,
HASHBUCKET(SUBSTR(STATS, 13+PadBytes+3, 1)
|| SUBSTR(STATS, 13+PadBytes+2, 1) (BYTE(4))) AS NumNullsw3,
HASHBUCKET(SUBSTR(STATS, 13+PadBytes+1, 1)
|| SUBSTR(STATS, 13+PadBytes+0, 1) (BYTE(4))) AS NumNullsw4,
(-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,
CASE
WHEN SUBSTR(STATS, 23+PadBytes, 1) = '00'XB THEN 16
ELSE 0
END + 2*PadBytes AS Offset,
HASHBUCKET(SUBSTR(STATS, 41+Offset+7, 1)
|| SUBSTR(STATS, 41+Offset+6, 1) (BYTE(4))) AS ModeFreqw1,
HASHBUCKET(SUBSTR(STATS, 41+Offset+5, 1)
|| SUBSTR(STATS, 41+Offset+4, 1) (BYTE(4))) AS ModeFreqw2,
HASHBUCKET(SUBSTR(STATS, 41+Offset+3, 1)
|| SUBSTR(STATS, 41+Offset+2, 1) (BYTE(4))) AS ModeFreqw3,
HASHBUCKET(SUBSTR(STATS, 41+Offset+1, 1)
|| SUBSTR(STATS, 41+Offset+0, 1) (BYTE(4))) AS ModeFreqw4,
(-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,
HASHBUCKET(SUBSTR(STATS, 49+Offset+7, 1)
|| SUBSTR(STATS, 49+Offset+6, 1) (BYTE(4))) AS NumValuesw1,
HASHBUCKET(SUBSTR(STATS, 49+Offset+5, 1)
|| SUBSTR(STATS, 49+Offset+4, 1) (BYTE(4))) AS NumValuesw2,
HASHBUCKET(SUBSTR(STATS, 49+Offset+3, 1)
|| SUBSTR(STATS, 49+Offset+2, 1) (BYTE(4))) AS NumValuesw3,
HASHBUCKET(SUBSTR(STATS, 49+Offset+1, 1)
|| SUBSTR(STATS, 49+Offset+0, 1) (BYTE(4))) AS NumValuesw4,
(-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,
HASHBUCKET(SUBSTR(STATS, 57+Offset+7, 1)
|| SUBSTR(STATS, 57+Offset+6, 1) (BYTE(4))) AS NumRowsw1,
HASHBUCKET(SUBSTR(STATS, 57+Offset+5, 1)
|| SUBSTR(STATS, 57+Offset+4, 1) (BYTE(4))) AS NumRowsw2,
HASHBUCKET(SUBSTR(STATS, 57+Offset+3, 1)
|| SUBSTR(STATS, 57+Offset+2, 1) (BYTE(4))) AS NumRowsw3,
HASHBUCKET(SUBSTR(STATS, 57+Offset+1, 1)
|| SUBSTR(STATS, 57+Offset+0, 1) (BYTE(4))) AS NumRowsw4,
(-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
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(SUBSTR(IndexStatistics, 1, 88)) AS STATS
FROM
dbc.IndexStats
GROUP BY
DatabaseName,
TableName,
StatsType,
IndexNumber
HAVING STATS IS NOT NULL
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,
MAX(CASE WHEN StatisticsId = 129 AND ColumnName = 'PARTITION'
THEN 'D' ELSE 'M' END) AS StatsType,
MAX(SUBSTR(ColumnsStatistics, 1, 88)) AS STATS
FROM
dbc.MultiColumnStats
GROUP BY
DatabaseName,
TableName,
StatisticsID
HAVING STATS IS NOT NULL
UNION ALL
SELECT
DatabaseName,
TableName,
ColumnName,
1 AS ColumnCount,
'C' AS StatsType,
SUBSTR(fieldstatistics, 1, 120) AS STATS
FROM
dbc.ColumnStats
WHERE STATS IS NOT NULL
) dt
) dt;
64 Bit View:
REPLACE VIEW "admintools"."collectedstats_view" (
DatabaseName
,TableName
,ColumnName
,ColumnCount
,StatsType
,CollectDate
,CollectTime
,CollectTimestamp
,StatsVersion
,SampleSize
,NumOfRows
,NumOfValues
,NumOfNulls
,ModeFrequency
)
AS
SELECT
DatabaseName,
TableName,
ColumnName,
ColumnCount,
StatsType,
CollectDate,
CollectTime,
CollectTimestamp,
StatsVersion,
SampleSize,
NumRows,
NumValues,
NumNulls,
ModeFreq
FROM
(
SELECT
4 AS PadBytes,
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 CollectDate,
(
(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')
) (TIME(2))
) AS CollectTime,
CAST((CollectDate (FORMAT 'yyyy-mm-ddB'))
|| CAST(CollectTime AS CHAR(11)) AS TIMESTAMP(2)) AS CollectTimestamp,
HASHBUCKET (SUBSTR(STATS, 9+1, 1)
|| SUBSTR(STATS, 9+0, 1) (BYTE(4)))
AS StatsVersion,
CASE
WHEN HASHBUCKET ('00'xb || SUBSTR(STATS, 11, 1) (BYTE(4))) = 1
THEN HASHBUCKET ('00'xb || SUBSTR(STATS, 12, 1) (BYTE(4)))
ELSE 100
END AS SampleSize,
HASHBUCKET(SUBSTR(STATS, 13+PadBytes+7, 1)
|| SUBSTR(STATS, 13+PadBytes+6, 1) (BYTE(4))) AS NumNullsw1,
HASHBUCKET(SUBSTR(STATS, 13+PadBytes+5, 1)
|| SUBSTR(STATS, 13+PadBytes+4, 1) (BYTE(4))) AS NumNullsw2,
HASHBUCKET(SUBSTR(STATS, 13+PadBytes+3, 1)
|| SUBSTR(STATS, 13+PadBytes+2, 1) (BYTE(4))) AS NumNullsw3,
HASHBUCKET(SUBSTR(STATS, 13+PadBytes+1, 1)
|| SUBSTR(STATS, 13+PadBytes+0, 1) (BYTE(4))) AS NumNullsw4,
(-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,
CASE
WHEN SUBSTR(STATS, 23+PadBytes, 1) = '00'XB THEN 16
ELSE 0
END + 2*PadBytes AS Offset,
HASHBUCKET(SUBSTR(STATS, 41+Offset+7, 1)
|| SUBSTR(STATS, 41+Offset+6, 1) (BYTE(4))) AS ModeFreqw1,
HASHBUCKET(SUBSTR(STATS, 41+Offset+5, 1)
|| SUBSTR(STATS, 41+Offset+4, 1) (BYTE(4))) AS ModeFreqw2,
HASHBUCKET(SUBSTR(STATS, 41+Offset+3, 1)
|| SUBSTR(STATS, 41+Offset+2, 1) (BYTE(4))) AS ModeFreqw3,
HASHBUCKET(SUBSTR(STATS, 41+Offset+1, 1)
|| SUBSTR(STATS, 41+Offset+0, 1) (BYTE(4))) AS ModeFreqw4,
(-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,
HASHBUCKET(SUBSTR(STATS, 49+Offset+7, 1)
|| SUBSTR(STATS, 49+Offset+6, 1) (BYTE(4))) AS NumValuesw1,
HASHBUCKET(SUBSTR(STATS, 49+Offset+5, 1)
|| SUBSTR(STATS, 49+Offset+4, 1) (BYTE(4))) AS NumValuesw2,
HASHBUCKET(SUBSTR(STATS, 49+Offset+3, 1)
|| SUBSTR(STATS, 49+Offset+2, 1) (BYTE(4))) AS NumValuesw3,
HASHBUCKET(SUBSTR(STATS, 49+Offset+1, 1)
|| SUBSTR(STATS, 49+Offset+0, 1) (BYTE(4))) AS NumValuesw4,
(-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,
HASHBUCKET(SUBSTR(STATS, 57+Offset+7, 1)
|| SUBSTR(STATS, 57+Offset+6, 1) (BYTE(4))) AS NumRowsw1,
HASHBUCKET(SUBSTR(STATS, 57+Offset+5, 1)
|| SUBSTR(STATS, 57+Offset+4, 1) (BYTE(4))) AS NumRowsw2,
HASHBUCKET(SUBSTR(STATS, 57+Offset+3, 1)
|| SUBSTR(STATS, 57+Offset+2, 1) (BYTE(4))) AS NumRowsw3,
HASHBUCKET(SUBSTR(STATS, 57+Offset+1, 1)
|| SUBSTR(STATS, 57+Offset+0, 1) (BYTE(4))) AS NumRowsw4,
(-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
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(SUBSTR(IndexStatistics, 1, 88)) AS STATS
FROM
dbc.IndexStats
GROUP BY
DatabaseName,
TableName,
StatsType,
IndexNumber
HAVING STATS IS NOT NULL
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,
MAX(CASE WHEN StatisticsId = 129 AND ColumnName = 'PARTITION'
THEN 'D' ELSE 'M' END) AS StatsType,
MAX(SUBSTR(ColumnsStatistics, 1, 88)) AS STATS
FROM
dbc.MultiColumnStats
GROUP BY
DatabaseName,
TableName,
StatisticsID
HAVING STATS IS NOT NULL
UNION ALL
SELECT
DatabaseName,
TableName,
ColumnName,
1 AS ColumnCount,
'C' AS StatsType,
SUBSTR(fieldstatistics, 1, 120) AS STATS
FROM
dbc.ColumnStats
WHERE STATS IS NOT NULL
) dt
) dt;
| |
