Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Wed, 05 Jan 2011 @ 19:50:47 GMT


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


Subj:   Re: Copying Statistics from one table to another
 
From:   Barner, Eric

For non Teradata 13 or Non-identical tables, here is a piece of code (macro) you can use to generate the statistics statements from a source table.

You can modify this easily to add extra parameters to generate code to take into account for a target table if desired.


Eric W. Barner


     USAGE:
      EXEC SYS_MGMT.stats_create ('databasename','tablename');


     REPLACE macro SYS_MGMT.stats_create (dbname varchar(30) , tblname
     varchar(30)) as ( select col1|| ' '||col2|| ' '||col3 (varchar (10000)) FROM
     ( SELECT 'COLLECT STATS "'||TRIM(databasename)||'".'||TRIM(tablename)||'
     INDEX  '
     AS col1
     , (indexname)AS col2
     , ' ;' as  col3
     FROM
     (
     SEL databasename
     ,tablename
     ,TRIM(INDEXNAME) AS INDEXNAME
     ,count(*) as n_cols
     FROM dbc.INDEXSTATS
     WHERE databasename =  :dbname
     and tablename = :tblname
     and indexstatistics is not null
     and indexname is not null
     group by  1,2,3
     ) dt

     )  A


     UNION
     select  col1|| ' '||col2|| ' '||col3  (varchar (10000)) FROM ( SELECT
     'COLLECT STATS "'||TRIM(databasename)||'".'||TRIM(tablename)||' INDEX ('
     AS col1
     , MAX(CASE WHEN columnposition =1 THEN ColumnName ELSE '' END) || MAX(CASE
     WHEN columnposition =2 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
     columnposition =3 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
     columnposition =4 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
     columnposition =5 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
     columnposition =6 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
     columnposition =7 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
     columnposition =8 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
     columnposition =9 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
     columnposition =10 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
     columnposition =11 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
     columnposition =12 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
     columnposition =13 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
     columnposition =14 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
     columnposition =15 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
     columnposition =16 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
     columnposition =17 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
     columnposition =18 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
     columnposition =19 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
     columnposition =20 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
     columnposition =21 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
     columnposition =22 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
     columnposition =23 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
     columnposition =24 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
     columnposition =25 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
     columnposition =26 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
     columnposition =27 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
     columnposition =28 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
     columnposition =29 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
     columnposition =30 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
     columnposition =31 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
     columnposition =32 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
     columnposition =33 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
     columnposition =34 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
     columnposition =35 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
     columnposition =36 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
     columnposition =37 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
     columnposition =38 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
     columnposition =39 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
     columnposition =40 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
     columnposition =41 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
     columnposition =42 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
     columnposition =43 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
     columnposition =44 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
     columnposition =45 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
     columnposition =46 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
     columnposition =47 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
     columnposition =48 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
     columnposition =49 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
     columnposition =50 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
     columnposition =51 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
     columnposition =52 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
     columnposition =53 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
     columnposition =54 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
     columnposition =55 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
     columnposition =56 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
     columnposition =57 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
     columnposition =58 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
     columnposition =59 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
     columnposition =60 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
     columnposition =61 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
     columnposition =62 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
     columnposition =63 THEN ',' || ColumnName ELSE '' END) || MAX(CASE WHEN
     columnposition =64 THEN ',' || ColumnName ELSE '' END) AS col2 , ' );' as
     col3 FROM ( SEL A.databasename ,A.tablename ,A.columnposition
     ,TRIM(A.columnName) AS ColumnName
     ,count(*) as n_cols
     FROM dbc.INDEXSTATS  a
     join
     (
     SELECT DISTINCT DATABASENAME, TABLENAME FROM dbc.INDEXSTATS WHERE
     indexstatistics is not null and indexname is  null

      )  B

      on  A.DATABASENAME = B.DATABASENAME
     AND A.TABLENAME= B.TABLENAME

     WHERE A.databasename =  :DBNAME
     AND B.TABLENAME = :TBLNAME

     and A.indexname is  null
     group by  1,2,3,4

     ) dt
     GROUP BY 1
     ) A



     UNION
     select  col1|| ' '||col2|| ' '||col3  (varchar (10000))
     FROM
     (
     SELECT STATISTICSID,
     'COLLECT STATS "'||TRIM(databasename)||'".'||TRIM(tablename)||' COLUMN
     (' AS col1
     , MAX(CASE WHEN columnposition =1 THEN ColumnName ELSE '' END) ||
     MAX(CASE WHEN columnposition =2 THEN ',' || ColumnName ELSE '' END) ||
     MAX(CASE WHEN columnposition =3 THEN ',' || ColumnName ELSE '' END) ||
     MAX(CASE WHEN columnposition =4 THEN ',' || ColumnName ELSE '' END) ||
     MAX(CASE WHEN columnposition =5 THEN ',' || ColumnName ELSE '' END) ||
     MAX(CASE WHEN columnposition =6 THEN ',' || ColumnName ELSE '' END) ||
     MAX(CASE WHEN columnposition =7 THEN ',' || ColumnName ELSE '' END) ||
     MAX(CASE WHEN columnposition =8 THEN ',' || ColumnName ELSE '' END) ||
     MAX(CASE WHEN columnposition =9 THEN ',' || ColumnName ELSE '' END) ||
     MAX(CASE WHEN columnposition =10 THEN ',' || ColumnName ELSE '' END) ||
     MAX(CASE WHEN columnposition =11 THEN ',' || ColumnName ELSE '' END) ||
     MAX(CASE WHEN columnposition =12 THEN ',' || ColumnName ELSE '' END) ||
     MAX(CASE WHEN columnposition =13 THEN ',' || ColumnName ELSE '' END) ||
     MAX(CASE WHEN columnposition =14 THEN ',' || ColumnName ELSE '' END) ||
     MAX(CASE WHEN columnposition =15 THEN ',' || ColumnName ELSE '' END) ||
     MAX(CASE WHEN columnposition =16 THEN ',' || ColumnName ELSE '' END) ||
     MAX(CASE WHEN columnposition =17 THEN ',' || ColumnName ELSE '' END) ||
     MAX(CASE WHEN columnposition =18 THEN ',' || ColumnName ELSE '' END) ||
     MAX(CASE WHEN columnposition =19 THEN ',' || ColumnName ELSE '' END) ||
     MAX(CASE WHEN columnposition =20 THEN ',' || ColumnName ELSE '' END) ||
     MAX(CASE WHEN columnposition =21 THEN ',' || ColumnName ELSE '' END) ||
     MAX(CASE WHEN columnposition =22 THEN ',' || ColumnName ELSE '' END) ||
     MAX(CASE WHEN columnposition =23 THEN ',' || ColumnName ELSE '' END) ||
     MAX(CASE WHEN columnposition =24 THEN ',' || ColumnName ELSE '' END) ||
     MAX(CASE WHEN columnposition =25 THEN ',' || ColumnName ELSE '' END) ||
     MAX(CASE WHEN columnposition =26 THEN ',' || ColumnName ELSE '' END) ||
     MAX(CASE WHEN columnposition =27 THEN ',' || ColumnName ELSE '' END) ||
     MAX(CASE WHEN columnposition =28 THEN ',' || ColumnName ELSE '' END) ||
     MAX(CASE WHEN columnposition =29 THEN ',' || ColumnName ELSE '' END) ||
     MAX(CASE WHEN columnposition =30 THEN ',' || ColumnName ELSE '' END) ||
     MAX(CASE WHEN columnposition =31 THEN ',' || ColumnName ELSE '' END) ||
     MAX(CASE WHEN columnposition =32 THEN ',' || ColumnName ELSE '' END) ||
     MAX(CASE WHEN columnposition =33 THEN ',' || ColumnName ELSE '' END) ||
     MAX(CASE WHEN columnposition =34 THEN ',' || ColumnName ELSE '' END) ||
     MAX(CASE WHEN columnposition =35 THEN ',' || ColumnName ELSE '' END) ||
     MAX(CASE WHEN columnposition =36 THEN ',' || ColumnName ELSE '' END) ||
     MAX(CASE WHEN columnposition =37 THEN ',' || ColumnName ELSE '' END) ||
     MAX(CASE WHEN columnposition =38 THEN ',' || ColumnName ELSE '' END) ||
     MAX(CASE WHEN columnposition =39 THEN ',' || ColumnName ELSE '' END) ||
     MAX(CASE WHEN columnposition =40 THEN ',' || ColumnName ELSE '' END) ||
     MAX(CASE WHEN columnposition =41 THEN ',' || ColumnName ELSE '' END) ||
     MAX(CASE WHEN columnposition =42 THEN ',' || ColumnName ELSE '' END) ||
     MAX(CASE WHEN columnposition =43 THEN ',' || ColumnName ELSE '' END) ||
     MAX(CASE WHEN columnposition =44 THEN ',' || ColumnName ELSE '' END) ||
     MAX(CASE WHEN columnposition =45 THEN ',' || ColumnName ELSE '' END) ||
     MAX(CASE WHEN columnposition =46 THEN ',' || ColumnName ELSE '' END) ||
     MAX(CASE WHEN columnposition =47 THEN ',' || ColumnName ELSE '' END) ||
     MAX(CASE WHEN columnposition =48 THEN ',' || ColumnName ELSE '' END) ||
     MAX(CASE WHEN columnposition =49 THEN ',' || ColumnName ELSE '' END) ||
     MAX(CASE WHEN columnposition =50 THEN ',' || ColumnName ELSE '' END) ||
     MAX(CASE WHEN columnposition =51 THEN ',' || ColumnName ELSE '' END) ||
     MAX(CASE WHEN columnposition =52 THEN ',' || ColumnName ELSE '' END) ||
     MAX(CASE WHEN columnposition =53 THEN ',' || ColumnName ELSE '' END) ||
     MAX(CASE WHEN columnposition =54 THEN ',' || ColumnName ELSE '' END) ||
     MAX(CASE WHEN columnposition =55 THEN ',' || ColumnName ELSE '' END) ||
     MAX(CASE WHEN columnposition =56 THEN ',' || ColumnName ELSE '' END) ||
     MAX(CASE WHEN columnposition =57 THEN ',' || ColumnName ELSE '' END) ||
     MAX(CASE WHEN columnposition =58 THEN ',' || ColumnName ELSE '' END) ||
     MAX(CASE WHEN columnposition =59 THEN ',' || ColumnName ELSE '' END) ||
     MAX(CASE WHEN columnposition =60 THEN ',' || ColumnName ELSE '' END) ||
     MAX(CASE WHEN columnposition =61 THEN ',' || ColumnName ELSE '' END) ||
     MAX(CASE WHEN columnposition =62 THEN ',' || ColumnName ELSE '' END) ||
     MAX(CASE WHEN columnposition =63 THEN ',' || ColumnName ELSE '' END) ||
     MAX(CASE WHEN columnposition =64 THEN ',' || ColumnName ELSE '' END)
     AS col2 ,
     ' );' as col3
     FROM
     (
     SEL
     databasename
     ,tablename
     ,columnposition
     ,STATISTICSID
     ,TRIM(columnName) AS ColumnName
     FROM
     dbc.MultiColumnStats
     WHERE databasename = :DBNAME
     and TABLENAME = :TBLNAME
     ) dt
     GROUP BY 1 ,2
     ) A
     UNION
     select col1|| ' '||col2|| ' '||col3 (varchar (10000))
     FROM
     (
     SEL
     'COLLECT STATS "'||TRIM(databasename)||'".'||TRIM(tablename)||' COLUMN
     (' AS col1,
     (Columnname)  as col2,
     ');' as col3

     FROM
     (
     SEL
     databasename
     ,tablename

     ,TRIM(columnName) AS ColumnName
     FROM
     dbc.Columnstats
     WHERE databasename= :DBNAME
     AND TABLENAME=:TBLNAME
     and FieldStatistics is not null

     ) dt

     ) a

     ;
     );


     
  <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: 23 Jun 2019