Archives of the TeradataForum

Message Posted: Wed, 03 Dec 2003 @ 14:07:44 GMT

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

Sorry, my last email was trim....

Here how I collect stats on multiple column index.

Rudel

        -----------------------------------------------------------------------------------------------
        -- generate collect stat command for :
        --         non quaified index and multiple column index
        ------------------------------------------------------------------------------------------------

        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 <> 'desire_databasename'

        ORDER BY 1
        ;

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