Re: Index Coalesce vs Index Rebuild

"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:40d9f563$0$18195$afc38c87_at_news.optusnet.com.au...
>
> "Charles Davis" <cdavis10717_at_comcast.net> wrote in message
> news:EdOdndaskMoSbUTdRVn-jw_at_comcast.com...
> > Re: Oracle 9.2:
> >
> > Does an Index Coalesce basically accomplish what an Index Rebuild Online
> > does?
>
> No. An offline index rebuild essentially does the following:
>
> 1. Lock the table
> 2. Create a new, temporary, index by reading against the contents of the
> existing index
> 3. Drops the original index
> 4. Renames the temporary index to make it seem to be the original index
> 5. Remove the table lock.
>
> An online index rebuild basically does this:
>
> 1. Lock the table
> 2. Create a new, temporary and empty, index and an IOT to store on-going
DML
> 3. Release the table lock
> 4. Populate the temporary index by reading against the contents of the
> existing index
> 5. Merge contents of the IOT in with the new index
> 6. Lock the table
> 7. Final merge from IOT and drop the original index
> 4. Renames the temporary index to make it seem to be the original index
> 5. Remove the table lock.
>
> And a coalesce does this:
>
> 1. Scan along the base of the index
> 2. Where adjacent nodes can be combined into a single node, do so
>
> That's it. There's no table locking. There's no new index created. There's
> no reduction in the size of the index, therefore. Just adjacent nodes
which
> are mostly-full of empty space merged into a single, well-filled node,
> leaving a totally empty node now available for fresh inserts.
>
> > Is it faster? Slower? Lower-impact? Logged?
>
> Yes, it's faster because it's not doing so much. Yes, it's lower-impact
> because there's no table locking (even an online rebuild takes exclusive
> table locks. Twice.) Yes, it's logged because you're modifying the
contents
> of the index blocks.
>
> > I have developers wanting to use this instead of a daily index rebuild
> > process each night.
>
> And, at the risk of starting World War III, why on Earth are you allowing
> your developers to even *consider* doing something as utterly stupid and
> nonsensical as a daily index rebuild? But if you have no real say in the
> matter, and you can't persuade your developers by reasoned argument that
> what they are doing is moronic, then yes, let them switch to doing reguar
> coalesces instead. At least that way they will think they are achieving
> something, but without actually doing much damage. A bit like putting a
> plastic kiddie's steering wheel behind the driver's seat so the children
can
> be kept safely amused during a long drive. Functionally pointless, but it
at
> least shuts them up.
>
> > Opinions are welcomed.
>
> Actually, what you should be more interested in are cold, hard facts. The
> facts about index rebuilds have been stated here many, many times: they
are
> almost always unnecessary. Fact, not opinion. Coalescing has a role to
play
> when a table is subject to patchy deletes that never quite clear out an
> entire index block, but always leave behind the odd index entry here and
> there. Otherwise, it's an exercise in fatuous futility. If you do bulk
> deletes that clear out index nodes completely, then a coalesce is a lot of
> I/O for nothing.
>
> Regards
> HJR
>
>

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.

So, I suppose I have no choice in 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, why would they absolutely need to have these indexes rebuilt daily? hmmm.

thanks.

charles Received on Wed Jun 23 2004 - 18:38:37 CDT