5.8.3 Determining the number of storage pages for each B-tree index segment

(1) Determining the number of storage pages used in the lower page segment (variable IP_LOWER(i))

The number of storage pages used in the lower page segment (variable IP_LOWER(i)) can be determined by using the formula shown later.

If the null-value exclusion specification is specified for a B-tree index, exclude keys that are comprised of null values only from the formulas for determining the number of key types and the average number of duplicated key values.

Formula

IP_LOWER(i) = (PIDX_LEAF + PDUP) × R

Determine PIDX_LEAF from Formula 1.

Determine PDUP from Formula 2. PDUPLIST is the average number of row ID list pages for each key value, and PDUPDIR is the average number of row ID directory pages for each key value.

R is the number of duplicate keys in each B-tree index page, or a value between 1.2 and 1.5 if the key length varies. If the value converges around the average value, use 1.

Formula 1 (for determining PIDX_LEAF)

Formula 2 (for determining PDUP)

Explanation of variables

• key_num

  Number of key types in which the number of duplicate key values is 255 or less (key types)

• dup_key_num

  Number of key types in which the number of duplicate key values is 256 or greater (key types)

• dup_key_dup

  Average number of duplicate keys in which the number of duplicate key values is 256 or greater (keys)

• page_size

  Page size of data DB area (bytes)

• KNPP

  Average number of keys stored per B-tree index page

  Use the following formula to determine this value.

  Formula (for determining KNPP)

• pctfree

  Percentage of unused area specified in the CREATE INDEX statement (%)

  If the percentage of unused area is not specified, assume 30% for the calculation.

• key_dup

  Average number of duplicate keys in which the number of duplicate key values is 255 or less (keys)

• dbarea_file_num

  Number of files in the data DB area (number of files)

• KEYSZDB

  Length of keys stored in the database (bytes)

  To determine the key length, use the KEYSZ value determined in 5.8.4 Determining the key length (KEYSZ) of a B-tree index in the following formula:

  Formula (for determining KEYSZDB)

To Page Top

(2) Determining the number of storage pages used in the upper page segment (variable IP_UPPER(i))

The number of storage pages used in the upper page segment (variable IP_UPPER(i)) can be determined by using the following formula:

Formula

Determine PIDX(k) using the recursion formula shown in Formula 1. Always determine the value of PIDX(2), and repeat the formula for PIDX(k + 1) until PIDX(n) = 1 is reached.

R is the number of duplicate keys in each B-tree index page, or a value between 1.2 and 1.5 if the key length varies. If the value converges around the average value, use 1.

Formula 1 (for determining PIDX(k))

Explanation of variables

• PIDX_LEAF

  Determine this value by using Formula 1 (for determining PIDX_LEAF) in (1) Determining the number of storage pages used in the lower page segment (variable IP_LOWER(i)).

• page_size

  Page size of data DB area (bytes)

• pctfree

  Percentage of unused area specified in the CREATE INDEX statement (%)

  If the percentage of unused area is not specified, assume 30% for the calculation.

• KEYSZDB

  Length of keys stored in the database (bytes)

  Determine the value in the same way as when determining the variable KEYSZDB in (1) Determining the number of storage pages used in the lower page segment (variable IP_LOWER(i)).

To Page Top