Archives of the TeradataForum

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

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

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