There are two approaches to table reorganization: classic
reorganization (offline) and inplace reorganization (online).
Offline reorganization is the default behavior. To specify an online
reorganization operation, use the INPLACE option on the REORG
TABLE command.
An alternative approach to inplace reorganization, using online
table move stored procedures, is also available. See "Moving tables
online by using the ADMIN_MOVE_TABLE procedure".
Each approach has its advantages and drawbacks, which are summarized
below. When choosing a reorganization method, consider which approach
offers advantages that align with your priorities. For example, if
recoverability in case of failure is more important than performance,
online reorganization might be preferable.
Advantages of offline reorganization
This
approach offers:
- The fastest table reorganization operations, especially if large
object (LOB) or long field data is not included
- Perfectly clustered tables and indexes upon completion
- Indexes that are automatically rebuilt after a table has been
reorganized; there is no separate step for rebuilding indexes
- The use of a temporary table space for building a shadow copy;
this reduces the space requirements for the table space that contains
the target table or index
- The use of an index other than the clustering index to re-cluster
the data
Disadvantages of offline reorganization
This
approach is characterized by:
- Limited table access; read access only during the sort and build
phase of a reorg operation
- A large space requirement for the shadow copy of the table that
is being reorganized
- Less control over the reorg process; an offline reorg operation
cannot be paused and restarted
Advantages of online reorganization
This
approach offers:
- Full table access, except during the truncation phase of a reorg
operation
- More control over the reorg process, which runs asynchronously
in the background, and which can be paused, resumed, or stopped; for
example, you can pause an in-progress reorg operation if a large number
of update or delete operations are running against the table
- A recoverable process in the event of a failure
- A reduced requirement for working storage, because a table is
processed incrementally
- Immediate benefits of reorganization, even before a reorg operation
completes
Disadvantages of online reorganization
This
approach is characterized by:
- Imperfect data or index clustering, depending on the type of transactions
that access the table during a reorg operation
- Poorer performance than an offline reorg operation
- Potentially high logging requirements, depending on the number
of rows being moved, the number of indexes that are defined on the
table, and the size of those indexes
- A potential need for subsequent index reorganization, because
indexes are maintained, not rebuilt
Table 1. Comparison of online and offline reorganizationCharacteristic |
Offline reorganization |
Online reorganization |
Performance |
Fast |
Slow |
Clustering factor of data at completion |
Good |
Not perfectly clustered |
Concurrency (access to the table) |
Ranges from no access to read-only |
Ranges from read-only to full access |
Data storage space requirement |
Significant |
Not significant |
Logging storage space requirement |
Not significant |
Could be significant |
User control (ability to pause, restart process) |
Less control |
More control |
Recoverability |
Not recoverable |
Recoverable |
Index rebuilding |
Done |
Not done |
Supported for all types of tables |
Yes |
No |
Ability to specify an index other than the clustering
index |
Yes |
No |
Use of a temporary table space |
Yes |
No |
Table 2. Table types that are supported for online and
offline reorganizationTable type |
Offline reorganization supported |
Online reorganization supported |
Multidimensional clustering tables (MDC) |
Yes1 |
No |
Range-clustered tables (RCT) |
No2 |
No |
Append mode tables |
Yes |
No3 |
Tables with long field or large object (LOB)
data |
Yes4 |
Yes5 |
System catalog tables: - SYSIBM.SYSCODEPROPERTIES
- SYSIBM.SYSDATATYPES
- SYSIBM.SYSNODEGROUPS
- SYSIBM.SYSROUTINES
- SYSIBM.SYSSEQUENCES
- SYSIBM.SYSTABLES
- SYSIBM.SYSVARIABLES
|
Yes |
No |
Notes: - Because clustering is automatically maintained through MDC block
indexes, reorganization of an MDC table involves space reclamation
only. No indexes can be specified.
- The range area of an RCT always remains clustered.
- Online reorganization can be performed after append mode is disabled.
- Reorganizing long field or large object (LOB) data can take a
significant amount of time, and does not improve query performance;
it should only be done for space reclamation.
- Online table reorganization does not reorganize the
LONG/LOB data, but reorganizes the other columns.
|
Monitoring the progress of table reorganization
Information
about the progress of a current table reorg operation is written to
the history file. The history file contains a record for each reorganization
event. To view this file, execute the LIST HISTORY command
against the database that contains the table being reorganized.
You
can also use table snapshots to monitor the progress of table reorg
operations. Table reorganization monitoring data is recorded, regardless
of the setting for the database system monitor table switch.
If
an error occurs, an SQLCA message is written to the history file.
In the case of an inplace table reorg operation, the status is recorded
as PAUSED.