To maintain good concurrency and performance, the B-tree index is not rebuilt after deletions. Deletions occur at the leaf and data page level, but an empty leaf page is not released. If your environment is one where many deletions are performed, you must occasionally update the index

In VDBA, you do this using the Shrink B-tree Index option in the Modify Table Structure and Modify Index Structure dialogs.

In SQL, you accomplish this task with the MODIFY statement. The TO MERGE clause is the same as the Shrink B-tree Index option. For more information, see the SQL Reference Guide.

The Shrink B-tree Index option is also important for users with incremental keys, which can incur lopsided indexes after heavy appends to the end of the table.

Not updating the index to reflect unused leaf pages can cause the index to be larger than necessary.

For example, if the emp table is keyed on empno (ranging from 1 to 31), and you fire all employees with employee numbers less than 16, the B-tree index does not shrink, but is unbalanced. This is shown in the following "Before" diagram:

To re-balance the index level, you can use the Shrink B-tree Index option. It also reclaims unused leaf pages that otherwise are never reused. This is shown in the following "After" diagram:

The index is rebuilt, and empty leaf pages are marked as free, but otherwise leaf and data pages remain untouched. Therefore, this procedure is neither as time-consuming nor as disk-space intensive as modifying the table structure using the Change Storage Structure option. Shrink B-tree Index, however, does not re-sort the data on the data pages. Modifying the structure to B-tree is the only option for resorting data on data pages.