Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Index Coalesce vs Index Rebuild

Re: Index Coalesce vs Index Rebuild

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Thu, 24 Jun 2004 10:44:59 +1000
Message-ID: <40da23ef$0$18671$afc38c87@news.optusnet.com.au>

"Charles Davis" <cdavis10717_at_comcast.net> wrote in message news:iredneFJ8JzuiUfd4p2dnA_at_comcast.com...

> Thank you, Mr Rogers, I appreciate you taking the time to reply.
>
> I inherited an SAP database that had a daily index rebuild job for 9
tables
> that are high insert/deletes each day. Users claim that if the index
> rebuilds are not finished each day then the performance is terrible.

Yup, that about figures. I've had people using SAP doing this sort of thing, and Peoplesoft. It seems to be a 'feature' of these sorts of applications.

> So, I suppose I have no choice in the matter.

Well, the DBA always has a choice: accept the myth, or test it. Some performance tuning metrics before a rebuild and after it would resolve the matter.

> Thanks for the info, I will work it into my reply to the developers about
> this change they want to make.
>
> So, given that you've stated that indexes do not need to be rebuilt,

Not quite what I said, which was actually that "index rebuilds are almost always unnecessary". There's a qualification in there which is important: Almost.

> why
> would they absolutely need to have these indexes rebuilt daily? hmmm.

As I said, the qualification I made is important. It is possible that you happen to be cursed with an application that really does muck around with its tables such that indexes only ever grow, with lots of un-re-useable space in the 'trailing edge' of the index. Possible, but not likely. The real reason why people claim they "absolutely need" daily rebuilds is that they don't understand how Oracle indexes work; the myth that they need rebuilding has been around for a long time; it looks like 'real DBA work is being done'; and it therefore has the same effect as a placebo would have in a medical trial: it makes people *feel* that performance is OK, even when it is substantially no different.

As I said, the only thing you can really do is to measure it, in cold, hard numbers. The other thing I have been known to do is to tell people that something has been implemented or performed when it hasn't been. If they quickly come back complaining, there's a fair chance the measure really is required. If they don't, then the placebo effect is at work. Coalescing is actually not a bad idea for this sort of hanky-panky, because it quite conceivably does nothing very much for an index (for example, suppose a sequence of leaf nodes are 100% 10% 100% 24% 100% 56% full. A coalesce will leave your index as: 100% 10% 100% 24% 100% 56% full, because nothing adjacent to anything else can be combined into a single leaf node). But it certainly makes it *look* as if something is being done, and people will quite often report that performance is better afterwards!

Regards
HJR Received on Wed Jun 23 2004 - 19:44:59 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US