Re: rebuild index?

Jonathan:

So aside from the browning factor and reaching close to max extents, when do I rebuild an index? If pct_used is sometimes sparse at one end and at the other end of the tree and if blks_gets_per _access is only an estimate, what else do I use to tell that the index needs to be rebuilt? When do you recreate your indices?

Thanks.

David Spaisman
Jonathan Lewis wrote:

> Sorry, talking rubbish there. The note below
> describes the avg_leaf_blocks_per_key from
> the XXX_INDEXES view.
>
> The block_gets_per_access column in
> INDEX_STATS also relates to the number
> of rows with a given value, but is an estimate
> of the number of logical I/Os required to get
> a given row from the table using that index,
> and I think it tends to work out as
> (average rows per key / 2) plus a bit
>
> So it still is not an indicator of whether
> or not the index should be rebuilt.
>
> --
>
> Jonathan Lewis
> Yet another Oracle-related web site: www.jlcomp.demon.co.uk
>
> Jonathan Lewis wrote in message
> <929471723.28362.0.nnrp-07.9e984b29_at_news.demon.co.uk>...
> >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.
Received on Tue Jun 15 1999 - 22:10:57 CDT