The mechanism Oracle actually uses to track the free space in an extent is the freelist.
Each table has one or more freelists, which contain a list of all free data blocks in the current extent.
If a data block is filled to the maximum space allowed, it is removed from the freelist.
If the space used by a data block drops below the percentage specified in the PCTUSED parameter, the block is placed back onto the freelist.
You can specify more than one freelist for a table. Because Oracle must check the freelist for each insert into a table, you can have multiple freelists if your usage scenario calls for frequent simultaneous inserts.
Multiple freelists can avoid any potential contention for the freelist contents.
The coalesce command tells the database to merge the contents of the index blocks to free blocks for reuse later, where it is possible to do so.
Here is an example:
SQL> alter index test_idx1 coalesce;
Index altered.
SQL>
Coalescing an index does not release space back to the database. The purpose of the coalesce command is to reduce fragmentation in an index.
It does not deallocate space that has been allocated to an index segment. Coalescing an index performs an in-place reorganization of the index data. It combines
adjacent leaf blocks into a single leaf block and puts the newly empty leaf blocks on the free list of the index segment.
The freed up index leaf blocks are reused by the database during subsequent block splits.
The goal here is to reduce the free space within the
leaf blocks of an index. The
database scans the index leaf blocks to compare the free space in neighboring index blocks.
If there is free space in a block, the contents of the block are merged with the contents of another block, thus freeing up index leaf blocks where possible.
The database removes any freed index blocks from the index structure and places them on the free list of index blocks.
Coalescing an index keeps the space you allocated for the index intact; it does not return the unused space to the database.
If you have a case where you are dealing with an index with monotonically increasing values such as on a sequence or a date and you delete a lot of the old values, coalescing might
be helpful. Many shops regularly purge older data based on the sequence number or a data range.
Coalescing indexes in such cases helps you reclaim the unused space, which is not going to be reused by the indexes anyway.
If you are performing a select of all the rows in a table with such an index and are ordering the results by the indexed column, the database will have to read the mostly
empty index leaf blocks.
Queries might perform better when you coalesce such an index. Coalescing rather than rebuilding the index is the right action to take here.
Unlike in the case of an index rebuild, coalescing an index does not require additional disk space; rebuilding an index requires space both for the original and
the new index structures until the index is rebuilt. Coalesce also runs much faster than an index rebuild
in most cases, freeing up unused leaf blocks for reuse.