Reasons for reorganizing table spaces

Reorganize a table space for the following reasons:

• To reclaim space from dropped tables

• To eliminate pointer-overflow pairs

  When a row increases in size, it may no longer fit in the space reserved for it in the page. When this happens, Db2 moves the row to a new location and places in the original spot a pointer to this overflow location. This happens only if your rows contain variable columns that significantly increase in size as the result of SQL UPDATE statements, or if compression of the rows becomes less efficient after an update.

• To re-establish free space

  If the table space is ordered by a clustering index, then the amount of free space assumes more significance. Availability of free space can allow Db2 to maintain clustering. Conversely, a free space shortage can cause inserted rows to be placed out of clustering order.
  Reestablishing free space may be counter-productive. If an application has heavy inserts and inadequate free space, it may seem at first glance that reorganizing the table space may improve performance by reestablishing free space for subsequent inserts. Often the opposite is the case. Table space scans take longer because there are more pages to scan. In either case, the benefit or penalty is small.

• To collect segments in segmented table spaces

  Adjacency of segments is important if the table space is being scanned. However, avoiding table space scans is a much better idea than trying to make them more effective. Reorganizing an index may make it more attractive to Db2, thus providing a much more significant performance advantage.

• To re-establish clustering

  Clustering indexes provide a performance advantage. However, in order to be useful, Db2 must use the index. If the clustering ratio is too low because of a disorganized table space, Db2 might not use the index. However, by reorganizing the table space, you can increase the cluster ratio.

You might find that you should not reorganize table spaces, for the following reasons:

• Table space reorganizations cannot improve some access paths, such as index scans for non-clustering indexes and direct access to the table space via the index.
• Table space reorganizations primarily improve the efficiency of table space scans. However, table space scans should be avoided, especially with large tables.
• Table space reorganizations require more system resources than index reorganizations. Table space reorganizations reorganize every index in the table space, including indexes that do not need reorganizing.