5.3.4 Allocating an unused area inside a B-tree index page (PCTFREE)

You use the PCTFREE operand of the CREATE INDEX statement to specify the percentage of the allocated area in a B-tree index page that is to remain unused.

If a B-tree index page that is being used to store index keys contains no unused area, a B-tree index page split will occur whenever a row is added to or updated in the table for which the B-tree index is defined. For details about B-tree index page splits, see (2) B-tree index page splits.

When a B-tree index page split occurs, the number of disk I/O operations increases, which might result in HADB performance degradation. Therefore, when defining a B-tree index for a table to which a row will be added using the INSERT data manipulation SQL statement or whose row will be updated using the UPDATE statement, allocate some unused area in the index page.

Organization of this subsection

(1) Estimating the size of the unused area to be allocated in a B-tree index page
(2) B-tree index page splits

(1) Estimating the size of the unused area to be allocated in a B-tree index page

Use the following guidelines when allocating an unused area in a B-tree index page.

When you can estimate the number of rows that will be added or updated in the table

The number of index keys increases each time a row is added to, or updated in, the table. For the unused area, specify the value determined using the following formula.

Formula (%)

[Figure]

number of rows when data page is built means the number of table data rows that are stored when the adbimport command is first executed to populate the table with data.

When you cannot estimate the number of rows that will be added or updated in the table

Specify 30 (%) (default value of the PCTFREE operand) for the unused area.

When rows will not be added or updated in the table (only referenced)

Specify 0 (%) for the unused area.

When the following commands are executed, data is stored, leaving free the percentage of unused area specified in the PCTFREE operand:

adbimport command
- When data is imported in creation mode (when the -d option is specified)
- When the background-import facility is used (when the -b option is specified)
- When data is imported in addition mode (when neither the -d option nor the -b option is specified)
adbidxrebuild command
adbmergechunk command
adbunarchivechunk command

To Page Top

(2) B-tree index page splits

A B-tree index determines the range of keys managed by each page. If you add a key to an index page for storing keys that has no free space, the key group managed by the page is split in two. This is called a B-tree index page split. The following figure shows an example of a B-tree index page split.

Figure 5‒7: Example of B-tree index page splits

To Page Top