Index Coalesce vs Index Rebuild

Q:Does an Index Coalesce basically accomplish what an Index Rebuild Online?

A:

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.

Q:Is it faster? Slower? Lower-impact? Logged?

A:

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.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10714335/viewspace-441096/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10714335/viewspace-441096/