Re: How to know when an index needs rebuild ?

From: Howard J. Rogers <howardjr2000_at_yahoo.com.au>
Date: Sun, 28 Sep 2003 12:55:32 +1000
Message-ID: <3f764e6a$0$14559$afc38c87@news.optusnet.com.au>

Mr. DB wrote:

> "Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message
> news:<3f74c5c5$0$15134$afc38c87_at_news.optusnet.com.au>...

>> Ryan Gaffuri wrote:
>> 
>> > Connor McDonald <connor_mcdonald_at_yahoo.com> wrote in message
>> > news:<3F7411E0.4B2B_at_yahoo.com>...
>> >> Mark wrote:
>> >> > 
>> >> > Hi all dba users,
>> >> > I'd like to know if there's some good sql script / indicator to
>> >> > detect if an index need to be rebuilt.
>> >> > Thanks a lot
>> >> > Mark
>> >> 
>> >> Don't.
>> >> 
>> >> Or use coalesce
>> >> 
>> >> hth
>> >> connor
>> > 
>> > isnt there something to look for in index_stats? I have found that if
>> > we do alot of deletes and rebuild it there is a big speed increase(yes
>> > I know creating the table or doing a 'move' is import too).
>> 
>> Try reading other posts in the thread. I answered you about index_stats.
>> 
>> But when Connor tells you 'don't', that's something worth listening to.
>> 
>> Regards
>> HJR

>
>
> Anurag posted a rather lengthy message that showed a case where the
> index should be rebuilt rather than coalesced. The rebuild took 1
> minute, whereas teh coalesce took 40 minutes. The coalesce won't
> entirely defragment the index in some severe circumstances. In this
> case, the question that needs to be asked is whether you have the
> right type of index, or whether it is configured properly. If you
> rebuild a 400MB index and it is 10MB when you're done, you need to
> start looking for something that will address the root cause.

I agree.

When people read a one-word post "don't" utterly literally, we're in real trouble here. It should be read (as I'm sure Connor meant it to be read) as "before you do anything, back off, and think about it", not a literal injunction never, ever, under any circumstances, to rebuild an index. His post (and mine) mean: stop, think. The balance of the index rebuild argument is *very*, *very* heavily weighted away from rebuilding... but that does not mean you can't come up with an example or three where a rebuild would indeed be the appropriate course of action.

Both Connor and myself (and others, such as Anurag, and Richard Foote) are fighting an entrenched culture which says that periodic rebuilds are OK, for all indexes, without qualification. You should read one-liners saying "don't" in that context.

If people can't do that simple bit of comprehension and interpolation, then we might aswell all post replies which say "it depends" and have done with it.

But that would be (and usually is when it rears its head here) a meaningless response: Of course it sodding well depends!! But on what, and what are the balances in making the judgement?

Put it this way: you will do far more harm if you take 'periodically rebuild' literally than if you take 'don't rebuild at all, ever' literally. The balance is most definitely on the 'don't rebuild' side of the equation. But enough caveats were put in my original reply to the original poster to indicate that, occasionally, under particular circumstances, a rebuild might be in order... so one hopes that people take the 'never, ever' short-form in that spirit.

Regards
HJR Received on Sat Sep 27 2003 - 21:55:32 CDT