Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Mon, 25 Feb 2008 @ 21:36:07 GMT


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


Subj:   Re: Stale Stats query?
 
From:   Hanson, Robert

Here are two view that I have created to facilitate working with statistics collection. They are based on Dieters SQL and have been working great for us.

One is for MPRAS/Windows 32 bit systems and the other is for Linux 64 bit systems:

Change the databasename to meet your needs and enjoy.


32 Bit View:

     REPLACE VIEW "admintools"."collectedstats_view" (
        DatabaseName
        ,TableName
        ,ColumnName
        ,ColumnCount
        ,StatsType
        ,CollectDate
        ,CollectTime
        ,CollectTimestamp
        ,StatsVersion
        ,SampleSize
        ,NumOfRows
        ,NumOfValues
        ,NumOfNulls
        ,ModeFrequency
        )
        AS


        SELECT
         DatabaseName,
         TableName,
         ColumnName,
         ColumnCount,
         StatsType,
         CollectDate,
         CollectTime,
         CollectTimestamp,
         StatsVersion,
         SampleSize,
         NumRows,
         NumValues,
         NumNulls,
         ModeFreq

     FROM
        (
         SELECT

           0 AS PadBytes,

           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 (FORMAT 'yyyy-mm-ddB'))
           || CAST(CollectTime AS CHAR(11)) AS TIMESTAMP(2)) AS CollectTimestamp,
           HASHBUCKET (SUBSTR(STATS, 9+1, 1)
                    || SUBSTR(STATS, 9+0, 1) (BYTE(4)))
           AS StatsVersion,


           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+PadBytes+7, 1)
                   || SUBSTR(STATS, 13+PadBytes+6, 1) (BYTE(4))) AS NumNullsw1,
           HASHBUCKET(SUBSTR(STATS, 13+PadBytes+5, 1)
                   || SUBSTR(STATS, 13+PadBytes+4, 1) (BYTE(4))) AS NumNullsw2,
           HASHBUCKET(SUBSTR(STATS, 13+PadBytes+3, 1)
                   || SUBSTR(STATS, 13+PadBytes+2, 1) (BYTE(4))) AS NumNullsw3,
           HASHBUCKET(SUBSTR(STATS, 13+PadBytes+1, 1)
                   || SUBSTR(STATS, 13+PadBytes+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,


           CASE
             WHEN SUBSTR(STATS, 23+PadBytes, 1) = '00'XB THEN 16
             ELSE 0
           END + 2*PadBytes 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,

             MAX(SUBSTR(IndexStatistics, 1, 88)) AS STATS

           FROM
             dbc.IndexStats
           GROUP BY
             DatabaseName,
             TableName,
             StatsType,
             IndexNumber
           HAVING STATS IS NOT NULL


           UNION ALL

           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,

             MAX(CASE WHEN StatisticsId = 129 AND ColumnName = 'PARTITION'
                      THEN 'D' ELSE 'M' END) AS StatsType,

             MAX(SUBSTR(ColumnsStatistics, 1, 88)) AS STATS

           FROM
             dbc.MultiColumnStats
           GROUP BY
             DatabaseName,
             TableName,
             StatisticsID
           HAVING STATS IS NOT NULL


           UNION ALL

           SELECT
             DatabaseName,
             TableName,
             ColumnName,
             1 AS ColumnCount,
             'C' AS StatsType,

             SUBSTR(fieldstatistics, 1, 120) AS STATS

           FROM
             dbc.ColumnStats
           WHERE STATS IS NOT NULL
          ) dt
        ) dt;

64 Bit View:

     REPLACE VIEW "admintools"."collectedstats_view" (
        DatabaseName
        ,TableName
        ,ColumnName
        ,ColumnCount
        ,StatsType
        ,CollectDate
        ,CollectTime
        ,CollectTimestamp
        ,StatsVersion
        ,SampleSize
        ,NumOfRows
        ,NumOfValues
        ,NumOfNulls
        ,ModeFrequency
        )
        AS


        SELECT
         DatabaseName,
         TableName,
         ColumnName,
         ColumnCount,
         StatsType,
         CollectDate,
         CollectTime,
         CollectTimestamp,
         StatsVersion,
         SampleSize,
         NumRows,
         NumValues,
         NumNulls,
         ModeFreq

     FROM
        (
         SELECT

           4 AS PadBytes,

           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 (FORMAT 'yyyy-mm-ddB'))
           || CAST(CollectTime AS CHAR(11)) AS TIMESTAMP(2)) AS CollectTimestamp,
           HASHBUCKET (SUBSTR(STATS, 9+1, 1)
                    || SUBSTR(STATS, 9+0, 1) (BYTE(4)))
           AS StatsVersion,


           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+PadBytes+7, 1)
                   || SUBSTR(STATS, 13+PadBytes+6, 1) (BYTE(4))) AS NumNullsw1,
           HASHBUCKET(SUBSTR(STATS, 13+PadBytes+5, 1)
                   || SUBSTR(STATS, 13+PadBytes+4, 1) (BYTE(4))) AS NumNullsw2,
           HASHBUCKET(SUBSTR(STATS, 13+PadBytes+3, 1)
                   || SUBSTR(STATS, 13+PadBytes+2, 1) (BYTE(4))) AS NumNullsw3,
           HASHBUCKET(SUBSTR(STATS, 13+PadBytes+1, 1)
                   || SUBSTR(STATS, 13+PadBytes+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,


           CASE
             WHEN SUBSTR(STATS, 23+PadBytes, 1) = '00'XB THEN 16
             ELSE 0
           END + 2*PadBytes 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,

             MAX(SUBSTR(IndexStatistics, 1, 88)) AS STATS

           FROM
             dbc.IndexStats
           GROUP BY
             DatabaseName,
             TableName,
             StatsType,
             IndexNumber
           HAVING STATS IS NOT NULL


           UNION ALL

           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,

             MAX(CASE WHEN StatisticsId = 129 AND ColumnName = 'PARTITION'
                      THEN 'D' ELSE 'M' END) AS StatsType,

             MAX(SUBSTR(ColumnsStatistics, 1, 88)) AS STATS

           FROM
             dbc.MultiColumnStats
           GROUP BY
             DatabaseName,
             TableName,
             StatisticsID
           HAVING STATS IS NOT NULL


           UNION ALL

           SELECT
             DatabaseName,
             TableName,
             ColumnName,
             1 AS ColumnCount,
             'C' AS StatsType,

             SUBSTR(fieldstatistics, 1, 120) AS STATS

           FROM
             dbc.ColumnStats
           WHERE STATS IS NOT NULL
          ) dt
        ) dt;


     
  <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: 27 Dec 2016