Re: Question about Index (Rebuild or not )

The ratio you describe gives some indication (on a new index) of the relative use of branch blocks and leaf blocks.

The PCT_USED figure in index_stats is a measure of how many leaf and branch rows the index could hold (given the number of blocks actually used) compared to the number it actually holds. If there are no deleted row reported, this is the figure you want.

If there are deleted rows reported they will have been counted twice, once because they exist, and once because they are deleted.

In this case you need to work out:

    100 * (br_rows_len + lf_rows_len - del_lf_rows_len) / btree_space

Remember too that the figure of 0.7 is just a rule of thumb, and need not apply in all cases. Your data may result in a different characteristic index use.

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

assoy_at_my-deja.com wrote in message <7th009$qvi$1_at_nnrp1.deja.com>...
>
>
>I have read somewhere (about when an index need to be drop and rebuild
>).
>It said that I have to run ANALYZE INDEX command to validate Structure,
>then
>calculate the ratio of LF_BLK_LEN/(LF_BLK_LEN+BR_BLK_LEN). If it is not
>greater that 0.7 then the ratio should be rebuild. It also said that if
>the ratio of LF_BLK_LEN/(LF_BLK_LEN+BR_BLK_LEN) is nearing 0.3, the
>index
>definitely need to be drop and recreate or Rebuild.
Received on Thu Oct 07 1999 - 02:45:48 CDT