16.1.2 Benefits of rebuilding indexes

(1) Rebuilding indexes in unfinished status (releasing indexes from unfinished status)

When a new index is defined for a table to which segments for storing rows have been assigned, the newly defined index is placed in unfinished status. Retrieval processing using an index in unfinished status results in an error.

When the adbidxrebuild command is executed on a table for which any indexes that are in unfinished status are defined, the indexes are released from unfinished status, thereby enabling them to be used for retrieval processing.

For details about the status in which segments for storing rows are assigned, see Notes on defining B-tree indexes (unfinished status of B-tree indexes) in the HADB Setup and Operation Guide.

To Page Top

(2) Rebuilding unique indexes on which a uniqueness constraint violation occurred (releasing the uniqueness constraint violation)

If a unique index is in violation of the uniqueness constraint (uniqueness constraint violation), retrieval efficiency might be lower than when the uniqueness constraint is satisfied.

In such a case, you can release the uniqueness constraint violation by deleting the rows causing the uniqueness constraint violation and then using the adbidxrebuild command to rebuild the unique index.

For details about how to release a uniqueness constraint violation, see Steps to take when the uniqueness constraint is violated (when the KFAA61205-W message is output) in Problems related to B-tree indexes in Troubleshooting in the HADB Setup and Operation Guide.

To Page Top

(3) Rebuilding indexes in normal status

You can also use the adbidxrebuild command to rebuild indexes that are in normal status (indexes that are not in unfinished status or on which a uniqueness constraint violation occurred). You can expect the following benefits from rebuilding such indexes.

For B-tree indexes

If you rebuild B-tree indexes (including those corresponding to the primary key) defined for a table in which rows have been updated, added, or deleted repeatedly, you can obtain the following benefits:

• Data that has become invalid as a result of updating or deleting rows can be deleted from the B-tree indexes. This reduces the amount of DB area required to store the B-tree indexes.

• Unused areas in index pages that have been used for adding or updating rows can be reallocated. This reduces the frequency of index page splitting. For details about the unused areas in index pages, see Allocating an unused area inside a B-tree index page (PCTFREE) in Designing a B-tree index in Designing a Database in the HADB Setup and Operation Guide.

For text indexes

If you rebuild the text indexes defined for a table in which rows have been updated, added, or deleted repeatedly, you can obtain the following benefits:

• You can delete from the text indexes links to data pages that contain no actual data, and that were generated as a result of updating or deleting rows. This deletion improves the performance of retrievals that use text indexes. It also reduces the amount of DB area required to store the text indexes.

• Unused areas in index pages that have been used to add or update rows can be reallocated. This reduces the frequency of index page splitting. For details about the unused areas in index pages, see Allocating an unused area inside a text index page (PCTFREE) in Designing a text index in Designing a Database in the HADB Setup and Operation Guide.

For range indexes

If you rebuild the range indexes defined for a table in which rows have been updated or deleted repeatedly, you can obtain the following benefits:

• The ranges of the data included in the range indexes become smaller, thereby improving retrieval performance.

• The amount of DB area required for storing the range indexes can be reduced because range information that is not needed for the range indexes is deleted.

To Page Top