Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Mon, 18 Aug 2003 @ 16:09:22 GMT


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


Subj:   Re: Show index
 
From:   Terry Stover

A more elaborate implementation, crosstabs the column names so that you get one row per index. Row counts are useful if you collect them periodically, nusi's are ~8 bytes per row.

select  di.databasename, di.tablename, di.indexname, di.indextype,
di.uniqueflag, max(di.columnposition) nbr_cols,
SUM(cast(t.currentperm as decimal(18,0))/(1024*1024)) perm_mb,
    100*((cast(max(t.currentperm ) as decimal(18,3)) -
    cast(ave(t.currentperm ) as decimal(18,3))))/cast(max(t.currentperm)
as decimal(18,3)) as tableskew,
max (case when columnposition = 1 then columnname else '' end)  col1,
max (case when columnposition = 2 then columnname else '' end)  col2,
max (case when columnposition = 3 then columnname else '' end)  col3,
max (case when columnposition = 4 then columnname else '' end)  col4,
max (case when columnposition = 5 then columnname else '' end)  col5,
max (case when columnposition = 6 then columnname else '' end)  col6,
max (case when columnposition = 7 then columnname else '' end)  col7,
max (case when columnposition = 8 then columnname else '' end)  col8,
max (case when columnposition = 9 then columnname else '' end)  col9
 from dbc.indices di, dbc.tablesize t
 where  --  di.databasename in ('live_data','temp_data') and  --
generally
want to see particular databases, not everything on the server
di.databasename = t.databasename and di.tablename = t.tablename
group by   di.databasename, di.tablename, di.indextype, di.indexname,
di.uniqueflag
order by    di.databasename, di.tablename, di.indextype, di.indexname


     
  <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