The performance of SQL statements that use indexes can be impaired after many updates, deletes, or inserts have been made. Generally, newly inserted rows cannot be placed in a physical sequence that is the same as the logical sequence defined by the index (unless you use clustered indexes). This means that the Database Manager must perform additional read operations to access the data, because logically sequential data may be on different physical data pages that are not sequential.
In general, reorganizing a table takes more time than running statistics. Performance may be improved sufficiently by obtaining the current statistics for your data and rebinding your applications, so try this first. If this does not improve performance, the data in the tables and indexes may not be arranged efficiently, so reorganization may help. The information in this section applies not only to reorganizing your own tables, but also to the system catalog tables which may also require reorganization.
The REORGCHK command returns information about the physical characteristics of a table, and whether or not it would be beneficial to reorganize that table. This command can be used through the command line processor. Refer to the Command Reference for more information, including how to interpret the command output.
The REORG utility optionally rearranges data into a physical sequence according to a specified index. REORG has an option to specify the order of rows in a table with an index, thereby clustering the table data according to the index and improving the CLUSTERRATIO or CLUSTERFACTOR statistics collected by the RUNSTATS utility. As a result, SQL statements requiring rows in the indexed order can be processed more efficiently. REORG also stores the tables more compactly by removing unused, empty space (though if you specified PCTFREE when you used ALTER TABLE, that space remains unused).
Do not use the REORG or REORGCHK commands with nicknames.
The REORG utility requires that all other applications that would normally be working against the affected table data and indexes be offline. You may have a work environment where you wish to limit the amount of time your applications cannot work against the data. In this environment, you might consider using the online index reorganization utility.
You may wish to consider the following factors to determine when to reorganize your table data:
To execute the REORG utility, you must have SYSADM, SYSMAINT, SYSCTRL or DBADM authority, or CONTROL privilege on the table.
The REORG utility uses temporary tables that can be significantly larger than the original table, if columns were added to a table, or a table has LOB columns. If these temporary tables are larger, the resulting permanent table, created by the REORG utility, will also be larger.
The REORG utility allows you to specify a temporary table space, which is used to create the temporary REORG table. If a temporary table space is not specified, the REORG utility will create the temporary REORG tables in the table space that contains the table being reorganized. The following guidelines can assist you in determining whether to use a temporary table space:
Remember that you may be reorganizing a table within a table space that is using greater than 4 KB pages (8 KB, 16 KB, or 32 KB) pages. During the reorganization, the temporary table space used during the reorganization must have the same size pages as the base table space.
If the REORG utility does not complete successfully, do not delete any temporary files, tables or table spaces. These files and tables are used by the Database Manager to roll back the changes made by the REORG utility, or to complete the reorganization, depending on how far the reorganization had progressed before the failure.
In a partitioned database, the REORG utility reorganizes data on each partition. If the utility fails on any partition, only the failing partition is rolled back. If you specify a directory path to store temporary tables, this path is extended by the Database Manager at each database partition. Therefore, if you specify a path that is shared by other database partitions, the temporary files are stored in different subdirectories (identified by node name) under this path.
An online reorganization is possible by providing a user-definable threshold for the maximum amount of free space on an index leaf page. When there is a deletion of an index key from a leaf page and the threshold is crossed, the neighboring index leaf pages are checked to determine if two leaf pages can be merged. If there is sufficient space on a page for a merge of two neighboring pages to take place, the merge occurs without having to take the database offline.
This online reorganization of the index is only possible with indexes created in this release and those following this release. Existing indexes requiring the ability to reorganize online in this fashion will have to be dropped and then re-created in order for the necessary internal changes to the index leaf pages. To turn on online index reorganization for a particular index, specify a MINPCTUSED value when the index is created. The MINPCTUSED value should be set to less than one hundred (100). This value becomes the reorganization threshold which is the percentage of used space on an index page that is the minimum acceptable value before attempting to merge the index leaf page with that of it's neighbor. The recommended value for MINPCTUSED is one that is less than 50 percent since the goal is to merge two neighboring index leaf pages. A value of zero for MINPCTUSED, which is also the default, disables online reorganization.
Index leaf pages that are freed for use following an online index reorganization are available for re-use. However, the freed pages are available only to other indexes in the same table. A full reorganization of the table will free up pages for other object when working with a DMS storage model; or will free up disk space when working with a SMS storage model.
Index non-leaf pages are not freed for use following an online index reorganization. However, a full reorganization of the table will make the index as small as possible. The leaf and non-leaf pages are reduced in number as well as the levels of the index.
To reduce the need for reorganizing a table, do the following after you have created the table:
Now you have a table with a clustering index. The clustering index, in conjunction with PCTFREE on table, will preserve the original sorted order. With sufficient space on pages, new data can be inserted on the correct pages thereby maintaining the clustering characteristics of the clustering index. If, as more data is inserted, and the pages of the table become full, records are appended to the end of the table, and the table gradually becomes unclustered.
It is recommended that you perform a REORG or a sort and LOAD after creating a clustering index. A clustering index attempts to maintain a particular order of data improving the CLUSTERRATIO or CLUSTERFACTOR statistics collected by the RUNSTATS utility.
The amount of free space to be left on each page during a REORG is determined by the PCTFREE value of the table. If this value has not been set, REORG will fill up the pages as the data is being reorganized.