Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Fri, 30 Jan 2015 @ 14:34:39 GMT


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


Subj:   Re: TD14 Optimised stats collection
 
From:   Pirri, Antonio

Hi all,

Depends of your TD version, maybe this can Help you.

     select
     databasename
     ,Tablename
     ,ColumnName
     ,CASE
          WHEN orden = 1 and nextTable = Tablename
             THEN 'COLLECT STATS COLUMN ( ' || trim(columnname) || ') '
          WHEN orden = 1 and nextTable <> Tablename
             THEN 'COLLECT STATS COLUMN ( ' || trim(columnname) || ') ON ' || trim(databasename) || '.' || trim(TableName) || ';'
          WHEN nextTable <> Tablename
             THEN ',COLUMN( ' || trim(ColumnName) || ' ) ON ' || trim(databasename) || '.' || trim(TableName) || ';'
             ELSE ',COLUMN( ' ||  trim(ColumnName) || ') '
     END as StatsCMD
     from
     (
     select
     databasename
     ,Tablename
     ,ColumnName
     ,ROW_NUMBER() over(partition by  databasename ,Tablename order by Databasename,
     Tablename, Columnname)  as orden
     ,MAX(Tablename) over(partition by  databasename order by Databasename, Tablename,
     Columnname rows between 1 following and 1 following)  as nextTable from dbc.statsv where
     databasename in ('XXXXXX') and ColumnName is not null
     ) tmp
     order by 1,2,3

Take Care..

Antonio Pirri
Professional Services Consultant
Teradata Argentina



     
  <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