Re: How to calculate the size of an index?

stephen.howard_at_us.pwcglobal.com (Steve Howard) wrote in message news:<6d8b7216.0401121254.7f99fd2_at_posting.google.com>...
> jeffyee_at_hotmail.com (Jeffrey Yee) wrote in message news:<ec4cec95.0401120502.6f4b273_at_posting.google.com>...
> > Dear all,
> >
> > To calculate the size of a table, I usually use num_rows *
> > avg_row_len. But how to I calculate the size of an index? Do I use
> > avg_data_blocks_per_key * distinct_key * db_block_size of the
> > database?
> >
> > Please advice. Thank you.
> >
> > Best Regards,
> > Jeffrey Yee
>
> Look up "ANALYZE INDEX...VALIDATE STRUTCURE" in the docs.
>
> HTH,
>
> Steve

The reason I need to calculate the size of the tables & indexes is because the number of extents allocated is more than 100000 extents. I thought of exporting this data out, drop the tables, and than recreating the tables with the proper initial, next and pct_increase size). To do this, I need the actual size used by the tables and indexes. For yor information, these tables contain static data.

The reason I don't use dba_segments because it tells me how much space has been allocated, but not the actual space used.

Therefore, can you please advice if I can rely on the num_rows * avg_row_len to calcualte the size of the tables?

I will look up "ANALYZE INDEX...VALIDATE STRUTCURE" in a minute.

Thank you for the ideas. Received on Mon Jan 12 2004 - 21:23:47 CST