Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Mon, 29 Jan 2007 @ 19:51:09 GMT


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


Subj:   Re: Multi-column index COLLECT STATISTICS script
 
From:   Hanson, Robert

Try this one instead.

     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
          ;

           );


     
  <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: 15 Jun 2023