Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Tue, 26 Sep 2006 @ 12:42:35 GMT


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


Subj:   Re: Statistics expert needed
 
From:   Butt, Muhammad Affan

Hi,

Use this query to generate the SQL for Collect Stat. Just copy the output of the Text Field and paste it in SQL Assistance and run it.

Hope this helps.

     SELECT TableName, Text
     FROM
     (
     SELECT
     TableName
     , IndexNumber
     ,RANK() OVER (PARTITION BY TableName, IndexNumber ORDER BY
     ColumnPosition) Seq_Num
     ,CASE WHEN Seq_Num = 1
     THEN 'COLLECT STAT ON ' || TRIM(DatabaseName) || '.' || TRIM(TableName)
     || ' INDEX ( ' || ColumnName
     ELSE
     ', ' || ColumnName
     END AS Text
     FROM DBC.INDICES
     WHERE DatabaseName = 
     UNION
     SELECT TableName, IndexNumber, 100+CSUM(1,1), ');'
     FROM
     (
     SELECT DISTINCT TableName, IndexNumber FROM DBC.INDICES
     WHERE DatabaseName = 
     ) A
     ) IDX
     Order BY TableName,IndexNumber, Seq_Num;

Kind Regards,

Muhammad Affan



     
  <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