Archives of the TeradataForum

Message Posted: Fri, 28 Jan 2005 @ 08:13:01 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Sudha Muthusamy wrote:

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
;

	<Prev	Next>		<<First	<Prev	Next>	Last>>