Calculating index size based on number of rows
About this task
You can estimate the size of an R-tree index in pages by performing a series of calculations based on the number of rows in the table.
The following procedure estimates only the number of leaf pages in the R-tree index; it does not calculate the number of branch pages. This is because almost all of the space in an R-tree index is usually taken up by leaf pages, due to the wide shape of the tree. Therefore, calculating the number of leaf pages is usually adequate for a rough estimate of the total number of disk pages that make up the R-tree index.
To estimate the size of an R-tree index in disk pages:
Procedure
- Determine the size, in bytes, of the key value for the
data type being indexed.This value is referred to in this topic as colsize.
Entries of this size appear in index leaf pages.
If you are indexing a user-defined data type, the size of the key value is the value of the INTERNALLENGTH variable of the CREATE OPAQUE TYPE statement.
- Determine the size, in bytes, of each index entry in the
leaf page with the following formula that incorporates the overhead:
leafentrysize = colsize + 16 bytes
- Determine the pagesize in bytes of the database
server that you use. To obtain the page size, run the oncheck
-pr command and look for the value next to
Page Size
: - Estimate the number of entries per index-leaf page with
the following formula:
whereleafpagents = trunc ( pagefree / leafentrysize ) * 60%
pagefree = pagesize - 88
The value leafpagents is multiplied by 60 % because index leaf pages are usually just over half full.
The trunc() function notation indicates you should round down to the nearest integer value.
- Estimate the number of leaf pages with the following formula:
leaves = rows / leafpagents
Use the SQL statement
SELECT COUNT(*) FROM table
to calculate the number of rows in the table.