Home Page for the TeradataForum
 
https:

Archives of the TeradataForum

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


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


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
;


     
  <Prev Next>   <<First <Prev Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 28 Jun 2020