If you suspect that the data on the data pages is scattered over several data pages, you can modify the table to B-tree again. You can check this by retrieving the tids as well as the column values, and looking at the pages they reflect.

Remodifying sorts the data and builds the B-tree index, placing like keys on the same data pages, which can slightly reduce the number of disk I/Os required to access the data. For more information, see Tids on page 224.

This type of modification is especially useful when the key size is small, the row size is large, and the data has not been appended in sorted order. Remodifying a B-tree is also useful when you have deleted many rows and must reclaim disk space. For more information, see Tracking of Used and Free Pages on page 399.

The first example represents the table before modification, and the second example shows it after modification.

The following retrieval touches all three data pages before modification but only one page after modification:

The following table shows the leaf and data pages prior to modification. The records with a key of 35 are found on several data pages:

The following example modifies the emp table, re-specifying B-tree as its structure:

After you perform this modification, the table looks as follows. All records with a key of 35 are clustered together on Page 2: