Shrinking a B-tree Index
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:
Before
<=16 >16
/ \
<=8 >8 <=24 >24
/ \ / \
<=4 >4 <=12 >12 <=20 >20 <=28 >28
Page 1 Page 2 Page 3 Page 4
(deleted (deleted valid valid
data) data) data data
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:
Last modified date: 01/30/2023