Remodifying B-tree Tables
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 30.
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 5.
Examples: Remodifying a Table to B-tree
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:
select * from emp where emp.age = 35;
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:
Leaf Page
key page,row (tid)
35 1,2 (514)
35 2,2 (1026)
35 3,3 (1539)
36 2,3 (1027)
37 3,2 (1538)
Data Pages
Page 1 Page 2 Page 3
1,1 (513) 29 2,1 (1025) 29 3,1 (1537) 30
1,2 (514) 35 2,2 (1026) 35 3,2 (1538) 37
1,3 (515) 30 2,3 (1027) 36 3,3 (1539) 35
The following example modifies the emp table, re-specifying B-tree as its structure:
modify to btree on age;
In VDBA:
1. Open the Structure of Table dialog for the table.
2. Select B-tree from the Structure drop-down list.
3. Enable the age column in the Columns list.
After you perform this modification, the table looks as follows. All records with a key of 35 are clustered together on Page 2:
Page 1 Page 2 Page 3
1,1 (513) 29 2,1 (1025) 35 3,1 (1537) 36
1,2 (514) 29 2,2 (1026) 35 3,2 (1538) 37
1,3 (515) 30 2,3 (1027) 35
Last modified date: 01/30/2023