# Space requirements for indexes

When designing indexes, you must be aware of their space requirements. For compressed indexes, the estimates you derive from the formulas in this topic can be used as an upper bound, however, it will likely be much smaller.

## Space requirements for uncompressed indexes

- (
`average index key size`+`index key overhead`) ×`number of rows`× 2

- For every column that allows null values, add one extra byte for the null indicator.
- For block indexes created internally
for multidimensional clustering (MDC) or insert time clustering (ITC)
tables, the
number of rows

would be replaced by thenumber of blocks

. - For every column that has a random ordering, add two extra bytes.

## Space requirements for XML indexes

- (
`average index key`+`index key overhead`) ×`number of indexed nodes`× 2

## Temporary space requirements for index creation

- (
`average index key size`+`index key overhead`) ×`number of rows`× 3.2

- (
`average index key size`+`index key overhead`) ×`number of indexed nodes`× 3.2

3.2is for index overhead, and space required for sorting during index creation. The

`number of rows`or the

`number of indexed nodes`is the number in an entire table or in a given data partition.

`average index key size`×`number of indexed nodes`× 1.2

## Estimating the number of keys per leaf page

The following two formulas can be used to estimate the number of keys per index leaf page (the second provides a more accurate estimate). The accuracy of these estimates depends largely on how well the averages reflect the actual data.

- A rough estimate of the average number of keys per leaf page is:
- ((.9 * (
`U`- (`M`×2))) × (`D`+ 1)) ÷ (`K`+ 7 + (`Ds`×`D`))

`U`, the usable space on a page, is approximately equal to the page size minus 100. For example, with a page size of 4096, U would be 3996.`M`=`U`÷ (9 + minimumKeySize)`Ds`= duplicateKeySize (See the note underTemporary space requirements for index creation

.)`D`= average number of duplicates per key value`K`= averageKeySize

Note: The minimumKeySize and averageKeysize must include an additional:- byte for each nullable key part.
- 2 bytes for the length of each variable length key part,
- 2 bytes for each column that is defined with a random ordering.

If there are include columns, they should be accounted for in minimumKeySize and averageKeySize.

The`minimum key size`is the sum of the key parts that make up the index:`fixed overhead`+`variable overhead`+`byte count of sql-data-type`

- The
`fixed overhead`is 13 bytes. - The
`variable overhead`is the minimum depth of the indexed node plus 4 bytes. - The
`byte count of sql-data-type`value follows the same rules as SQL.

The

`.9`

can be replaced by any (100 - pctfree)/100 value, if a percent free value other than the default value of ten percent is specified during index creation. - ((.9 * (
- A more accurate estimate of the average number of keys per leaf
page is:
`number of leaf pages`=`x`/ (`avg number of keys on leaf page`)

`x`is the total number of rows in the table or partition.For the index on an XML column,

`x`is the total number of indexed nodes in the column.You can estimate the original size of an index as:- (
`L`+ 2`L`/(`average number of keys on leaf page`)) ×`pagesize`

For DMS table spaces, add the sizes of all indexes on a table and round up to a multiple of the extent size for the table space on which the index resides.

You should provide additional space for index growth due to INSERT/UPDATE activity, from which page splits might result.

Use the following calculation to obtain a more accurate estimate of the original index size, as well as an estimate of the number of levels in the index. (This might be of particular interest if include columns are being used in the index definition.) The average number of keys per non-leaf page is roughly:- ((.9 × (
`U`- (`M`× 2))) × (`D`+ 1))÷(`K`+ 13 + (9 *`D`))

`U`, the usable space on a page, is approximately equal to the page size minus 100. For a page size of 4096, U is 3996.`D`is the average number of duplicates per key value on non-leaf pages (this will be much smaller than on leaf pages, and you might want to simplify the calculation by setting the value to 0).`M`=`U`÷ (9 + minimumKeySize for non-leaf pages)`K`= averageKeySize for non-leaf pages

The minimumKeySize and the averageKeySize for non-leaf pages will be the same as for leaf pages, except when there are include columns. Include columns are not stored on non-leaf pages.

You should not replace .9 with (100 -

`pctfree`)÷100, unless this value is greater than .9, because a maximum of 10 percent free space will be left on non-leaf pages during index creation.The number of non-leaf pages can be estimated as follows:

where:`if`

`L`> 1 then {`P`++;`Z`++} While (`Y`> 1) {`P`=`P`+`Y``Y`=`Y`/ N`Z`++ }`P`is the number of pages (0 initially).`L`is the number of leaf pages.`N`is the number of keys for each non-leaf page.`Y`=`L`÷`N``Z`is the number of levels in the index tree (1 initially).

Note: The previous calculation applies to single, nonpartitioned indexes, or to a single index partition for partitioned indexes.Total number of pages is:- T = (
`L`+`P`+ 2) × 1.0002

The amount of space required to create the index is estimated as:- T × page size