You can estimate the size of index pages, using a series
of formulas.

To estimate the number of index pages:

- Add up the total widths of the indexed
column or columns.
This value is referred to as colsize.
Add 4 to *colsize* to obtain *keysize*, the actual size
of a key in the index. For example, if *colsize* is 6, the value
of *keysize* is 10.

- Calculate the expected proportion of unique entries to
the total number of rows.
The formulas in subsequent
steps see this value as *propunique*.

If the index is unique
or has few duplicate values, use 1 for *propunique*.

If
a significant proportion of entries are duplicates, divide the number
of unique index entries by the number of rows in the table to obtain
a fractional value for *propunique*. For example, if the number
of rows in the table is 4,000,000 and the number of unique index entries
is 1,000,000, the value of *propunique* is .25.

If the
resulting value for *propunique* is less than .01, use .01 in
the calculations that follow.

- Estimate the size of a typical index entry
with one of the following formulas, depending on whether the table
is fragmented or not:
- For nonfragmented tables, use the following formula:
`entrysize = (`*keysize* * *propunique*) + 5 + 4

The
value 5 represents the number of bytes for the row pointer in a nonfragmented
table.

For nonunique indexes, the database server stores the
row pointer for each row in the index node but stores the key value
only once. The `entrysize` value represents the average
length of each index entry, even though some entries consist of only
the row pointer.

For
example, if *propunique* is .25, the average number of rows for
each unique key value is 4. If *keysize* is 10, the value of *entrysize* is
11.5, calculated as (10 * 0.25) + 5 + 4 = 2.5 + 9 = 11.5. The following
calculation shows the space required for all four rows:

`space for four rows = 4 * 11.5 = 46`

This space
requirement is the same when you calculate it for the key value and
add the four row pointers, as the following formula shows:

`space for four rows = 10 + (4 * 9) = 46`

- For fragmented tables, use the following formula:
`entrysize = (`*keysize* * *propunique*) + 9 + 4

The
value 9 represents the number of bytes for the row pointer in a fragmented
table.

- Estimate the number of entries per
index page with the following formula:
`pagents = trunc(`*pagefree*/*entrysize*)

In
this formula:

*pagefree* is the page size minus the page
header (2020 for a 2-kilobyte page size).
*entrysize* is the size of a typical index
entry, which you estimated in the previous step.

The **trunc()** function notation indicates that you should
round down to the nearest integer value.

- Estimate the number of leaf pages with the following formula:
`leaves = ceiling(`*rows*/*pagents*)

In
this formula:

*rows* is the number of rows that you expect
to be in the table.
*pagents* is the number of entries per index
page, which you estimated in the previous step.

The **ceiling()** function notation indicates that you
should round up to the nearest integer value.

- Estimate the number of branch pages
at the second level of the index with the following formula:
`branches`_{0} = ceiling(*leaves*/*node_ents*)

Calculate
the value for *node_ents* with the following formula:

`node_ents = trunc( `*pagefree* / ( *keysize* + 4) + 4)

In
this formula:

*pagefree* is the page size minus the page
header (2020 for a 2-kilobyte page size).
`keysize` is the *colsize* plus 4. You obtained
this value in step 1.

In the formula, 4 represents the number of bytes for the leaf
node pointer.

- If the value of
*branches*_{0} is greater
than 1, more levels remain in the index. To calculate
the number of pages contained in the next level of the index, use
the following formula:

`branches`_{n+1} = ceiling(*branches*_{n}/*node_ents*)

In
this formula:

*branches*_{n} is the number of
branches for the last index level that you calculated.
*branches*_{n+1} is the number of
branches in the next level.
*node_ents* is the value that you calculated
in step 6.

- Repeat the calculation in step 7 for each level of the
index until the value of branches
_{n+1 }equals 1.
- Add up the total number of pages for all branch levels
calculated in steps 6 through 8. This sum is called
*branchtotal*.
- Use the following formula to calculate the number of pages
in the compact index:
`compactpages = (`*leaves* + *branchtotal*)

- If your database server instance uses a fill factor for
indexes, the size of the index increases.
The default
fill factor value is 90 percent. You can change the fill factor value
for all indexes with the FILLFACTOR configuration parameter. You can
also change the fill factor for an individual index with the FILLFACTOR
clause of the CREATE INDEX statement in SQL.

To
incorporate the fill factor into your estimate for index pages, use
the following formula:

`indexpages = 100 * `*compactpages* / FILLFACTOR

The preceding estimate is a guideline only. As rows are
deleted and new ones are inserted, the number of index entries can
vary within a page. This method for estimating index pages yields
a conservative (high) estimate for most indexes. For a more precise
value, build a large test index with real data and check its size
with the **oncheck** utility.

Tip: A forest of trees index can be
larger than a B-Tree index. When you estimate the size of a forest
of trees index, the estimates apply to each subtree in the index.
Then, you must aggregate the buckets to calculate the total estimation.