Archives of the TeradataForum

Message Posted: Wed, 23 Mar 2005 @ 20:40:56 GMT

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

I used this script to generate a Collect Stat command for a multi-column index. You can change it to figure out LASTALTERTIMESTAMP field.

     SELECT 'COLLECT STATISTICS ON =
     '||TRIM(TD.DatabaseName)||'.'||TRIM(TD.tableName) (TITLE ''),
            '       INDEX('||
            TRIM(TD.col1)||TRIM(TD.col2)||TRIM(TD.col3) (TITLE ''),
            '      =
     '||TRIM(TD.col4)||TRIM(TD.col5)||TRIM(TD.col6)||TRIM(TD.col7)||');' (TITLE '')

     FROM (SELECT Indices.databasename,Indices.tablename,Indices.IndexNumber,
                  MAX(CASE WHEN Columnposition = 1 THEN columnname else ''
     END) AS COL1,
                  MAX(CASE WHEN Columnposition = 2 THEN ','||TRIM(columnname) else '' END) AS COL2,
                  MAX(CASE WHEN Columnposition = 3 THEN ','||TRIM(columnname) else '' END) AS COL3,
                  MAX(CASE WHEN Columnposition = 4 THEN ','||TRIM(columnname) else '' END) AS COL4,
                  MAX(CASE WHEN Columnposition = 5 THEN ','||TRIM(columnname) else '' END) AS COL5,
                  MAX(CASE WHEN Columnposition = 6 THEN ','||TRIM(columnname) else '' END) AS COL6,
                  MAX(CASE WHEN Columnposition = 7 THEN ','||TRIM(columnname) else '' END) AS COL7

           FROM   DBC.Indices

                 ,(SELECT databasename,tablename,IndexNumber
                   FROM  SYSDBA.Indices
                   WHERE IndexName IS NULL
                     AND IndexStatistics IS NOT NULL)  AS TD1

           WHERE  Indices.IndexName IS NULL
             AND  td1.databasename = Indices.databasename
             AND  td1.tablename    = Indices.tablename
             AND  td1.IndexNumber  = Indices.IndexNumber

           GROUP  BY 1,2,3
           having MIN(ColumnPOSITION) < MAX(Columnposition)) AS TD

     ,DBC.Dbase

     WHERE  TD.databasename = dbase.DatabaseName
       AND  (Dbase.OwnerName <> 'DBC' OR dbase.DatabaseName = 'SYSDBA')
       AND  dbase.DatabaseName <> 'DBC'

     ORDER BY 1
     ;

Rudel

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