Should I rebuild or coalesce my indexes ?

Should I rebuild or coalesce my indexes ?



My last 2 notes on this blogs were specific to rebuilding (when and to which) indexes.

There is one more critical aspect which I wanted to discuss is the cost/effort related to rebuilding indexes.

Rebuilding an index is quite a costly operation and you must evaluate the benefit Vs effort before rebuilding an index.

Rebuilding (online) an index requires additional resources like space, cpu usage, time.

Here is one more option, which is less used or probably less popular “coalesce”.

Rebuild Vs Coalesce


Rebuild

• Can move an index to a different tablespace


• Resource consuming process


• Takes more time


• Creates a new tree


• Shortens the height of an index if it was increased due to DML activities


• Rebuilding can affect future DML’s because index becomes compact and for future DML’s index has to be extend dynamically.

Coalesce

• Cannot move an index to a different tablespace


• Comparatively less resource consuming


• Takes relatively less time


• Only merge the adjacent free space between leaf blocks within a branch


• Doesn’t shorten height of index


• Since coalesce doesn’t effect the total size and only frees up the unused space, it doesn’t affect future DML’s

Image Source: Oracle doc
Coalescing the index, frees up space of adjacent leaf blocks within a branch block. This way the number of blocks or extents which an index is using will not change but there will be more number of free blocks which can be used for future inserts or updates.

In an OLTP environment, where data is highly volatile, coalesce is better because it doesn’t shrink the index and the free space remains with the index segment.