The REORGANIZE INDEX command corrects index fragmentation and corrects the skewing of index key values. REORGANIZE INDEX also revalidates an invalid index.
REORGANIZE INDEX automatically updates statistics while the index is being rebuilt. To calculate index statistics, the Database Services Utility has to have an exact count of the pages in the dbspace which contain rows from the indexed table. When the table is the only table in the dbspace, the database manager can find out how many pages contain rows from the dbspace directory. In other words, the number of used pages is the number of pages containing rows. If there is more than one table in the dbspace, the database manager has to scan each page to determine which ones are occupied by the indexed table. Hence, you get better performance for the REORGANIZE INDEX command when the indexed table is the only table in the dbspace. The DB2 Server for VSE & VM manuals recommend allocating one table per dbspace if the tables are large.
To reorganize a valid index, the database manager uses an internal dbspace as temporary storage to hold the keys of the index. The internal dbspace requirements to perform a REORGANIZE INDEX operation are one third of that required to perform the equivalent CREATE INDEX. If you do not have enough space, see the DB2 Server for VSE System Administration or DB2 Server for VM System Administration manual.
Packages that depend on an index are not invalidated when the index is reorganized. Therefore, using the REORGANIZE INDEX command instead of dropping and re-creating the index explicitly avoids the cost of the automatic preprocessing that reoccurs the next time an invalidated package is run. This benefit is realized whether the index you reorganize is valid or invalid.