Estimating storage from the number of index pages

Before you run a LOAD utility job to load an index, estimate the future storage requirements of the index.

About this task

Start of changeAn index key on an auxiliary table for LOBs is 19 bytes and uses the same formula as other indexes. The RID value that is stored within the index is 4, 5, or 7 bytes, depending on the table space type.End of change

In general, the length of the index key is the sum of the lengths of all the columns of the key, plus the number of columns that allow nulls. The length of a varying-length column is the maximum length if the index is padded. Otherwise, if an index is not padded, estimate the length of a varying-length column to be the average length of the column data, and add a two-byte length field to the estimate. You can retrieve the value of the AVGKEYLEN column in the SYSIBM.SYSINDEXES catalog table to determine the average length of keys within an index.

The following index calculations are intended only to help you estimate the storage required for an index. Because there is no way to predict the exact number of duplicate keys that can occur in an index, the results of these calculations are not absolute. It is possible, for example, that for a nonunique index, more index entries than the calculations indicate might be able to fit on an index page.

Important: Space allocation parameters are specified in kilobytes.

In the following calculations, assume the following:

k
The length of the index key.
n
The average number of data records per distinct key value of a nonunique index. For example:
  • a = number of data records per index
  • b = number of distinct key values per index
  • n = a / b
f
The value of PCTFREE.
p
The value of FREEPAGE.
r
The record identifier (RID) length. Use 4, 5, or 7 bytes, depending on the table space type:
Table space type r value to use
Partition-by-growth (PBG UTS) 5
Start of changePartition-by-range (PBR UTS) with relative page numberingEnd of change Start of change7End of change
Partition-by-range (PBR UTS) Start of changewith absolute page numberingEnd of change 5
Non-UTS defined with DSSIZE 4G or greater 5
Non-UTS defined with LARGE 5
Other non-UTS types 4
S
The value of the page size minus the length of the page header and page tail.
FLOOR
The operation of discarding the decimal portion of a real number.
CEILING
The operation of rounding a real number up to the next highest integer.
MAX
The operation of selecting the highest integer value.

Procedure

To estimate index storage size, complete the following calculations:

  1. Calculate the pages for a unique index.
    1. Calculate the total leaf pages
      1. Calculate the space per key

        space per key is approximately k + r + 3

      2. Calculate the usable space per page

        usable space per page is approximately FLOOR((100 - f) × S / 100)

      3. Calculate the entries per page

        entries per page is approximately FLOOR(usable space per page / space per key)

      4. Calculate the total leaf pages

        total leaf pages is approximately CEILING(number of table rows / entries per page)

    2. Calculate the total nonleaf pages
      1. Calculate the space per key

        space per key is approximately k + 7

      2. Calculate the usable space per page

        usable space per page is approximately FLOOR(MAX(90, (100 - f )) × S /100)

      3. Calculate the entries per page

        entries per page is approximately FLOOR(usable space per page / space per key)

      4. Calculate the minimum child pages

        minimum child pages is approximately MAX(2, (entries per page + 1))

      5. Calculate the level 2 pages

        level 2 pages is approximately CEILING(total leaf pages / minimum child pages)

      6. Calculate the level 3 pages

        level 3 pages is approximately CEILING(level 2 pages / minimum child pages)

      7. Calculate the level x pages

        level x pages is approximately CEILING(previous level pages / minimum child pages)

      8. Calculate the total nonleaf pages

        total nonleaf pages is approximately (level 2 pages + level 3 pages + ... + level x pages until the number of level x pages = 1)

  2. Calculate the pages for a nonunique index.
    1. Calculate the total leaf pages
      1. Calculate the space per key

        space per key is approximately 4 + k + (n × (r+1))

      2. Calculate the usable space per page

        usable space per page is approximately FLOOR((100 - f ) × S / 100)

      3. Calculate the key entries per page

        key entries per page is approximately n × (usable space per page / space per key)

      4. Calculate the remaining space per page

        remaining space per page is approximately usable space per page - (key entries per page / n) × space per key

      5. Calculate the data records per partial entry

        data records per partial entry is approximately FLOOR((remaining space per page - (k + 4)) / 5)

      6. Calculate the partial entries per page

        partial entries per page is approximately (n / CEILING(n / data records per partial entry)) if data records per partial entry >= 1, or 0 if data records per partial entry < 1

      7. Calculate the entries per page

        entries per page is approximately MAX(1, (key entries per page + partial entries per page))

      8. Calculate the total leaf pages

        total leaf pages is approximately CEILING(number of table rows / entries per page)

    2. Calculate the total nonleaf pages
      1. Calculate the space per key

        space per key is approximately k + r + 7

      2. Calculate the usable space per page

        usable space per page is approximately FLOOR (MAX(90, (100- f))× S / 100)

      3. Calculate the entries per page

        entries per page is approximately FLOOR((usable space per page / space per key)

      4. Calculate the minimum child pages

        minimum child pages is approximately MAX(2, (entries per page + 1))

      5. Calculate the level 2 pages

        level 2 pages is approximately CEILING(total leaf pages / minimum child pages)

      6. Calculate the level 3 pages

        level 3 pages is approximately CEILING(level 2 pages / minimum child pages)

      7. Calculate the level x pages

        level x pages is approximately CEILING(previous level pages / minimum child pages)

      8. Calculate the total nonleaf pages

        total nonleaf pages is approximately (level 2 pages + level 3 pages + ... + level x pages until x = 1)

  3. Calculate the pages for an index that is not compressed.
    1. Calculate the usable space per leaf page:

      usable space per leaf page is approximately FLOOR((100 - f) × S / 100)

      The page size can be 4096 bytes (4 KB), 8192 bytes (8 KB), 16384 bytes (16 KB), or 32768 bytes (32 KB). The length of the page header is 62 bytes. The length of the page tail is 20 bytes for 10-byte RBA or LRSN format, or 2 bytes for 6-byte RBA or LRSN format.

    2. Calculate the usable space per nonleaf page:

      usable space per nonleaf page is approximately FLOOR (MAX (90, (100 - f ) ) × S / 100)

      The page size can be 4096 bytes (4 KB), 8192 bytes (8 KB), 16384 bytes (16 KB), or 32768 bytes (32 KB). The length of the page header is 48 bytes. The length of the page tail is 20 bytes for 10-byte RBA or LRSN format, or 2 bytes for 6-byte RBA or LRSN format.

    3. Calculate the usable space per space map:

      usable space per space map is approximately CEILING ( (tree pages + free pages) / S), where S equals (page size − header length − tail length) × 2 − 1.

      Start of changeThe page size can be 4096 bytes (4 KB), 8192 bytes (8 KB), 16384 bytes (16 KB), or 32768 bytes (32 KB). The length of the page header is 28 bytes. The length of the page tail is 20 bytes for 10-byte RBA or LRSN format, or 2 bytes for 6-byte RBA or LRSN format.End of change

  4. Calculate the pages for a compressed index.
    1. Calculate the usable space per leaf page:

      usable space per leaf page is approximately FLOOR((100 - f) × S / 100)

      The page size can be 4096 bytes (4 KB), 8192 bytes (8 KB), 16384 bytes (16 KB), or 32768 bytes (32 KB). The length of the page header is 66 bytes. The length of the page tail is 20 bytes for 10-byte RBA or LRSN format, or 2 bytes for 6-byte RBA or LRSN format.

    2. Calculate the usable space per nonleaf page:

      usable space per nonleaf page is approximately FLOOR (MAX (90, (100 - f ) ) × S / 100)

      The page size is 4096 bytes for 4 KB, 8 KB, 16 KB, and 32 KB page sizes. The length of the page header is 48 bytes. The length of the page tail is 20 bytes for 10-byte RBA or LRSN format, or 2 bytes for 6-byte RBA or LRSN format.

    3. Calculate the usable space per space map:

      usable space per space map is approximately CEILING ( (tree pages + free pages) / S), where S equals (page size − header length − tail length) × 2 − 1.

      Start of changeThe page size is 4096 bytes for 4 KB, 8 KB, 16 KB, and 32 KB page sizes. The length of the page header is 28 bytes. The length of the page tail is 20 bytes for 10-byte RBA or LRSN format, or 2 bytes for 6-byte RBA or LRSN format.End of change

  5. Calculate the total space requirement by estimating the number of kilobytes required for an index built by the LOAD utility.
    1. Calculate the free pages

      free pages is approximately FLOOR(total leaf pages / p), or 0 if p = 0

    2. Calculate the space map pages

      space map pages is approximately CEILING((tree pages + free pages) / S)

    3. Calculate the tree pages

      tree pages is approximately MAX(2, (total leaf pages + total nonleaf pages))

    4. Calculate the total index pages

      total index pages is approximately MAX(4, (1 + tree pages + free pages + space map pages))

    5. Calculate the total space requirement

      total space requirement is approximately 4 × (total index pages + 2)

Example

In the following example of the entire calculation, assume that an index is defined with these characteristics:
  • The index is unique.
  • The table it indexes has 100000 rows.
  • The key is a single column defined as CHAR(10) NOT NULL.
  • The value of PCTFREE is 5.
  • The value of FREEPAGE is 4.
  • The page size is 4 KB.
Table 1. Sample of the total space requirement for a unique index
Quantity Calculation Result
Length of key
Average number of duplicate keys
PCTFREE
FREEPAGE
k
n
f
p
10
1
5
4
Calculate total leaf pages
Space per key
Usable space per page
Entries per page
 
Total leaf pages
 
k + 7
FLOOR((100 - f ) × 4032/100)
FLOOR(usable space per page / space per key)
 
CEILING(number of table rows / entries per page)
 
17
3844
225
 
445
Calculate total nonleaf pages
Space per key
Usable space per page
Entries per page
Minimum child pages
Level 2 pages
Level 3 pages
 
Total nonleaf pages
 
k + 7
FLOOR(MAX(90, (100 - f )) × 4046/100)
FLOOR(usable space per page / space per key)
MAX(2, (entries per page + 1))
CEILING(total leaf pages / minimum child pages)
CEILING(level 2 pages / minimum child pages)
 
(level 2 pages + level 3 pages +…+ level x pages until x = 1)
 
17
3843
226
227
2
1
 
3
Calculate total space required
Free pages
Tree pages
Space map pages
Total index pages
 
TOTAL SPACE REQUIRED, in KB
 
FLOOR(total leaf pages / p), or 0 if p = 0
MAX(2, (total leaf pages + total nonleaf pages))
CEILING((tree pages + free pages)/8131)
MAX(4, (1 + tree pages + free pages + space map pages))
 
4 × (total index pages + 2)
 
111
448
1
561
 
2252