Data that is physically well-organized can improve the
performance of access paths that rely on index or table scans, and
reduce the amount of disk storage used for the data.
About this task
Depending
on the number of changes, you might encounter performance degradations
for the following types of operations when your data becomes disorganized:
- Dynamic SQL queries.
- Updates and deletes. For example, delete operations sometimes result in pseudo-deleted
index entries, which can result in additional lock contention.
- ALTER statements (especially those that run concurrently).
- Concurrent REORG and LOAD utilities.
- Unloading a table that has had many changes before reorganization.
Procedure
To determine when to reorganize your data, use any of
the following approaches:
- Monitor statistics for increases in the following values:
- I/O operations
- Get page operations
- Processor consumption
When performance degrades to an unacceptable level, analyze
the statistics to develop your own rules for when to reorganize the
data in your particular environment.
- Invoke the DSNACCOX stored procedure
to get recommendations based on real-time statistics values.
- Query the catalog. Member DSNTESP of the SDSNSAMP data
set contains sample useful queries for determining whether to reorganize
- Use the REORG utility. The REORG utility embeds
the function of catalog queries. If a query returns a certain result
(you can use the default or supply your own), REORG either reorganizes
or does not reorganize. Optionally, you can have REORG run a report
instead of actually doing the reorganization. The following REORG
options invoke the catalog queries:
- The OFFPOSLIMIT and INDREFLIMIT options of REORG TABLESPACE
- The LEAFDISTLIMIT option of REORG INDEX
- Always reorganize your data after table definitions change. When ALTER TABLE statements are used to make any to the following
changes to table the table space is placed in advisory REORG-pending
(AREO*) status:
- Add columns
- Data type changes
- Changed column lengths
When an existing column is changed, the table
space is placed in AREO* status because the conversion to the new
definition is not immediate. Reorganizing the table space causes the
rows to be reloaded with the data converted to the new definition.
Until the table space is reorganized, the changes must be tracked
and applied as the data is accessed, which might degrade performance.