HiRDB indexes are based on a B-tree structure. When there is not enough space to add a key value to an index page, index page splitting occurs. Index page splitting is the method used by HiRDB to obtain additional free space when there is not enough space to add a key value to an index page. This method involves dividing the index information in the index page into two equal-sized halves and moving the latter half to a new page. The following figure shows an example of a typical index page split.

Figure 14-2 Example of index page splitting

(a) Increase in the amount of log information due to index page splitting

As data is added to a table that has an index, key values are also added to the index pages. If the percentage of unused space in the index pages is small, index page splitting might occur frequently. Frequent index page splitting results in an increase in the amount of log information due to changes made to the index structure.

(b) Reducing the frequency of index page splits

The frequency of index page splits can be reduced by changing the percentage of unused space in the index pages; the percentage value is obtained from the index page split information acquired by executing the statistics analysis utility (pdstedit command). The percentage of unused space in the index pages is changed by modifying the value of the PCTFREE operand in the CREATE INDEX definition SQL. When this is done, the index must then be redefined.

For details on using the index page split information acquired by the statistics analysis utility, see 23.7 Tuning indexes.

In the case of normal index page splitting, adding consecutive key values reduces the index page data storage efficiency. For this situation, an unbalanced index split can be used. Unbalanced index split is a method for dividing unevenly the index information in an index page, rather than dividing it uniformly into two equal-size halves. This method improves the data storage efficiency when consecutive key values are added.

When unbalanced index split is specified, the ratio used for dividing the index information in an index page is determined by the location in the page at which a new key value is to be inserted.

If the insertion location is in the first half of the index page, the key value is expected to be added in the first part of the page. Therefore, the first portion is stored in the page on the left-hand side using as the split location the next key value that is greater than the key being added.

If the location of the key value to be added is in the second half of the index page, the key is expected to be added in the latter part of the page. Therefore, the latter portion is stored in the page on the right-hand side using as the split location the key value that is being added. The following figure shows an example of an unbalanced index split when a key value is to be added in the latter half of an index page.

Figure 14-3 Example of an unbalanced index split

(a) Scope of an unbalanced index split

Unbalanced index split is applicable only to leaf pages other than the last page. During rollback, regular index page splitting is used instead of an unbalanced index split. In this case, a page is split at a 50:50 ratio.

On the last leaf page, data is divided on the basis of the value specified in the PCTFREE operand in the CREATE INDEX definition SQL statement in order to handle addition of key values in ascending order for data loading-related processes.

(b) Criteria for an unbalanced index split

Unbalanced index split is effective when it is applied to an index that satisfies the following conditions:

• There is balance in terms of duplication of key values.
• Lengths of key values are close to uniform.
• Consecutive intermediate key values are added frequently.

(c) Specification

To use an unbalanced index split, specify UNBALANCED SPLIT for the index option of the CREATE INDEX, CREATE TABLE, or ALTER TABLE definition SQL statement.