Index reorganization can be applied to normal indexes only; it cannot be used to reorganize plug-in indexes. Index reorganization should be executed in order to release dead space in index storage pages that has been generated by addition, deletion, or updating of large amounts of data.

• When data updating (UPDATE) has been performed frequently, reorganization of the indexes only is recommended.
• When data deletion (DELETE) or addition (INSERT) has been performed frequently, reorganization of the table is recommended.
• If there is not enough time to reorganize a table, you can reorganize the index only in order to reduce index search time.

Index re-creation involves a search of the table data; index reorganization does not involve a search of the table data. For this reason, index reorganization requires less processing time than re-creation,^* sorting is not required, and processing performance is improved.

^* Processing time is reduced if the following condition is satisfied:

Number of pages used in table storage RDAREA > Number of pages used in index storage RDAREA

Before reorganizing simultaneously multiple indexes that are stored in the same RDAREA, you should use the pdhold command to place the RDAREA on shutdown status. When reorganization processing has been completed, you can use the pdrels command to release the RDAREA from shutdown status.

The processing time involved in index reorganization can be reduced by not collecting database update log information (setting either the no-log mode or the pre-update log acquisition mode in the -l option of the database reorganization utility (pdrorg)).

The amount of unused area per page is specified in the PCTFREE operand of CREATE TABLE or CREATE INDEX when the index is reorganized is applied. 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.

It must be noted that this is a temporary measure that is used during reorganization. In normal operations, the RDAREA should be expanded by executing the database structure modification utility (pdmod).