Re: Estimating index sizes

Brian,

You can use the Analyze command on indexes as well:

Analyze emp_idx validate structure

This puts a row into the index_stats table. Some of the useful columns in that table are:

btree_space (space allocated to the index) used_space (space being used)
pct_used (percent of allocated space used) lf_rows (number of values curently in the index) lf_rows_len (sum in bytes of the length of all the values) del_lf_rows (number of values deleted from index) del_lf_rows_len (sum in bytes of all deleted rows)

Divide lf_rows_len by lf_rows to get average index length.

According to the Oracle manual if the deleted entries are more than 20% of the current entries, it's probably a good idea to rebuild the indexes. To compute this number:
Select (del_lf_rows/lf_rows_len) * 100 from index_stats;

Hope this helps. Rob Received on Fri Dec 12 1997 - 00:00:00 CST