Figure 3-28 shows an example of even-split index page splitting.

Figure 3-28 Example of even-split index page splitting

Explanation

The addition of key 5 to index page a causes index page splitting. Key 5, as well as key 6 that follows, are moved to a new index page b so that the keys are distributed evenly.

Adding many contiguous key values can degrade an index page's data storage efficiency. In such a case, it is advisable to split the index information on the index page unevenly; this is called unbalanced index splitting.

When unbalanced index splitting is used, the place in the index information at which the index page is split is determined by the location of the key value that is being added. If the location of the new key value is in the top part of the index page, the system assumes the subsequent keys will also be added in the top part. Therefore, it stores the top part in the left-hand page, using the key value that is one greater than the added key value as the partitioning position. If the location of the new key value is in the bottom part of the index page, the system assumes the subsequent keys will also be added in the bottom part. Therefore, it stores the bottom part in the right-hand page, using the key value of the added key as the partitioning position. Figure 3-29 shows an example of an unbalanced index split in which a key is added to the bottom part of an index page.

Figure 3-29 Example of unbalanced index split

Explanation

Key 7 is added to the second half of index page a, so key 7, as well as key 8 that follows, are moved to index page b on the right side.

Application criteria

Use of unbalanced index splitting is advisable in the following cases, because it can improve the data storage efficiency and reduce the number of times index splitting is necessary:

• The index has a uniform degree of duplication of key values
• The index's key values are roughly uniform in size
• Index is one to which consecutive intermediate key values are added frequently

Specifying an unbalanced index split

For an unbalanced index split, you specify the UNBALANCED SPLIT option in the CREATE INDEX or CREATE TABLE of the definition SQL. For details on the unbalanced index split option, see the HiRDB Version 8 System Operation Guide.