Re: Analyzing indexes

From: Chuck <chuckh_at_softhome.net>
Date: 28 Mar 2003 13:39:51 GMT
Message-ID: <Xns934C5822F7E4Cchuckhsofthomenet@130.133.1.4>

Connor McDonald <connor_mcdonald_at_yahoo.com> wrote in news:3E810E13.3F79_at_yahoo.com:

> Chuck wrote:

>> 
>> "Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in
>> news:pan.2003.03.11.19.53.03.636123_at_yahoo.com.au:
>> 
>> > On Tue, 11 Mar 2003 14:38:05 +0000, Chuck wrote:
>> >
>> >> I need to determine whether or not a couple of indexes need to be
>> >> rebuilt.
>> >
>> > They almost certainly won't be!
>> >
>> > What makes you think they might need it? If this is an index on a
>> > monotonically incrementing sequence number, and you've been doing
>> > sporadic deletes of table records with low sequence numbers, then
>> > yes, you might have a need.
>> 
>> Just the fact that I reorged them once last summer and saw dramatic
>> performance increases on queries involving the indexes in question.
>> That was the first reorg in 18 months. These are indexes on
>> sequential numbers and yes we bulk purge older rows (ie. lower
>> sequence #'s) from the indexed table.

>
> Assuming the blocks from the older purged rows are totally freed up
> (ie you're are not leaving sporadic old rows around) then these blocks
> will be reused for the subsequent high value sequence numbers thus
> avoiding the rebuild requirement.
>
> Whilst rebuilding might improve query speed by "packing" the blocks
> better, you might also find it has a detrimental effect on dml
> performance and possibly an increased drain on your redo.
>
> Also, you might like to consider using coalesce.
>
> hth
> connor

We don't purge all old records. Just those that are 2 years old and meet a few other criteria. About 5% of them hang around forever.

We do periodically coalesce as this can be done online. Received on Fri Mar 28 2003 - 07:39:51 CST