Re: rebuild index?

Rules of thumb:
- when the pct_used of an index falls below 80 or 70 percent, where it was initially 89 (seems to be the max with a 2k block size). This is from experience. In the particular case I have in mind, this also meant the index segment was extending, which we didn't want, because we simply didn't have the space.
- when there is significant empty space in the segment. The value blocks in index stats is what has been allocated to the segment, btree_space of course is always lower, and used_space is still lower. Note: pct_used is nothing more than used_space/btree_space, it does not relate to blocks. - when there is a significant number of deleted rows - when the height of the index has been increased by 1. A height of 3 is my usual maximum, 4 and higher shouldn't occur. And of course keep in mind, there are situations where pct_used is low by design, because the space simply isn't necessary.

Hth,

Sybrand Bakker, Oracle DBA

David Spaisman wrote in message <376715C1.73637DA1_at_intercall.net>...
>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 - 23:33:44 CDT