Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Tue, 07 Apr 2009 @ 16:42:07 GMT


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


Subj:   Re: Dieter's collect stats query on v12
 
From:   Kevin.Leach

Below is a modified query which accounts for TDv12(also works on TDv13) as well as the bitness(32bit or 64bit) of the OS. This query does have a prerequisite of a simple function compiled to gives us the bitness. Maybe there's a more graceful way to do this?

Here's a simple test table create to test your environment.

     drop table vers_stats;
     create multiset table vers_stats (col1 char(5));
     insert into vers_stats values ('hello');
     insert into vers_stats values ('hello');
     insert into vers_stats values (NULL);
     insert into vers_stats values ('there');
     The below sql should return this row, and of course should avoid the
     numeric overflow.
     TableName                       ColumnName       SampleSize
     NumRows       NumValues     NumNulls        ModeFreq
     ------------------------------  ------------------------------
     --------------------  --------------------  --------------------
     -------------------- -----------------
     VERS_STATS                      col1
     100               4.                    3.
     1.                  2.
     Kevin
     -- http://www.teradataforum.com/teradata/20080528_141149.htm
     -- requires precompiled function 'floor'
     -- returns table name, column name and stats for all tables in logged
     in database
     -- original code modified to work on 64-bit and/or TDv12+ dbms
     -- tested on v2r6 Windows 32-bit, MP-RAS 32-bit, Windows 64-bit
     --           v12  Windows 32-bit, Linux 64-bit
     --           v13  Linux 64-bit
     SELECT
     trim(TableName) TableName,
     trim(ColumnName) ColumnName,
     SampleSize,
     NumRows,
     NumValues,
     NumNulls,
     ModeFreq
      FROM
      ( SELECT
          TableName,
          ColumnName,
          CASE
            WHEN HASHBUCKET ('00'xb || SUBSTR(Statss, 11, 1) (BYTE(4))) = 1

            THEN HASHBUCKET ('00'xb || SUBSTR(Statss, 12, 1) (BYTE(4)))
            ELSE 100
          END AS SampleSize,
          HASHBUCKET(SUBSTR(Statss, 13+offset2+7, 1)
            || SUBSTR(Statss, 13+offset2+6, 1) (BYTE(4)))/d AS NumNullsw1,
          HASHBUCKET(SUBSTR(Statss, 13+offset2+5, 1)
            || SUBSTR(Statss, 13+offset2+4, 1) (BYTE(4)))/d AS NumNullsw2,
          HASHBUCKET(SUBSTR(Statss, 13+offset2+3, 1)
            || SUBSTR(Statss, 13+offset2+2, 1) (BYTE(4)))/d AS NumNullsw3,
          HASHBUCKET(SUBSTR(Statss, 13+offset2+1, 1)
            || SUBSTR(Statss, 13+offset2+0, 1) (BYTE(4)))/d 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,
          HASHBUCKET(SUBSTR(Statss, 41+Offset+7, 1)
            || SUBSTR(Statss, 41+Offset+6, 1) (BYTE(4)))/d AS ModeFreqw1,
          HASHBUCKET(SUBSTR(Statss, 41+Offset+5, 1)
            || SUBSTR(Statss, 41+Offset+4, 1) (BYTE(4)))/d AS ModeFreqw2,
          HASHBUCKET(SUBSTR(Statss, 41+Offset+3, 1)
            || SUBSTR(Statss, 41+Offset+2, 1) (BYTE(4)))/d AS ModeFreqw3,
          HASHBUCKET(SUBSTR(Statss, 41+Offset+1, 1)
            || SUBSTR(Statss, 41+Offset+0, 1) (BYTE(4)))/d 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(Statss, 49+Offset+7, 1)
           || SUBSTR(Statss, 49+Offset+6, 1) (BYTE(4)))/d AS NumValuesw1,
          HASHBUCKET(SUBSTR(Statss, 49+Offset+5, 1)
           || SUBSTR(Statss, 49+Offset+4, 1) (BYTE(4)))/d AS NumValuesw2,
          HASHBUCKET(SUBSTR(Statss, 49+Offset+3, 1)
           || SUBSTR(Statss, 49+Offset+2, 1) (BYTE(4)))/d AS NumValuesw3,
          HASHBUCKET(SUBSTR(Statss, 49+Offset+1, 1)
           || SUBSTR(Statss, 49+Offset+0, 1) (BYTE(4)))/d 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(Statss, 57+Offset+7, 1)
           || SUBSTR(Statss, 57+Offset+6, 1) (BYTE(4)))/d AS NumRowsw1,
          HASHBUCKET(SUBSTR(Statss, 57+Offset+5, 1)
           || SUBSTR(Statss, 57+Offset+4, 1) (BYTE(4)))/d AS NumRowsw2,
          HASHBUCKET(SUBSTR(Statss, 57+Offset+3, 1)
           || SUBSTR(Statss, 57+Offset+2, 1) (BYTE(4)))/d AS NumRowsw3,
          HASHBUCKET(SUBSTR(Statss, 57+Offset+1, 1)
           || SUBSTR(Statss, 57+Offset+0, 1) (BYTE(4)))/d 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
            TableName,
            ColumnName,
            CASE
              WHEN trim(version)||trim(bitness) = '632'
                then case
                     when SUBSTR(FieldStatistics, 23, 1) = '00'XB then 16
                     else 0
                     end
              WHEN trim(version)||trim(bitness) = '664'
                then case
                     when SUBSTR(FieldStatistics, 27, 1) = '00'XB then 24
                     else 8
                     end
              WHEN trim(version)||trim(bitness) = '1232'
                then case
                     when SUBSTR(FieldStatistics, 23, 1) = '00'XB then 50
                     else 34
                     end
              WHEN trim(version)||trim(bitness) = '1264'
                then case
                     when SUBSTR(FieldStatistics, 27, 1) = '00'XB then 64
                     else 48
                     end
            END AS offset,
            CASE
              WHEN bitness = 64 THEN 4
              ELSE 0
            END AS offset2,
            CASE
              WHEN version = 12 THEN 16
              ELSE 1
            END AS d,
            FieldStatistics AS Statss
          FROM
           (select
              CASE
                WHEN v.cnt = 0 THEN 6
                ELSE 12
              END AS version,
              CASE
                WHEN b.cnt = 0 then 32
                else 64
                end as bitness
             from
             (select count(*) as cnt
                     from dbc.columnstats
                    where databasename=database and
                          SUBSTR(FieldStatistics, 9, 1) = '03'XB) as v,
             (select count(*) as cnt
                     from dbc.functions
                    where databasename=database and functionname='FLOOR'
                          and platform like '%64%') b) as vb,
            dbc.ColumnStats
          WHERE
            Statss IS NOT NULL
            and DatabaseName = database
           and tablename='vers_stats'
        ) dt
      ) dtt
     ORDER BY 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: 27 Dec 2016