jump to navigation

ALTER INDEX COALESCE: 10g Improvements (Jump They Say) February 5, 2008

Posted by Richard Foote in Index Coalesce, Oracle General, Oracle Indexes, Performance Tuning.

I thought it might be worth mentioning some interesting changes in the manner in which the ALTER INDEX … COALESCE command works since Oracle 10g.

Basically the purpose of the COALESCE option is to reduce free space within the Leaf Blocks of an index. This is achieved by effectively performing a Full Index Scan of the leaf blocks, comparing the free space available in neighbouring blocks. In 9i, the basic method was to logically start with the left most leaf block and see if it could be coalesced or merged with the 2nd left most block. This required the sum of used space within these 2 blocks to be less than 100% of a block less the PCTFREE value. If so, the contents were merged with the contents of one block placed in the other and with the now empty leaf block removed from the index structure and placed on the index freelist.

It then looked at the 2nd leaf block  (which might now be the first block if previously coalesced) and 3rd leaf blocks to see if these could be coalesced. If so they were merged and the empty block placed on the freelist.

And so on and so on until all leaf blocks had been traversed and all possible leaf blocks coalesced.

Note branch blocks are not directly merged during this process, except to be updated with modified pointer information if a leaf block coalesce had taken place. However, if enough leaf blocks are removed such that the branch block contains no more pointers to leaf blocks (or other intermediate branch blocks), it’s also removed from the index structure. However, there must always be at least one branch block from each level remaining hence the height of an index always remains the same during a coalesce operation.

Note if no leaf block had 50% or more free space, nothing would be coalesced as no two consecutive leaf blocks would have sufficient free space in which to be coalesced.

In 10g, the Coalesce operation has been modified somewhat.

An index no longer requires the sum of used space plus PCTFREE in adjacent blocks to be less than 100% of a block be effectively coalesced. For example, the free space in a block can be 25% in one leaf block and just 25% in the adjacent block (hence the combined used space alone being 150% of a block) and 10g can effectively coalesce these leaf blocks together.

This demo show how Coalesce differs between a 9i ( and a 10g ( database.

10g introduced the concept of being able to SHRINK an index and the Coalesce option can be viewed as now being very similar to an index Shrink command. Similar but not quite the same.

I’ll cover the similarities and differences between a Coalesce and a Shrink in the next day or two …


1. Christian Antognini - February 6, 2008

Hi Richard

> Note the branch blocks are ignored during this process,
> except to be updated with modified pointer information
> if a leaf block coalesce had taken place. Therefore the
> height of an index always remains the same during a
> coalesce operation.

Mhmm… But if the branch blocks, because of the merges, become empty, they are removed from the tree. As you wrote, the height doesn’t change, but the root block might become almost empty. In extreme cases (most of the rows deleted) I observed root blocks (at level 2) with a single branch block (at level 1).

Best regards,


2. Richard Foote - February 6, 2008

Hi Christian

Yes, that part was worded very poorly and you are absolutely correct, fully emptied branch blocks are removed from the index structure in extreme cases.

I’ve tried to re-word the post somewhat more accurately.

Many thanks for the feedback and correction 🙂


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: