Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Thu, 08 Sep 2011 @ 13:23:16 GMT


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


Subj:   Re: Stats Refresh Post Expansion
 
From:   Fenwick, Ruth

Okay....I thought about it and had a few minutes so I wrote the following....it would be cleaner if the UPDATE statement was wrapped in a macro, but since this a one-off, I'm not going through change control to create macro for the sake of 'elegance' ;-)

The following will generate collect stats statements for all NUSI indices.....modify as you see fit.

Determine the largest secondary index on your system (you may be in for a surprise like I was, when you find some purchased product has 10 columns in a secondary index!)

     SEL MAX(columnposition)
     FROM dbc.indices WHERE indextype = 's'
     AND uniqueflag = 'n';

     CREATE VOLATILE TABLE  nusi_col ,NO FALLBACK ,
             NO BEFORE JOURNAL,
             NO AFTER JOURNAL,
             CHECKSUM = DEFAULT
             (
              databasename CHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC,
              tablename CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
              indexnumber SMALLINT,
              NameText VARCHAR(512) CHARACTER SET LATIN NOT CASESPECIFIC
     )
        UNIQUE PRIMARY INDEX ( databasename, tablename, indexnumber ) on
     commit preserve rows;

     INSERT INTO nusi_col
     SEL databasename, tablename, indexnumber, columnname
     FROM dbc.indices
     WHERE indextype = 's'
     AND uniqueflag = 'n'
     AND columnposition = 1;


     --run the following repeating SQL until you reach your max
     columnposition.
      UPDATE nusi_col
      SET NameText = TRIM(NameText) ||
                     ','            ||
                     TRIM(columnname)
      WHERE dbc.indices.databasename = nusi_col.databasename
      AND dbc.indices.tablename = nusi_col.tablename
      AND dbc.indices.indexnumber = nusi_col.indexnumber
       AND  dbc.indices.columnposition = 2;


       UPDATE nusi_col
      SET NameText = TRIM(NameText) ||
                     ','            ||
                     TRIM(columnname)
      WHERE dbc.indices.databasename = nusi_col.databasename
      AND dbc.indices.tablename = nusi_col.tablename
      AND dbc.indices.indexnumber = nusi_col.indexnumber
       AND  dbc.indices.columnposition = 3;

       UPDATE nusi_col
      SET NameText = TRIM(NameText) ||
                     ','            ||
                     TRIM(columnname)
      WHERE dbc.indices.databasename = nusi_col.databasename
      AND dbc.indices.tablename = nusi_col.tablename
      AND dbc.indices.indexnumber = nusi_col.indexnumber
       AND  dbc.indices.columnposition = 4;

       UPDATE nusi_col
      SET NameText = TRIM(NameText) ||
                     ','            ||
                     TRIM(columnname)
      WHERE dbc.indices.databasename = nusi_col.databasename
      AND dbc.indices.tablename = nusi_col.tablename
      AND dbc.indices.indexnumber = nusi_col.indexnumber
       AND  dbc.indices.columnposition = 5;

       UPDATE nusi_col
      SET NameText = TRIM(NameText) ||
                     ','            ||
                     TRIM(columnname)
      WHERE dbc.indices.databasename = nusi_col.databasename
      AND dbc.indices.tablename = nusi_col.tablename
      AND dbc.indices.indexnumber = nusi_col.indexnumber
       AND  dbc.indices.columnposition = 6;

       UPDATE nusi_col
      SET NameText = TRIM(NameText) ||
                     ','            ||
                     TRIM(columnname)
      WHERE dbc.indices.databasename = nusi_col.databasename
      AND dbc.indices.tablename = nusi_col.tablename
      AND dbc.indices.indexnumber = nusi_col.indexnumber
       AND  dbc.indices.columnposition = 7;

       UPDATE nusi_col
      SET NameText = TRIM(NameText) ||
                     ','            ||
                     TRIM(columnname)
      WHERE dbc.indices.databasename = nusi_col.databasename
      AND dbc.indices.tablename = nusi_col.tablename
      AND dbc.indices.indexnumber = nusi_col.indexnumber
       AND  dbc.indices.columnposition = 8;

       UPDATE nusi_col
      SET NameText = TRIM(NameText) ||
                     ','            ||
                     TRIM(columnname)
      WHERE dbc.indices.databasename = nusi_col.databasename
      AND dbc.indices.tablename = nusi_col.tablename
      AND dbc.indices.indexnumber = nusi_col.indexnumber
       AND  dbc.indices.columnposition = 9;

       UPDATE nusi_col
      SET NameText = TRIM(NameText) ||
                     ','            ||
                     TRIM(columnname)
      WHERE dbc.indices.databasename = nusi_col.databasename
      AND dbc.indices.tablename = nusi_col.tablename
      AND dbc.indices.indexnumber = nusi_col.indexnumber
       AND  dbc.indices.columnposition = 10;


     SEL 'COLLECT STATS ' || TRIM(DATABASENAME) || '.' || TRIM(TABLENAME) ||
     ' INDEX ( ' || TRIM(NAMETEXT) || ' ) ;'
       FROM NUSI_COL
       ORDER BY 1;

Ruth Fenwick | Teradata Technical Lead, Enterprise Data Management,
Enterprise Information Management | Royal Bank of Canada |



     
  <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