16. Calculating Disk Space : Space Requirements for Tables : Calculate Space Requirements for B-tree Tables
 
Share this page                  
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)
Note:  When rows span pages, determine the number of data pages using the calculation in Calculate Space Requirements When Rows Span Pages (see page Calculate Space Requirements When Rows Span Pages) instead.
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