Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Thu, 19 Mar 2015 @ 21:17:17 GMT


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


Subj:   dbc.indexstats table
 
From:   JAMES PARK

Dbc.indexstats table existed before TD 14. But now it doesn't.

I am trying to create collect stats statement. What table do I have to use instead of DBC.indexstats table?

     with have_stats (db, tbl, ixnum) as
     (select databasename, tablename,
     indexnumber
     from dbc.indexstats
     where columnposition = 1
     and indexstatistics is not null)

     select
     case
     when a.columnposition=1
        then 'collect statistics on '||trim(a.databasename)||'.'||
          trim(a.tablename)||' index('||trim(a.columnname) when a.columnposition = h.lastcol
        then ' ,'||trim(a.columnname)||' );'
     else
        ' ,'||trim(columnname)
     end

     from dbc.indexstats a,

     (select databasename, tablename,
     indexnumber, max(columnposition) lastcol from dbc.indexstats where (databasename,
     tablename, indexnumber) in (sel db, tbl, ixnum from have_stats) group by 1,2,3 ) h where
     a.databasename = h.databasename and a.tablename = h.tablename and a.indexnumber =
     h.indexnumber

     order by a.databasename, a.tablename,
     a.indexnumber, a.columnposition;

James Park
Database Specialist



     
  <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