DB2 objects management | Index management
DB2 Table management
After many changes to table data, logically sequential data may be on non-sequential physical data pages so that the database manager must perform additional read operations to access data. Additional read operations are also required if a significant number of rows have been deleted. In such a case, you might consider reorganizing the table to match the index and to reclaim space. You can reorganize the system catalog tables as well as database tables.
Because reorganizing a table usually takes more time than running statistics, you might execute RUNSTATS to refresh the current statistics for your data and rebind your applications. If refreshed statistics do not improve performance, reorganization might help.
Consider the following factors, which might indicate that you should reorganize a table:
- A high volume of insert, update, and delete activity on tables accessed by queries.
- Significant changes in the performance of queries that use an index with a high cluster ratio.
- Executing RUNSTATS to refresh statistical information does not improve performance.
- The REORGCHK command indicates a need to reorganize your table.
- The tradeoff between the cost of increasing degradation of query performance and the cost of reorganizing your table, which includes the CPU time, the duration time, and the reduced concurrency resulting from the REORG utility locking the table until the reorganization is complete (performance overhead).
Efficient table management can improve the performance of the entire database system, but before that, IBM recommendsed analyzing and determining how to performance reorganize tables, and when it is necessary to perform a table reorganization. The best approach for DB2 is to examine the statistics collected by RUNSTATS. From the information RUNSTATS gets, the statistics show the data distribution within tables, the number of used and empty pages, and RIDs marked deleted in index leaf pages. The statistics also provide information about prefetch efficiency. If you run RUNSTATS regularly and analyze the statistics over a period of time, you can get more indication about the performance trend of the database system.
xxxx