 |
 |
Archives of the TeradataForum
Message Posted: Wed, 25 Jul 2007 @ 17:53:15 GMT
| Subj: | | Re: View statistic values |
| |
| From: | | Dieter Noeth |
Daniel Frei wrote:
| | This works fine on our old hardware, but now we have migrated to V2R6.2 on a Linux 64bit. I always get the error message 2616: Numeric
overflow occurred during computation. | |
I don't have access to a 64-bit system, yet.
But did you already try to remove the (decimal(18,0)) cast or replace it with decimal(38,0)?
And you should use the modified version, which includes the PARTITION column in V2R6.1, see below...
Dieter
/** Additional info on collected statistics, including Date, Time, Rowcount...
2003-03-12 dn initial version
2003-07-22 dn modified to use dbc.???Stats views instead of base tables
2004-01-20 dn added ColumnCount
2004-11-08 dn added StatsVersion
fixed SampleSize for pre-V2R5 stats, now displays 100%
2004-11-15 dn added version based on base tables to display CollectDuration,
modified/reformatted source code
2007-05-22 dn modified base table version to include the pseudo-column PARTITION (V2R6.1+)
**/
/** Base table version, select rights needed on:
dbc.dbase, dbc.tvm, dbc.tvfields, dbc.Indexes
**/
SELECT
DatabaseName,
TableName,
ColumnName,
/** Number of columns within MultiColumn or Index stats **/
ColumnCount,
/** stats collected on:
'C' --> Column
'I' --> Index
'M' --> Multiple columns (V2R5)
'D' --> Pseudo column PARTITION (V2R6.1)
**/
StatsType,
/** collect stats date **/
CollectDate,
/** collect stats time **/
CollectTime,
CollectTimestamp,
/** Time needed to collect stats
I don't know if it's really correct, because CollectDuration is
sometimes negative for sample stats, That's why i use ABS ;-)
**/
ABS(CollectDuration) AS CollectDuration,
/** Version
1: pre-V2R5
2: V2R5
**/
StatsVersion,
/** V2R5: sample size used for collect stats**/
SampleSize,
/** Row Count, Estimated when SampleSize < 100 **/
NumRows,
/** Distinct Values, Estimated when SampleSize < 100 **/
NumValues,
/** Number of NULLs, Estimated when SampleSize < 100 **/
NumNulls,
/** Maximum number of rows / value, Estimated when SampleSize < 100
**/
ModeFreq
FROM
(
SELECT
DatabaseName,
TableName,
ColumnName,
ColumnCount,
STATS,
StatsType,
LastAlterTimestamp,
(
(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,
CollectTimestamp-
(
COALESCE(
MAX(CollectTimestamp) OVER (
PARTITION BY DatabaseName, TableName, LastAlterTimestamp
ORDER BY CollectTimestamp
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)
,LastAlterTimestamp)
) HOUR(4) TO SECOND(2)
AS CollectDuration,
HASHBUCKET (SUBSTR(STATS, 9+1, 1)
|| SUBSTR(STATS, 9+0, 1) (BYTE(4)))
AS StatsVersion,
/** V2R5: sample size used for collect stats**/
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+7, 1)
|| SUBSTR(STATS, 13+6, 1) (BYTE(4))) AS NumNullsw1,
HASHBUCKET(SUBSTR(STATS, 13+5, 1)
|| SUBSTR(STATS, 13+4, 1) (BYTE(4))) AS NumNullsw2,
HASHBUCKET(SUBSTR(STATS, 13+3, 1)
|| SUBSTR(STATS, 13+2, 1) (BYTE(4))) AS NumNullsw3,
HASHBUCKET(SUBSTR(STATS, 13+1, 1)
|| SUBSTR(STATS, 13+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,
/** Numbers are stored AS Floats after the data and data may be 16 or 32 BYTEs **/
/** depending on BYTE[23]**/
CASE
WHEN SUBSTR(STATS, 23, 1) = '00'XB THEN 16
ELSE 0
END 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
d.databasename AS DatabaseName,
t.tvmname AS TableName,
MAX(CASE WHEN i.FieldPosition = 1 THEN (CASE WHEN IndexType = 'D'
THEN 'PARTITION' ELSE TRIM(c.FieldName) END) 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(i.LastAlterTimestamp) AS LastAlterTimestamp,
COUNT(*) AS ColumnCount,
CASE IndexType
WHEN 'M' THEN 'M'
WHEN 'D' THEN 'D'
ELSE 'I'
END AS StatsType,
MAX(SUBSTR(i.IndexStatistics, 1, 80)) AS STATS
FROM dbc.Indexes i
JOIN dbc.tvm t
ON t.TVMid = i.tableid
JOIN dbc.dbase d
ON t.databaseid = d.databaseid
LEFT JOIN dbc.tvfields c
ON c.tableid = i.tableid
AND c.fieldid = i.fieldid
GROUP BY
DatabaseName,
TableName,
StatsType,
i.IndexNumber
HAVING STATS IS NOT NULL
UNION ALL
SELECT
d.databasename AS DatabaseName,
t.tvmname AS TableName,
c.fieldname AS ColumnName,
c.LastAlterTimestamp,
1 AS ColumnCount,
'C' AS StatsType,
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
WHERE STATS IS NOT NULL
) dt
) dt
ORDER BY
DatabaseName,
TableName,
ColumnName
;
/** View version, select rights needed on:
dbc.ColumnStats, dbc.IndexStats, dbc.MultiColumnStats
**/
SELECT
DatabaseName,
TableName,
ColumnName,
/** Number of columns within MultiColumn or Index stats **/
ColumnCount,
/** stats collected on:
'C' --> Column
'I' --> Index
'M' --> Multiple columns (V2R5)
'D' --> Pseudo column PARTITION (V2R6.1)
**/
StatsType,
/** collect stats date **/
CollectDate,
/** collect stats time **/
CollectTime,
CollectTimestamp,
/** Time needed to collect stats **/
/* CollectDuration,*/
/** Version
1: pre-V2R5
2: V2R5
**/
StatsVersion,
/** V2R5: sample size used for collect stats**/
SampleSize,
/** Row Count, Estimated when SampleSize < 100 **/
NumRows,
/** Distinct Values, Estimated when SampleSize < 100 **/
NumValues,
/** Number of NULLs, Estimated when SampleSize < 100 **/
NumNulls,
/** Maximum number of rows / value, Estimated when SampleSize < 100
**/
ModeFreq
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 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 AS TIMESTAMP(2))
+ ((CollectTime - TIME '00:00:00') HOUR TO SECOND)
AS CollectTimestamp,
HASHBUCKET (SUBSTR(STATS, 9+1, 1)
|| SUBSTR(STATS, 9+0, 1) (BYTE(4)))
AS StatsVersion,
/** V2R5: sample size used for collect stats**/
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+7, 1)
|| SUBSTR(STATS, 13+6, 1) (BYTE(4))) AS NumNullsw1,
HASHBUCKET(SUBSTR(STATS, 13+5, 1)
|| SUBSTR(STATS, 13+4, 1) (BYTE(4))) AS NumNullsw2,
HASHBUCKET(SUBSTR(STATS, 13+3, 1)
|| SUBSTR(STATS, 13+2, 1) (BYTE(4))) AS NumNullsw3,
HASHBUCKET(SUBSTR(STATS, 13+1, 1)
|| SUBSTR(STATS, 13+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,
/** Numbers are stored AS Floats after the data and data may be 16 or 32 BYTEs **/
/** depending on BYTE[23]**/
CASE
WHEN SUBSTR(STATS, 23, 1) = '00'XB THEN 16
ELSE 0
END 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,
/** 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
HAVING STATS IS NOT NULL
UNION ALL
/** Remove for pre-V2R5 --> **/
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,
CASE WHEN StatisticsID = 129 THEN 'D' ELSE 'M' 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(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
HAVING STATS IS NOT NULL
UNION ALL
/** <-- Remove for pre-V2R5 **/
SELECT
DatabaseName,
TableName,
ColumnName,
1 AS ColumnCount,
'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
WHERE STATS IS NOT NULL
) dt
) dt
ORDER BY
DatabaseName,
TableName,
ColumnName
;
| |
