![](//teradataforum.com/imgs/ptdfw1.gif) |
![](//teradataforum.com/imgs/ptdfw2.gif) |
Archives of the TeradataForum
Message Posted: Fri, 28 Jan 2005 @ 08:13:01 GMT
Subj: | | Re: Record count stored in Data Dictionary |
|
From: | | Dieter Noeth |
Sudha Muthusamy wrote:
| I would like to know if the record count for a table is stored in Teradata DD (Data Dictionary) | |
As others told you it's not stored anywhere within system tables. But you can get it from statistics via "help stats ..." for a single
table or using a weird query for all stats.
As stats may be outdated another way for any table (even withou stats) is "help index ..." which returns an "Appoximate Count" derived by a
Random AMP Sample. It's only approximate, but if the table is large and not skewed it's quite good.
Dieter
It's time to post the stats queries again ;-)
/** 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
**/
/** 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)
**/
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 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(i.LastAlterTimestamp) AS LastAlterTimestamp,
COUNT(*) AS ColumnCount,
CASE WHEN IndexType = 'M' THEN 'M' ELSE 'I' END AS StatsType,
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
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)
**/
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,
'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
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
;
| |