Calculate Space Requirements for B-tree Tables
Follow these steps to determine the amount of space needed to store the data in a B-tree table:
1. Create the table and modify it to B-tree.
2. Determine the number of rows that fit on a page, the number of keys that fit on an index page, and the number of keys that fit on a leaf page (adjusted by the appropriate fillfactors):
select tups_per_page * table_dfillpct/100, keys_per_page * table_ifillpct/100, keys_per_leaf * table_lfillpct/100 from iitables where table_name = ‘tablename’;
3. Determine the number of leaf pages needed. Save the remainder of the division because it is used later:
leaf_pages = (num_rows/keys_per_leaf)
remainder = modulo (num_rows / (keys_per_leaf)
4. Determine the number of data pages needed.
data_pages = leaf_pages * (keys_per_leaf / tups_per_page)
5. If the remainder from Step 3 is greater than 0, adjust the number of leaf and data pages:
a. leaf_pages = leaf_pages + 1
b. Round the division up to the nearest integer:
data_pages = data_pages + (remainder / tups_per_page)
Note: When rows span pages, Step 5b does not apply.
6. Determine the number of sprig pages.
sprig_pages: The number of index pages that have leaf pages as their next lower level:
a. If leaf_pages <= keys_per_page, then sprig_pages = 0
b. Otherwise, calculate as follows, and round up to the nearest integer:
sprig_pages = (leaf_pages / keys_per_page)
7. Determine the number of index pages.
index_pages: The number of index pages that are not sprig pages. This is done iteratively. Do the following if sprig_pages > keys_per_page:
x = sprig_pages
do
{
x = x / keys_per_page
index_pages = index_pages + x
}
while (x > keys_per_page>
8. Determine the total space required. The total includes data pages, leaf pages, sprig pages, and index pages.
total_btree_pages = data_pages + leaf_pages + sprig_pages + index_pages