jump to navigation

Differences and Similarities Between Index Coalesce and Shrink Space February 6, 2008

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

As already discussed, ALTER INDEX COALESCE in 10g onwards works in a very similar manner to ALTER INDEX SHRINK SPACE.

However, there are a number of key differences.

The first thing to point out is that each command has a slightly different purpose.

Coalesce is designed specifically to reduce fragmentation within an index but not to deallocate any freed up blocks which are placed on the freelist and recycled by subsequent block splits.

Shrink is designed specifically to reduce the overall size of an index segment, resetting the High Water Mark (HWM) and releasing any excess storage as necessary.

The key difference being that Shrink must reorganise the index leaf blocks in such a way that all the freed up, now empty blocks are all grouped together at “one end” of the index segment. All these blocks can then be deallocated and removed from the index segment. This means that specific leaf block entries must be removed from these specific blocks, in order to free up the leaf blocks in this manner.

Although Coalesce in 10g performs the operation in a similar manner to that of the Shrink Space, it can be more “lazy” in how it deals with the subsequent empty blocks and places then on the segment freelist as necessary.

COALESCE and SHRINK SPACE COMPACT are logically equivalent commands. Both options will “defragment” an index by “merging” index entries where possible thus reducing the number of blocks within the logical index structure. Both will result in the same number of leaf blocks within the index and both will result in the index height not being changed.

However, there are two key differences.

1) The SHRINK SPACE COMPACT option has the disadvantage of being more expensive to process as it has to concern itself with ensuring all necessary blocks can be emptied from the physical “end” of the index segment to be subsequently deallocated. This will result in more undo and redo being generated during the defragmentation of the index than would have been generated by the same corresponding COALESCE command.

2) The SHRINK SPACE COMPACT option has the advantage of being able to immediately deallocate the empty blocks, thereby reducing the actual size of the index segment by issuing a subsequent SHRINK SPACE option (although of course this can be performed in the one step by issuing SHRINK SPACE in the first place). However, the COALESCE option will not be able to just deallocate the free space. A subsequent Index SHRINK SPACE command on a previously coalesced index will require additional undo and redo than that of a previously “Shrunk” index as the necessary empty blocks are removed from the freelist and redistributed to allow for the de-allocation of blocks and the resetting of the High Water Mark of the index segment.

Note also that the Shrink option can only be used in Automatic Segment Space Management (ASSM) tablespaces.

Use Coalesce when the intent is to just defragment an index, knowing that the freed leaf blocks will be recycled by subsequent block splits, as it uses less resources than an equivalent Index Shrink Space.

Use Shrink Space when the intent is to reduce the actual storage allocated to an index, for example in the scenario where a table has permanently reduced its size and the index is unlikely to reuse the freed storage.

This demo highlights the Differences (and similarities) between an Index Coalesce and an Index Shrink Space.

Note however, that an index REBUILD might actually use substantially less resources than either a Coalesce or a Shrink Space and might reduce the height of an index as well.

But that’s a discussion for another day …


1. Robert - February 7, 2008


thanks for the detailed explanation! Am I right that REBUILD is likely to be more efficient if a large portion of the data has been release recently? In that case a tablescan during reconstruction might be less expensive than an index walk.




2. Richard Foote - February 7, 2008

Hi Robert

I think you might be onto something there 😉

Hopefully tomorrow I’ll have time to follow it all up.


3. abrams wee - December 8, 2011

hi Robert,

thanks for the clarifications. much appreciated.



4. stalinsksk - July 13, 2013

Hi Richard, Very nice detailed explanation. I have a quick question. We have a scenario where we are archiving/deleting rows from table(s) that are older than 3 months. Wondering if completely emptied leaf blocks will be given back to freelist for future block splits post delete operation, assuming the index is on sequence based key.



Richard Foote - July 15, 2013

Hi Stalin

Yes, they will be. This is something I’ve discussed previously 🙂



stalinsksk - July 15, 2013

Thanks Richard.

Your last comment on that blog post makes me curious what issues one would encounter. Is there another blog post you have discussed about. In my case, since we are doing data archival after long time, number of block in freelist is going to more than daily insert demand. Appreciate your input.

“However, these empty index blocks can potentially be problematic and can cause performance issues until eventually they actually get reused and recycled.

But that’s a topic for another day.”


5. Richard Foote - July 19, 2013

Hi Stalin

Yes, I discuss it in the next Part V of that particular series:


Basically, until the index blocks get reused, they can get in the way of subsequent index range scans if you happen to access that portion of the index, such as in my MIN example.


stalinsksk - July 20, 2013

Thanks Richard. That makes sense.

Appreciate your insights and sharing the same to the community.



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 )

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: