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
For each uncompressed index, the space needed
can be estimated as:
- (average index key size + index
key overhead) × number of rows × 2
where:
- The average index key size is the byte count
of each column in the index key. When estimating the average column
size for VARCHAR and VARGRAPHIC columns, use an average of the current
data size, plus two bytes.
- The index key overhead depends on the type
of table on which the index is created:
Table 1. Index
key overhead for different tablesType of table space |
Table type |
Index type |
Index key overhead |
Any |
Any |
XML paths or regions |
11 bytes |
Regular |
Nonpartitioned |
Any |
9 bytes |
Partitioned |
Partitioned |
9 |
Nonpartitioned |
11 |
Large |
Partitioned |
Partitioned |
11 |
Nonpartitioned |
13 |
- The number of rows is the
number of rows in a table or the number of rows in a given data partition.
Using the number of rows in the entire table in this calculation will
give you an estimate the size for the index (for a nonpartitioned
index) or for all index partitions combined (for a partitioned index).
Using the number of rows in a data partition will give you an estimate
of the size for the index partition.
- The factor of "2" is for overhead, such as non-leaf pages
and free space.
Note: - For every column that allows null values, add one extra byte for
the null indicator.
- For block indexes created internally for multidimensional clustering
(MDC) tables, the "number of rows" would be replaced by the "number
of blocks".
Space requirements for XML indexes
For
each index on an XML column, the space needed can be estimated as:
- (average index key + index key overhead)
× number of indexed nodes × 2
where:
- The average index key is the sum of the key
parts that make up the index. The XML index is made up of several
XML key parts plus a value (sql-data-type):
- 14 + variable overhead + byte count
of sql-data-type
where: - 14 represents the number of bytes of fixed overhead
- The variable overhead is the average depth
of the indexed node plus 4 bytes.
- The byte count of sql-data-type follows the
same rules as SQL.
- The number of indexed nodes is the number of
documents to be inserted multiplied by the number of nodes in a sample
document that satisfy the XML pattern expression (XMLPATTERN) in the
index definition. The number of indexed nodes could
be the number of nodes in a partition or the entire table.
Temporary space requirements for index creation
Temporary
space is required when creating the index. The maximum amount of temporary
space required during index creation can be estimated as:
- (average index key size + index
key overhead) × number of rows × 3.2
For those indexes for which there could be more than
one index key per row, such as spatial indexes, indexes on XML columns
and internal XML regions indexes, the temporary space required can
be estimated as:
- (average index key size + index key
overhead) × number of indexed nodes ×
3.2
where the factor of
"3.2" is 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.
Note: In the case of non-unique
indexes, only one copy of a given duplicate key
entry is stored on any given leaf node. For indexes on tables in
LARGE table spaces the size for duplicate keys is 9
for nonpartitioned indexes, 7 for partitioned indexes and indexes
on nonpartitioned tables. For indexes on tables in REGULAR table
spaces these values are 7 for nonpartitioned indexes,
5 for partitioned indexes and indexes on nonpartitioned tables.
The only exception to these rules are XML paths and XML regions indexes
where the size of duplicate keys is always 7.The estimate shown
above assumes no duplicates. The space required to store an index
might be over-estimated by the formula shown above.
Temporary
space is required when inserting if the number of index nodes exceeds
64 KB of data. The amount of temporary space can be estimated as:
- 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.
Note: For
SMS table spaces, the minimum required space for leaf pages is three times the page size. For DMS table spaces,
the minimum is an extent.
- A rough estimate of the average number of keys per leaf page is:
- ((.9 * (U - (M×2))) × (D +
1)) ÷ (K + 7 + (Ds × D))
where: - 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 under "Temporary space requirements for index creation".)
- D = average number of duplicates per key value
- K = averageKeySize
Remember that minimumKeySize and averageKeysize must
include an extra byte for each nullable key part, and an extra two
bytes for the length of each variable length key part.
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
where:
- 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.
- 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)
where 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 + 2L/(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))
where:
- 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:
if L > 1 then {P++; Z++}
While (Y > 1)
{
P = P + Y
Y = Y / N
Z++
}
where:
- 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 calculation above applies to a single,
nonpartitioned indexes, or to a single index partition for partitioned
indexes.
Total number of pages is:
The additional 0.02% (1.0002) is for overhead, including space
map pages.
The amount of space required to create the index
is estimated as: