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:
```
leafpagents = trunc ( pagefree / leafentrysize ) * 60%
```
where
```
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.

Results

The number of leaf pages that make up the R-tree index is close to the total number of disk pages that make up the index.

Important: As rows are deleted from the table, and new ones are inserted, the number of index entries can vary within a page. The calculation described in this topic yields an estimate for an R-tree index whose leaf pages are 60 % full. Your R-tree index might be smaller or larger depending on the activity within the table and the data that you store.