Used with tables without clustering index
|
|---|
These tables may become fragmented over time. DB2 lets you reorganize fragmented tables without shutting down the tablespace. Table reorgs are done within the existing tablespace, eliminating the need for excessive storage. Each moved row leaves behind a “reorg pointer” indicating its new location. This ensures rows are not read (or even reorged) more than once. Once the reorganization is complete, the pointers are removed. Refer to Figure 6.6.
No Staging Table With Staging Table
DATA INITIALLY DEFERRED REFRESH IMMEDIATE SET INTEGRITY FOR a1 IMMEDIATE CHECKED
CREATE TABLE s1 FOR a1 PROPAGATE IMMEDIATE SET INTEGRITY FOR s1 IMMEDIATE CHECKED . . .
| TRAN 1: | TRAN 1: | t1 | Refresh | |||
|---|---|---|---|---|---|---|
| Prunes s1 | ||||||
| INSERT INTO t1 | INSERT INTO t1 | |||||
| VALUES (1,2) | a1 | VALUES (1,2) | a1 | |||
| TRAN n: | TRAN n: | |||||
| INSERT INTO t1 | INSERT INTO t1 | |||||
| VALUES (1,100) | VALUES (1,100) | |||||
| Figure 6.5 |
|
|||||
| DB2 BI Fundamentals | 193 |
|---|
As shown in Figure 6.7, online table reorganization can be run in two modes:
■■ Reclustering mode reorganizes the clustering index and the underly- ing data together.
| TIME | ||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
 |
||||||||||||||
|
||||||||||||||
|
||
|---|---|---|
