Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Tue, 02 Dec 2003 @ 12:54:45 GMT


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


Subj:   Re: Help with creation of collect stats script
 
From:   Vivek Pandey

Hi Sodhi,

May be something like the below sql could help you out. You may do some R&D and modify the below SQL for the other DBC stats views columnstats & multicolumnstats (new in v2r5).

From your requirements I also think that you need to gerenalize this SQL for all the tables in your database too.

SELECT
   'COLLECT STATISTICS ON db_name.t_name INDEX ('
  || (max (case when ColumnPosition = 1 then trim (both from ColumnName) end))
  || (coalesce (max (case when ColumnPosition =  2 then  ', ' || trim (both from ColumnName) end),  ''))
  || (coalesce (max (case when ColumnPosition =  3 then  ', ' || trim (both from ColumnName) end),  ''))
  || (coalesce (max (case when ColumnPosition =  4 then  ', ' || trim (both from ColumnName) end),  ''))
  || ');'  (title '')
FROM DBC.IndexStats
WHERE (DatabaseName, TableName,  IndexNumber) IN (
   SELECT DatabaseName, TableName,  IndexNumber
   FROM   DBC.IndexStats
   WHERE  DatabaseName = db_name AND Tablename = t_name AND
          IndexStatistics IS NOT NULL
   GROUP BY DatabaseName, TableName,  IndexNumber
)
GROUP BY IndexNumber
HAVING count (*) > 1
ORDER BY IndexNumber
;

Thanks, Vivek.



     
  <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