Re: Index management

Brian Peasland wrote:

> On this last point, you and I agree, sort of.

And it's a point Mike just made up. No-one has ever accused him of advocating rebuilding all indexes.

> I don't say rebuild
> indexes just because of some magic number. And I don't say to never
> rebuild indexes. There are times to rebuild indexes and times not to.
> But for me, those times are more dictated by the expected flow of data,
> not by some magic number. If an index has a tendancy to get one-sided
> (normally due to the use of a monotic sequence as the value to the
> indexed column followed by deletions of "older" values) then rebuilding
> periodically can help.

See, this is where that paper you wrote is wrong, too, IIRC. If the index has got "lop-sided" because you deleted a lot of older values, then you now have a lot of empty leaf nodes on the left-hand edge of your index, and those empty blocks can now receive the next inserts from your monotonically incrementing sequence number. Which means that a rebuild is *not* warranted.

If you have performed *scattered* deletes, different story. There are always exceptions, after all.

> And if I delete massive amounts of data and I
> don't expect to add much more data in the future, then rebuilding can
> help.

Well, I'd suggest that if you'd done that to your table, you are going to get a much better performance improvement from re-compacting your *table* (alter table X move), because you are now suffering from High Water Mark Inflation Syndrome. Of course, after a table move, an index must be rebuilt for it to be usable. And we could discuss, forever I suspect, how to disentangle the effects of both operations on performance. But I think anyone who would carefully rebuild their index, but happily leave their table full of fresh air, has lost the plot.

In which case, we are *still* not talking about just rolling out 'alter index X rebuild' commands.

>And sometimes rebuilding an index can help in one area and hurt in
> others, but the one area that I am fixing is more important to me than
> the other areas.

That's getting rather vague (which I think is really your point: these things are vague and subtle and no one number is going to give you the green light or red). But I'll buy into it. There are always the odd exceptions.

> But in none of these circumstances can I arrive at any
> ratio which defines the need to rebuild or not. There is simply no query
> you can apply the index and table metrics to arrive at these
> conclusions.

The starting point for any discussion you may have about the need to rebuild an index should be, I think, that Tom Kyte has rebuilt 7 of them in his entire professional career.

And I don't mean he just got unlucky with the allocation of keyboard duties over time, but that he positively and actively decided to rebuild only 7.

Your justification for rebuilding an index when taken in that light had better be pretty good, I think.

Regards
HJR Received on Mon May 10 2004 - 16:00:58 CDT