Archives of the TeradataForum

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

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

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