Re: Coalescing Indxes on a regular basis

From: Phil Singer <psinger1_at_chartermi.net>
Date: Wed, 11 Apr 2007 21:26:58 -0400
Message-ID: <461EEB6B.1070508@chartermi.net>

Mark W. Farnham wrote:
> In most releases still being used Oracle does a pretty good job of avoiding
> large tracts of lost space in index structures. So routinely coalescing is
> an expenditure of horsepower you would want to carefully justify on an index
> by index basis.
>
> A non-exhaustive list of conditions that *might* add up to a justification
> of periodic index "maintenance" (you might determine that an online rebuild
> is superior in various operational cases than a coalesce):
>
> 1) If the cycle of inserts and deletes routinely pushes some index to a
> deeper blevel if it is left for two months where once a month maintenance
> keeps it one level less, (Or some other not too frequent period compared to
> some other not quite as frequent period that gets you deeper) and heavily
> used queries actually perform significantly better with one less level.
>
> 2) Permanent large reduction in number of rows that still remain scattered
> with respect to some index so that the index is now much larger than it
> needs to be. Less justification is required for things that happen "once."
>
> Hmm, I'm out of steam on justifications.

Actually, I have a third, but it really must be implemented on a shop-by-shop basis. If your shop

a) insists on doing charge-back accounting for everything, and
b) wants to use totally objective billing statistics and
c) can't find anything to use besides disk space allocations and
d) thus charges back something like $1000/G/Month (US) so that
e) There is a strong motivation to use as little disk as absolutely 

possible
Then it would be profitable to rebuild your indexes as often as possible.

[and yes, I once found myself in such a spot]

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Apr 11 2007 - 20:26:58 CDT