 |
 |
Archives of the TeradataForum
Message Posted: Tue, 26 Sep 2006 @ 12:39:43 GMT
| Subj: | | Re: Statistics expert needed |
| |
| From: | | Stubbs, Donald |
Here's a macro I made from some of Dieter's sql. It takes all the stats from one database and generates the collect stats statements for the
to database. I works great for me.
REPLACE MACRO gen_db_collstats
( ToDBName (varchar(30), default ' '),
FromDBName (varchar(30), default ' ')
)
AS (
/* much thanks to Dieter Noth */
select trim(chartext) (title '')
from (
select 'collect statistics on '
|| trim(:ToDBName) || '.' || trim(tablename)
|| case when statstype = 'i' then ' index ('
else ' column ('
end
|| trim(columnname) || ');' (named chartext, char(2000))
from (
/* much thanks to Dieter Noth */
SELECT DatabaseName as databasename,
TableName as tablename,
ColumnName as columnname,
/** Number of columns within MultiColumn or Index stats **/
ColumnCount,
/** stats collected on:
'C' --> Column
'I' --> Index
'M' --> Multiple columns (V2R5)
**/
StatsType as statstype,
/** collect stats date **/
CollectDate as collectdate,
/** collect stats time **/
CollectTime as collecttime,
CollectTimestamp as collecttimestamp,
/** Time needed to collect stats **/
/* CollectDuration,*/
/** Version
1: pre-V2R5
2: V2R5
**/
StatsVersion as statsversion,
/** V2R5: sample size used for collect stats**/
SampleSize as samplesize,
/** Row Count, Estimated when SampleSize < 100 **/
NumRows as numrows,
/** Distinct Values, Estimated when SampleSize < 100 **/
NumValues as numvalues,
/** Number of NULLs, Estimated when SampleSize < 100 **/
NumNulls as numnulls,
/** Frequency of Mode / value, Estimated when SampleSize < 100 **/
ModeFreq as modefreq
FROM
(
SELECT DatabaseName as databasename,
TableName as tablename,
ColumnName as columnname,
ColumnCount as columncount,
Stats as stats,
StatsType as 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 as databasename,
TableName as tablename,
/** Concat up to 16 columnnames into a comma delimited list **/
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 = 17 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 18 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 19 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 20 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 21 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 22 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 23 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 24 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 25 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 26 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 27 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 28 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 29 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 30 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 31 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 32 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 33 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 34 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 35 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 36 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 37 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 38 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 39 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 40 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 41 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 42 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 43 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 44 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 45 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 46 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 47 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 48 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 49 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 50 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 51 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 52 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 53 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 54 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 55 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 56 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 57 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 58 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 59 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 60 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 61 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 62 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 63 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 64 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition > 64 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
where indextype in ('p','q','s','n','k','v','h','o')
and databasename in (:FromDBName)
GROUP BY DatabaseName,
TableName,
StatsType,
IndexNumber
/* include statement below to find indexes with existing statistics */
/*
HAVING Stats IS NOT NULL
*/
UNION ALL
/*Remove for pre-V2R5 --> **/
SELECT DatabaseName as databasename,
TableName as tablename,
/** Concat up to 16 columnnames into a comma delimited list **/
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 = 17 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 18 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 19 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 20 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 21 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 22 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 23 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 24 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 25 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 26 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 27 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 28 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 29 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 30 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 31 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 32 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 33 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 34 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 35 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 36 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 37 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 38 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 39 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 40 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 41 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 42 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 43 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 44 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 45 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 46 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 47 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 48 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 49 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 50 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 51 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 52 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 53 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 54 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 55 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 56 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 57 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 58 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 59 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 60 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 61 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 62 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 63 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition = 64 THEN ',' || '"' || TRIM(ColumnName) || '"' ELSE '' END) ||
MAX(CASE WHEN ColumnPosition > 64 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
where databasename in (:FromDBName)
GROUP BY DatabaseName,
TableName,
StatsType,
StatisticsID
HAVING Stats IS NOT NULL
UNION ALL
/* * <-- Remove for pre-V2R5 **/
SELECT DatabaseName as databasename,
TableName as tablename,
'"' || TRIM(ColumnName) || '"' as 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 databasename in (:FromDBName)
and Stats IS NOT NULL
) dt
) dt
)pds1
)pds2
ORDER BY 1
;
);
Don Stubbs
| |
