What are the differences between 'shrink space compact' and 'coalesce'?

Question

The oracle documentation says that during altering an index clauses shrink space compact and coalesce are quite similar and could be replaced by each other, but Tom found some differences in the behavior.

Since coalesce is not available in standart edition of Oracle Database, I suppose there're some benefits in using it.

So, what are the differences? Can I perform shrink space compact on a dynamically changing index?

Answer 1

The above answer is false. There are basically 4 options.

1 - ALTER INDEX COALESCE

2 - ALTER INDEX SHRINK SPACE

3 - ALTER INDEX SHRINK SPACE COMPACT

4 - ALTER INDEX REBUILD

Options 1 and 3 do NOT free up blocks. They just free up space in existing blocks. Coalesce does a little bit worse job, there will be more blocks with only 25-50% free space, while with shrink space compact, there will be more blocks with 75-100% free space. The total number of blocks, however, stay the same. For example, an index with 200 blocks with coalesce, and after deleting 1/5 of the rows randomly, will have ~1/5 of the index blocks have 25-50% free space while the rest remain full.

On the other hand, shrink space and rebuild do free up the blocks, and merge them into existing ones, thus reducing the total number of blocks. I think the only difference is speed. When you delete only 5% from a large table, there's no reason to rebuild the entire index, and it will be very slow. However, shrink space might be a little bit faster here, because it does not rebuild the entire index, just reorganizes the blocks.

Obviously the fastest choice would be coalescing or shrinking space with compact option.

Answer 2

First of all, indexes generally do not need to be frequently rebuilt. They generally grow to a steady size and stay there, and rebuilding them produces only a temporary benefit to queries that is then counterbalanced by increased load in modifying them due to an increased rate of block splits. So don't forget that the best optimisation for a process is to eliminate it completely -- if you think you have a need for frequent rebuilds then post a question and maybe the cause can be explained and a different approach be found.

Anyway, coalesce reduces the number of blocks that are holding index data, thus freeing up blocks completely so that they can be re-used for new index entries. The freed blocks are still allocated to the index, though. This can prevent indexes from growing too large.

Shrink does something similar but moves the populated blocks to allow freed blocks at the "end" of the index segment to be deallocated from it. Thus the index segment actually gets smaller. This requires an exclusive lock on the table.