REM Script to size an index based on the Table Name , Index Name and Number of Columns

set echo off
column table_name for a20 heading "Table Name"
column index_name for a20 heading "Index Name"
column col_length for 9999 heading "Column|Length"
column num_rows for 99999999 heading "No of|Rows"
column size_in_kb for 999999.99 heading "Size|(in Kb)"
column size_in_mb for 999999.99 heading "Size|(in Mb)"
spool descre_ind_size.log

select a.table_name,
a.index_name,
sum(a.column_length) col_length,
&&num_rows,
((&&num_rows*sum(a.column_length))+8)/1024 size_in_kb,
((&&num_rows*sum(a.column_length))+8)/(1024*1024) size_in_mb
from all_ind_columns a,
all_tables b
where a.table_name=b.table_name
and a.table_name=upper('&&tab_name')
and a.index_name=upper('&&ind_name')
group by a.index_name,a.table_name;

exit