The REORG operation performs the following functions:
Checks referential integrity, if applicable to the target table, and either deletes rows that violate it or invalidates any affected indexes. (Referential integrity is a relational property that every foreign key value in a table exists as a primary key value in a referenced table.
Performs an internal reorganization of one or more indexes for a table (of all types) to improve the internal storage of this information and, therefore, performance when the index is used to access data. It can rebuild all indices, selectively rebuild one or more named indices, or selectively rebuild one or more segments of one or more named indices.
Specifies the DEFERRED index created using the CREATE INDEX statement. A DEFERRED index is an empty index structure that can be populated later.
Restores pre-computed views. For example, if your application supports precalculation of views, you can use the REORG command to restore only precomputed views without touching the indexes in the target table. Alternatively, you can use the REORG command to restore indexes and views.
In addition to restoring aggregate table data, the REORG command restores indexes in aggregate tables.
REORG operation is necessary in the following cases:
To repair damaged indexes if you use the database restore operation to restore individual segments of a table or index.
Whenever changes to the database affect more than 30 percent of the data, run TMU with the REORG statement for any tables that are directly modified. Periodically rebuilding such tables and indexes using the REORG statement provides referential integrity and optimal performance.
To reorganize invalid STAR indexes. Some operations may invalidate STAR indices. For example, increasing the MAXROWS PER SEGMENT or MAXSEGMENTS parameter in a table or using the ALTER statement to expand a segment may invalidate the STAR indexes in tables that reference the modified table. These operations always generate a warning message stating that STAR indices based on the changed table may be invalid, in which case it is necessary to change the resulting STAR indices. You can either reorganize the affected indexes when a message is issued, or assign a REORG operation for a more convenient time. However, any operation without a query (INSERT, UPDATE, or DELETE) against a table with an invalid index results in an error message stating that the index should be reorganized. You must perform a REORG operation before the table is available for INSERT, UPDATE, DELETE, or LOAD operations.
REORG is not required in the following cases:
If no changes are made to the database, with the exception of full data downloads.
If the table and indexes are segmented the same way, and new index data is loaded into new index segments corresponding to the new table segments.