-
Is there a formula to calculate the approximate size of the index for a table like --
(sum of storage bytes of the columns forming index) * number_of_rows
I need to create an index on a large existing table and am trying to figure out the storage parameters.
Thanks,
- Rajeev
-
select 'ALTER INDEX ' || rtrim(substr(segment_name,1,length(segment_name))) ||
' REBUILD TABLESPACE tablespace_name STORAGE (INITIAL ', trunc(bytes / 1024 / 1024) + 1 || 'M NEXT 1M PCTINCREASE 0) ONLINE ;'
from dba_segments
where extents > 5
and segment_type = 'INDEX'
and owner = user
/
the above sql will work for in 8i.For all other versions pls remove the last word online.
after rebuliding the index do an analyze of the index.
hope this helps.
-
There is an excellent section in Appendix A of the Oracle8 Administrator's Guide that discusses appropriately estimating space requirements for tables, indexes and clusters.
Joe Ramsey