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'

     except

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

     except

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

     except

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

Or of course my StatsInfo view :-)

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

Dieter



     
  <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: 27 Dec 2016