Re: Coalescing Indxes on a regular basis
- From: Phil Singer <psinger1@xxxxxxxxxxxxx>
- To: 'oracle-l' <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 12 Apr 2007 22:31:07 -0400
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]
--
//www.freelists.org/webpage/oracle-l
Other related posts:
