RE: Coalescing Indxes on a regular basis

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of fmhabash_at_gmail.com
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?

--

http://www.freelists.org/webpage/oracle-l Received on Tue Apr 10 2007 - 12:15:59 CDT