Index reorganization is used for freeing the unusable areas in index storage pages that have resulted from addition, deletion, or updating of a large volume of data.

• Use index reorganization when there has been frequent data updating (UPDATE).
• Use table reorganization when there has been frequent data deletion (DELETE) or addition (INSERT).
• When reorganizing a table would take too much time, reorganizing its indexes only can reduce retrieval time.

In index re-creation, the table's data is retrieved. In contrast, the table's data is not retrieved in index reorganization. For this reason, index reorganization requires less processing time than re-creation,^# sorting is not required, and processing performance is improved. However, since plug-in indexes cannot be reorganized, you must re-create them.

#: Processing time is reduced when the following condition is satisfied:

number-of-used-pages-in-RDAREAs-storing-table > number-of-used-pages-in-RDAREAs-storing-index

• When multiple indexes in the same RDAREA are reorganized simultaneously, the pdhold command must be used to shut down the RDAREA before executing the reorganization. Then, after index reorganization is completed, the pdrels command must be used to release the RDAREA's shutdown status.
• Since plug-in indexes cannot be reorganized, you must re-create them.

If no database update log is being collected during index reorganization (no-log mode or pre-update log acquisition mode is in effect), there is a commensurate reduction in processing time. Collection of a database update log is specified with the -l option of the pdrorg command. The default is the pre-update log acquisition mode.

When an index is reorganized, a percentage of unused area per page can be specified in the PCTFREE operand of CREATE TABLE or CREATE INDEX. However, if the index being reorganized is in an RDAREA that has insufficient free space to accommodate the specified percentage of unused area, the RDAREA can run out of space during index reorganization processing. To prevent this, you should specify the idxfree operand in the option statement of the database reorganization utility (pdrorg), and use the PCTFREE operand of CREATE TABLE or CREATE INDEX to change the percentage of unused area per page.

Note that this is a temporary measure that is used when the RDAREA cannot be immediately expanded before index reorganization. In preparation for data updating, you should use the database structure modification utility (pdmod command) to expand the RDAREA so that reorganization can be performed that is within the value specified in the PCTFREE operand of CREATE TABLE.