Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Fri, 13 Jan 2012 @ 15:49:12 GMT

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

Subj:   Re: Generate collect statistics statement
From:   Dieter Noeth

sobhan.k.pratim wrote:

          > select distinct  b.TVMNameI  TABLE_NAME
          >   from DBC.TVFields  a
          > inner join DBC.TVM b
          > on a.Tableid = b.TVMid
          > AND b.TableKind = 'T'
          > and a.FieldStatistics  is null
          > order by 1

This will return some false positives: there might be multicolumn stats/indexes.

A simple brute force method:

     select databasename, tablename
     from dbc.Tables
     where TableKind = 'T'


     select databasename, tablename
     from dbc.IndexStats
     where IndexStatistics is not null


     select databasename, tablename
     from dbc.MultiColumnStats
     where ColumnsStatistics is not null


     select databasename, tablename
     from dbc.ColumnStats
     where FieldStatistics is not null

Or of course my StatsInfo view :-)

     select DatabaseName, TableName, CollectStatement from StatsInfo qualify
        over (partition by DatabaseName, TableName) = 'Y'
     and StatsType in ('UPI','NUPI')


  <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