Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Tue, 01 Dec 1999 @ 01:54:26 GMT


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


Subj:   SQL to find indexes without stats
 
From:   Michael McIntire

Ever need to figure out what indexes do not have stats? For 500 tables? Or how to build that multi-column index clause from the dictionary?

One word of caution: I had to re-format without rerunning it, in order to have it fit in the email with an minimum of wrapping, so there may be some syntax errors...


Michael McIntire


Sql to find multi-column indexes without stats:

     SELECT dbase.DatabaseName (named dbname),
             tvm.TVMName(NAMED TableName),
           indexes.IndexNumber (named indexnumber, FORMAT 'ZZ9'),
             count(*) (named cnt),
           max(case when ((indexstatistics is NULL)
                     and (fieldposition = 1)) then 1 else 0 end) (named IsStat),
         '(' || trim(max(case when fieldposition = 1
                     then tvfields.FieldName else NULL end))
             || trim(max(case when fieldposition = 2
                     then (',' || tvfields.FieldName) else ' ' end))
             || trim(max(case when fieldposition = 3
                     then (',' || tvfields.FieldName) else ' ' end))
             || trim(max(case when fieldposition = 4
                     then (',' || tvfields.FieldName) else ' ' end))
             || trim(max(case when fieldposition = 5
                     then (',' || tvfields.FieldName) else ' ' end))
             || trim(max(case when fieldposition = 6
                     then (',' || tvfields.FieldName) else ' ' end))
             || trim(max(case when fieldposition = 7
                     then (',' || tvfields.FieldName) else ' ' end))
             || trim(max(case when fieldposition = 8
                     then (',' || tvfields.FieldName) else ' ' end))
             || trim(max(case when fieldposition = 9
                     then (',' || tvfields.FieldName) else ' ' end))
             || trim(max(case when fieldposition = 10
                     then (',' || tvfields.FieldName) else ' ' end))
             || ')' (named IndexSTMT, Title ''),
              'collect statistics on ' || trim(dbase.databasename)
                     || '.' || trim(tvm.tvmname) || ' index ('
             || trim(max(case when fieldposition = 1
                     then tvfields.FieldName else NULL end))
             || trim(max(case when fieldposition = 2
                     then (',' || tvfields.FieldName) else ' ' end))
             || trim(max(case when fieldposition = 3
                     then (',' || tvfields.FieldName) else ' ' end))
             || trim(max(case when fieldposition = 4
                     then (',' || tvfields.FieldName) else ' ' end))
             || trim(max(case when fieldposition = 5
                     then (',' || tvfields.FieldName) else ' ' end))
             || trim(max(case when fieldposition = 6
                     then (',' || tvfields.FieldName) else ' ' end))
             || trim(max(case when fieldposition = 7
                     then (',' || tvfields.FieldName) else ' ' end))
             || trim(max(case when fieldposition = 8
                     then (',' || tvfields.FieldName) else ' ' end))
             || trim(max(case when fieldposition = 9
                     then (',' || tvfields.FieldName) else ' ' end))
             || trim(max(case when fieldposition = 10
                     then (',' || tvfields.FieldName) else ' ' end))
             || ');' (named collectSTAT, Title '')
     FROM DBC.dbase, DBC.tvm, DBC.tvfields, DBC.indexes
     WHERE   tvm.DatabaseId = dbase.DatabaseId
     AND     tvm.tvmid = indexes.tableid
     AND     tvm.tvmid = tvfields.tableid
     and      dbase.databasename = 'database'
     AND     tvfields.fieldid = indexes.fieldid
     group by 1,2,3
     having count(*) NE 1 and IsStat = 1
     order by 1,2,3;

SQL to find single column indexes without stats:

     SELECT dbase.DatabaseName (named dbname),
               tvm.TVMName(NAMED TableName),
               indexes.IndexNumber( named indexnumber, FORMAT 'ZZ9'),
               count(*) (named cnt),
               max(case when ((fieldstatistics is NULL)
                             and (fieldposition = 1))
                             then 1 else NULL end) (named IsStat),
             '(' || trim(max(case when fieldposition = 1
                             then fieldname
                             else NULL end)) || ')' (named IndexSTMT),
             'collect statistics on ' || trim(dbase.databasename)
                     || '.' || trim(tvm.tvmname) || ' column ('
                     || trim(max(case when fieldposition = 1
                                     then fieldname else NULL end))
                     || ');' (named CollectStat, Title '')
     FROM DBC.dbase, DBC.tvm, DBC.tvfields, DBC.indexes
     WHERE   tvm.DatabaseId = dbase.DatabaseId
     AND     tvm.tvmid = indexes.tableid
     AND     tvm.tvmid = tvfields.tableid
     AND     dbase.databasename = 'database'
     AND     tvfields.fieldid = indexes.fieldid
     group by 1,2,3
     having count(*) = 1 and IsStat = 1
     order by 1,2,3;


     
  <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