Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Fri, 18 Jun 2004 @ 15:11:31 GMT


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


Subj:   Re: Can we check stats automatically
 
From:   Stubbs, Donald

Here's a piece of SUPER SQL I got via this site. Written by Dieter Noth (thank you, thank you, thank you Dieter). I have used many variations of this for various purposes. You might consider keeping a history of statistics in a table in your administrative database.

Really, here's the sql this time!

     SELECT
        trim(DatabaseName) || '.' || trim(TableName),
        trim(ColumnName),

         /** stats collected on:
            'C' --> Column
            'I' --> Index
            'M' --> Multiple columns (V2R5)
         **/
        StatsType,

         /** collect stats date **/
        CollectDate,

         /** collect stats time **/
        CollectTime,

         /** V2R5: sample size used for collect stats**/
        CASE SampleSize WHEN 0 THEN 100 ELSE SampleSize END AS SampleSize,


         /** Row Count
            Estimated when SampleSize < 100
        **/
        (-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,

         /** Distinct Values
            Estimated when SampleSize < 100
        **/
        (-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,

         /** Number of NULLs
            Estimated when SampleSize < 100
        **/
        (-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,

         /** Maximum number of rows / value,
            Estimated when SampleSize < 100
        **/
        (-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
     FROM
       (
        SELECT
          DatabaseName,
          TableName,
          ColumnName,
          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,

          (CAST(
            (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')
            ) AS TIME(2))
          ) AS CollectTime,

          HASHBUCKET
           ('00'xb || SUBSTR(Stats, 12, 1) (BYTE(4))) AS SampleSize,

          HASHBUCKET(SUBSTR(Stats, 12 + 8, 1)
                  || SUBSTR(Stats, 12 + 7, 1) (BYTE(4))) AS NumNullsw1,
          HASHBUCKET(SUBSTR(Stats, 12 + 6, 1)
                  || SUBSTR(Stats, 12 + 5, 1) (BYTE(4))) AS NumNullsw2,
          HASHBUCKET(SUBSTR(Stats, 12 + 4, 1)
                  || SUBSTR(Stats, 12 + 3, 1) (BYTE(4))) AS NumNullsw3,
          HASHBUCKET(SUBSTR(Stats, 12 + 2, 1)
                  || SUBSTR(Stats, 12 + 1, 1) (BYTE(4))) AS NumNullsw4,

          HASHBUCKET(SUBSTR(Stats, 40 + Offset + 8, 1)
                  || SUBSTR(Stats, 40 + Offset + 7, 1) (BYTE(4))) AS ModeFreqw1,
          HASHBUCKET(SUBSTR(Stats, 40 + Offset + 6, 1)
                  || SUBSTR(Stats, 40 + Offset + 5, 1) (BYTE(4))) AS ModeFreqw2,
          HASHBUCKET(SUBSTR(Stats, 40 + Offset + 4, 1)
                  || SUBSTR(Stats, 40 + Offset + 3, 1) (BYTE(4))) AS ModeFreqw3,
          HASHBUCKET(SUBSTR(Stats, 40 + Offset + 2, 1)
                  || SUBSTR(Stats, 40 + Offset + 1, 1) (BYTE(4))) AS ModeFreqw4,

          HASHBUCKET(SUBSTR(Stats, 48 + Offset + 8, 1)
                  || SUBSTR(Stats, 48 + Offset + 7, 1) (BYTE(4))) AS NumValuesw1,
          HASHBUCKET(SUBSTR(Stats, 48 + Offset + 6, 1)
                  || SUBSTR(Stats, 48 + Offset + 5, 1) (BYTE(4))) AS NumValuesw2,
          HASHBUCKET(SUBSTR(Stats, 48 + Offset + 4, 1)
                  || SUBSTR(Stats, 48 + Offset + 3, 1) (BYTE(4))) AS NumValuesw3,
          HASHBUCKET(SUBSTR(Stats, 48 + Offset + 2, 1)
                  || SUBSTR(Stats, 48 + Offset + 1, 1) (BYTE(4))) AS NumValuesw4,

          HASHBUCKET(SUBSTR(Stats, 56 + Offset + 8, 1)
                  || SUBSTR(Stats, 56 + Offset + 7, 1) (BYTE(4))) AS NumRowsw1,
          HASHBUCKET(SUBSTR(Stats, 56 + Offset + 6, 1)
                  || SUBSTR(Stats, 56 + Offset + 5, 1) (BYTE(4))) AS NumRowsw2,
          HASHBUCKET(SUBSTR(Stats, 56 + Offset + 4, 1)
                  || SUBSTR(Stats, 56 + Offset + 3, 1) (BYTE(4))) AS NumRowsw3,
          HASHBUCKET(SUBSTR(Stats, 56 + Offset + 2, 1)
                  || SUBSTR(Stats, 56 + Offset + 1, 1) (BYTE(4))) AS NumRowsw4

        FROM
         (
          SELECT
            d.databasename AS DatabaseName,
            t.tvmname AS TableName,

            /** Concat up to 16 columnnames into a comma delimited list **/
            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,

            CASE WHEN IndexType = 'M' THEN 'M' ELSE 'I' END AS StatsType,

             /** Floats are stored after the data and data may be 16 or 32 BYTEs **/
             /** depending on BYTE[23]**/
            MAX(CASE
                  WHEN SUBSTR(i.indexStatistics, 23, 1) = '00'XB THEN 16
                  ELSE 0
                END)
            AS Offset,

            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

          UNION ALL

          SELECT
            d.databasename AS DatabaseName,
            t.tvmname AS TableName,
            c.fieldname AS ColumnName,
            'C' AS StatsType,

             /** Floats are stored after the data and data may be 16 or 32 BYTEs **/
             /** depending on BYTE[23]**/
            CASE
              WHEN SUBSTR(c.fieldStatistics, 23, 1) = '00'XB THEN 16
              ELSE 0
            END AS Offset,

            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
         ) dt
       ) dt
     WHERE Stats IS NOT NULL
     ORDER BY
        1,2 ;


     
  <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