Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Fri, 04 Dec 2003 @ 22:23:51 GMT


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


Subj:   Re: Help with creation of collect stats script
 
From:   Singh Sodhi, Prabhjot

Dear All,

The FORUM is still quiet active, I came to check it after a pretty long time.

I got the things working!!! :-) . Thanks to you all esp. Rudel, Vishal, Vivek and all others.

I am posting the macro just for any future reference.

It returns the following three answer sets:

1. Single column and single column indexes.

2. Multi column stats

3. Multi column index stats


/*********************************************************************/

REPLACE MACRO GetStatsInfo
   ( DBName Varchar(30)
   )

AS (

/*  Generate script for single column stats and single column index stats */

SELECT  'COLLECT STATISTICS ON
'||TRIM(dbase.DatabaseName)||'.'||TRIM(tvm.TVMName)||
       (
                CASE
                        WHEN    TD.databasename IS NULL
             THEN ' COLUMN '||TRIM(tvfields.FieldName)||';'
                        WHEN    td.indexname is Null
             THEN ' INDEX ('||TRIM(tvfields.FieldName)||');'
                        ELSE    ' INDEX '||TRIM(td.indexname)||';'
        END) (TITLE '')

FROM      DBC.Dbase

INNER JOIN DBC.TVM
        ON      tvm.DatabaseId = dbase.DatabaseId

INNER JOIN DBC.tvfields
        ON      tvm.tvmid = tvfields.tableid

LEFT JOIN (
SELECT  databasename,tablename,IndexNumber,indexname,MIN(Columnname) AS COL
FROM       DBC.Indices
GROUP     BY 1,2,3,4
HAVING  MIN(Columnname) = MAX(Columnname)
        OR      IndexName IS NOT NULL) AS TD
        ON      TD.databasename = dbase.DatabaseName
        AND
   TD.tablename = TVM.tvmname
        AND
   TD.COL = tvfields.FieldName

WHERE    tvfields.FieldStatistics IS NOT NULL
        AND     Dbase.OwnerName IN ( Sel child
From    dbc.children
Where   parent = :DBName)
ORDER   BY dbase.DatabaseName, tvm.TVMName;

/*  Generate script for multi column stats
    It has a max of 17 columns used for collecting stats */
sel 'Collect stats on ' || trim(ms1.databasename) || '.' || =
trim(ms1.tablename) || ' column ( ' ,
                Case
                        When    ms1.ColumnName IS NOT NULL THEN ms1.ColumnName
                        ELSE    ''
                END,
                Case
                        When    ms2.ColumnName IS NOT NULL THEN ', ' || ms2.ColumnName
                        ELSE    ''
                END,
                Case
                        When    ms3.ColumnName IS NOT NULL THEN ', ' || ms3.ColumnName
                        ELSE    ''
                END,
                Case
                        When    ms4.ColumnName IS NOT NULL THEN ', ' || ms4.ColumnName
                        ELSE    ''
                END,
                Case
                        When    ms5.ColumnName IS NOT NULL THEN ', ' || ms5.ColumnName
                        ELSE    ''
                END,
                Case
                        When    ms6.ColumnName IS NOT NULL THEN ', ' || ms6.ColumnName
                        ELSE    ''
                END,
                Case
                        When    ms7.ColumnName IS NOT NULL THEN ', ' || ms7.ColumnName
                        ELSE    ''
                END,
                Case
                        When    ms8.ColumnName IS NOT NULL THEN ', ' || ms8.ColumnName
                        ELSE    ''
                END,
                Case
                        When    ms9.ColumnName IS NOT NULL THEN ', ' || ms9.ColumnName
                        ELSE    ''
                END,
                Case
                        When    ms10.ColumnName IS NOT NULL THEN ', ' || ms10.ColumnName
                        ELSE    ''
                END,
                Case
                        When    ms11.ColumnName IS NOT NULL THEN ', ' || ms11.ColumnName
                        ELSE    ''
                END,
                Case
                        When    ms12.ColumnName IS NOT NULL THEN ', ' || ms12.ColumnName
                        ELSE    ''
                END,
                Case
                        When    ms13.ColumnName IS NOT NULL THEN ', ' || ms13.ColumnName
                        ELSE    ''
                END,
                Case
                        When    ms14.ColumnName IS NOT NULL THEN ', ' || ms14.ColumnName
                        ELSE    ''
                END,
                Case
                        When    ms15.ColumnName IS NOT NULL THEN ', ' || ms15.ColumnName
                        ELSE    ''
                END,
                Case
                        When    ms16.ColumnName IS NOT NULL THEN ', ' || ms16.ColumnName
                        ELSE    ''
                END,
  ');'
From
  dbc.multicolumnstats ms1
    Left Outer Join
  dbc.multicolumnstats ms2
        On      (ms1.Databasename = ms2.Databasename
        And
        ms1.Tablename = ms2.Tablename
        And
        ms1.StatisticsId = ms2.StatisticsId
        And
        ms2.ColumnPosition = 2)
    Left Outer Join
  dbc.multicolumnstats ms3
        On      (ms1.Databasename = ms3.Databasename
        And
        ms1.Tablename = ms3.Tablename
        And
        ms1.StatisticsId = ms3.StatisticsId
        And
        ms3.ColumnPosition = 3)
    Left Outer Join
  dbc.multicolumnstats ms4
        On      (ms1.Databasename = ms4.Databasename
        And
        ms1.Tablename = ms4.Tablename
        And
        ms1.StatisticsId = ms4.StatisticsId
        And
        ms4.ColumnPosition = 4)
    Left Outer Join
  dbc.multicolumnstats ms5
        On      (ms1.Databasename = ms5.Databasename
        And
        ms1.Tablename = ms5.Tablename
        And
        ms1.StatisticsId = ms5.StatisticsId
        And
        ms4.ColumnPosition = 5)
    Left Outer Join
  dbc.multicolumnstats ms6
        On      (ms1.Databasename = ms6.Databasename
        And
        ms1.Tablename = ms6.Tablename
        And
        ms1.StatisticsId = ms6.StatisticsId
        And
        ms4.ColumnPosition = 6)
    Left Outer Join
  dbc.multicolumnstats ms7
        On      (ms1.Databasename = ms7.Databasename
        And
        ms1.Tablename = ms7.Tablename
        And
        ms1.StatisticsId = ms7.StatisticsId
        And
        ms4.ColumnPosition = 7)
    Left Outer Join
  dbc.multicolumnstats ms8
        On      (ms1.Databasename = ms8.Databasename
        And
        ms1.Tablename = ms8.Tablename
        And
        ms1.StatisticsId = ms8.StatisticsId
        And
        ms4.ColumnPosition = 8)
    Left Outer Join
  dbc.multicolumnstats ms9
        On      (ms1.Databasename = ms9.Databasename
        And
        ms1.Tablename = ms9.Tablename
        And
        ms1.StatisticsId = ms9.StatisticsId
        And
        ms4.ColumnPosition = 9)
    Left Outer Join
  dbc.multicolumnstats ms10
        On      (ms1.Databasename = ms10.Databasename
        And
        ms1.Tablename = ms10.Tablename
        And
        ms1.StatisticsId = ms10.StatisticsId
        And
        ms4.ColumnPosition = 10)
    Left Outer Join
  dbc.multicolumnstats ms11
        On      (ms1.Databasename = ms11.Databasename
        And
        ms1.Tablename = ms11.Tablename
        And
        ms1.StatisticsId = ms11.StatisticsId
        And
        ms4.ColumnPosition = 11)
    Left Outer Join
  dbc.multicolumnstats ms12
        On      (ms1.Databasename = ms12.Databasename
        And
        ms1.Tablename = ms12.Tablename
        And
        ms1.StatisticsId = ms12.StatisticsId
        And
        ms4.ColumnPosition = 12)
    Left Outer Join
  dbc.multicolumnstats ms13
        On      (ms1.Databasename = ms13.Databasename
        And
        ms1.Tablename = ms13.Tablename
        And
        ms1.StatisticsId = ms13.StatisticsId
        And
        ms4.ColumnPosition = 13)
    Left Outer Join
  dbc.multicolumnstats ms14
        On      (ms1.Databasename = ms14.Databasename
        And
        ms1.Tablename = ms14.Tablename
        And
        ms1.StatisticsId = ms14.StatisticsId
        And
        ms4.ColumnPosition = 14)
    Left Outer Join
  dbc.multicolumnstats ms15
        On      (ms1.Databasename = ms15.Databasename
        And
        ms1.Tablename = ms15.Tablename
        And
        ms1.StatisticsId = ms15.StatisticsId
        And
        ms4.ColumnPosition = 15)
    Left Outer Join
  dbc.multicolumnstats ms16
        On      (ms1.Databasename = ms16.Databasename
        And
        ms1.Tablename = ms16.Tablename
        And
        ms1.StatisticsId = ms16.StatisticsId
        And
        ms4.ColumnPosition = 16)
Where   ms1.databasename IN ( Sel child
From    dbc.children
Where   parent = :DBName)
        And       ms1.ColumnPosition = 1;


/*  Generate script for multi column index stats */

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     DBC.Indexstats
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 IN ( Sel child
From    dbc.children
Where   parent = :DBName)

ORDER   BY 1
        ;

);

/*****************************************************************/

The second query currently looks a bit crude, but didnot have enough time to fix it. May be some day later!!!

My 2 AUDs for all those who responded!!!


Cheers,

Prabhjot.



     
  <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