RE: Coalescing Indxes on a regular basis
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <fmhabash@xxxxxxxxx>, "'oracle-l'" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 10 Apr 2007 13:15:59 -0400
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. There certainly might be some more
but most folks look with a pretty jaundiced eye towards reorganization
efforts done without justification these days, especially as more folks move
toward more global schedules that make maintenance window time more
precious. Even if you have pure idle non-production time you have to justify
other things like disturbing your steady state cache population, driven by
user usage rather than prospective optimization.
Remember too that heavily updated tables may or may not involve heavy
updates to one or more indexes. So metrics supporting the cost of the
rebuild really need to be considered index by index rather than table by
table.
None of this is to suggest that no gain can be made from rebuilding and/or
coalescing some particular index, and there are some papers kicking around
that help assess whether you'll get a boost from rebuilding a particular
index. But I'm trying to focus on the notion you ask being regular periodic
maintenance rather than a one time or infrequent special event.
Regards,
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of fmhabash@xxxxxxxxx
Sent: Tuesday, April 10, 2007 11:40 AM
To: oracle-l
Subject: Coalescing Indxes on a regular basis
Does any implement this type of maintenance on indexes regularly?
Theoretically, this should be good practice on tables that witness large
number of updates or deletes.
For those who do not, can you please explain why?
--
//www.freelists.org/webpage/oracle-l
Other related posts:
