Archives of the TeradataForum

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

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

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