Re: Index Coalesce vs Index Rebuild

"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
8. Renames the temporary index to make it seem to be the original index
9. 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 Received on Wed Jun 23 2004 - 16:26:21 CDT