Re: rebuild index?

The pct_used is the most useful indicator, although even this can be somewhat misleading if the index is (say) very sparse at one end and very well packed at the other.

The Blk_gets_per_access is not necessarily a good indicator - for a unique index bgpa will be height of index + 1, for a non-unique key bgpa will have an extra factor which relates to number of rows per key value and number of (expected) index entries per index block.

So an index with about 100 entries per key value, but only expected 20 index entries per block (i.e. key size is about 100 bytes) would have a bgpa of around 5 + 3 if the index were very well packed, and 10 + 3 if the index was running at 50% efficiency.

--

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

David Spaisman wrote in message
<7k647r$a3d$1_at_mailint03.im.hou.compaq.com>...
>Hello:
>
>I am looking into what factors require me to rebuild the indexes on my
>databases for Oracle 8.0.4 on NT 4. Aside from too small extents and
>'browning'(where deletes leave too many holes in the b_tree structure), I
>have come across an interesting statistic.
>
>For the analyze valaidate structure process on indexes the index_stats
table
>has two columns whose values determine if a particular index needs to be
>rebuild:
>
>1) pct_used < 50%
>2) blks_gets_per_access > 5.
>
>Has any one come across these statistics? Are they correct?
>
>Are there any other factors to determine when to rebuild indexes?
>
>Any information that can be provided will be greatly appreciated. THanks.
>
>David Spaisman
>
>
Received on Tue Jun 15 1999 - 13:29:59 CDT