5.4.2 Allocating an unused area inside a text index page (PCTFREE)

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

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

When a text index page split occurs, the number of disk I/O operations increases, which might result in HADB performance degradation. Therefore, when defining a text 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 text index page
(2) Text index page splits

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

Use the following guidelines when allocating an unused area in a text 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) Text index page splits

When data is added to or updated in a base table for which a text index has been defined, data is added to an index page of the text index. If the index page has no room for the data, a new index page is allocated for the text index. This is called a text index page split. The following figure shows an example of a text index page split.

Figure 5‒8: Example of a text index page split

To Page Top