透過 DB2 Explain 工具深度解析 MQT 刷新機制：即時、延遲與 Staging 表三模式對比

引言：為什麼要理解 MQT 刷新內幕？

物化查詢表 (MQT) 以預先計算結果加速複雜查詢，廣泛應用於數據倉庫。然而，刷新策略直接決定資料鮮度與系統負載的平衡點。DB2 提供 REFRESH IMMEDIATE（即時增量）、REFRESH DEFERRED（手動全量）、REFRESH DEFERRED + Staging Table（手動增量）三種模式。本文利用 db2exfmt 格式化 Explain 輸出，從訪問計劃層面剖析各模式運作細節，助您依業務場景精準選型。

實驗環境與通用準備

• 平台：Linux x64，DB2 9.7
• 建立說明表：db2 -tvf ~/sqllib/misc/EXPLAIN.DDL
• 啟用 Explain 模式：db2 set current explain mode explain
• 格式化輸出：db2exfmt -d MYDB -1 -e -o <file>.exfmt

模式一：REFRESH IMMEDIATE（即時增量刷新）

建立測試物件

CREATE TABLE basetable (c1 INT NOT NULL PRIMARY KEY, c2 INT, c3 INT, c4 INT);
INSERT INTO basetable VALUES (1,1,1,1),(2,2,2,2),(3,3,3,3),(11,11,11,11);

CREATE TABLE mqttab AS (SELECT c1,c2,c3 FROM basetable WHERE c1 > 10)
DATA INITIALLY DEFERRED REFRESH IMMEDIATE;

SET INTEGRITY FOR mqttab IMMEDIATE CHECKED NOT INCREMENTAL;

INSERT 操作的訪問計劃

db2 "INSERT INTO basetable VALUES(12,12,12,12)"

計劃關鍵點：

• 單一 INSERT 語句產生 兩個 INSERT 算子：Operator #3 寫入基表，Operator #2 同步寫入 MQT
• MQT INSERT 的 Estimated number of rows: 1 確認為增量刷新
• 基表掃描 (TBSCAN) 僅讀取新增列，無全表掃描

註：Explain 模式下不實際寫入，故查詢 MQT 不會看到新資料。

UPDATE/DELETE 操作

計劃結構與 INSERT 同構：基表操作算子 + 對應 MQT 維護算子（UPDATE/DELETE），同樣為增量處理。完整計劃請參考下載檔 update1.exfmt、delete1.exfmt。

手動 REFRESH TABLE

db2 "REFRESH TABLE mqttab"

計劃極簡：僅含 TBSCAN → GENROW，無任何對 MQT 的實際操作。因即時模式下資料已同步，手動刷新為空操作。

模式二：REFRESH DEFERRED 無 Staging（手動全量重建）

建立測試物件

CREATE TABLE basetab2 (c1 INT NOT NULL PRIMARY KEY, c2 INT, c3 INT, c4 INT);
INSERT INTO basetab2 VALUES (1,1,1,1),(2,2,2,2),(3,3,3,3),(11,11,11,11);

CREATE TABLE mqttab2 AS (SELECT c1,c2,c3 FROM basetab2 WHERE c1 > 10)
DATA INITIALLY DEFERRED REFRESH DEFERRED;

SET INTEGRITY FOR mqttab2 IMMEDIATE CHECKED NOT INCREMENTAL;

基表 DML 操作

INSERT/UPDATE/DELETE 計劃均僅含基表操作，完全無 MQT 相關算子，驗證「延遲刷新不即時維護」特性。

手動 REFRESH TABLE 全量重建

INSERT INTO basetab2 VALUES (12,12,12,12),(13,12,12,12),(14,12,12,12),(15,12,12,12);
UPDATE basetab2 SET c2=100 WHERE c1=11;
RUNSTATS ON TABLE hqy.basetab2;
db2 "REFRESH TABLE mqttab2"

計劃特徵：

• 包含 INSERT (Operator #6) + DELETE (Operator #9) 兩大 MQT 維護算子
• INSERT 來源為 FETCH → TBSCAN basetab2（全表掃描基表並過濾）
• DELETE 來源為 TBSCAN mqttab2（掃描舊 MQT 找出失效行）
• 無 UPDATE 算子：全量模式以「刪舊增新」替代就地更新

模式三：REFRESH DEFERRED + Staging Table（手動增量刷新）

建立測試物件

CREATE TABLE basetab3 (c1 INT NOT NULL PRIMARY KEY, c2 INT, c3 INT, c4 INT);
INSERT INTO basetab3 VALUES (1,1,1,1),(2,2,2,2),(3,3,3,3),(11,11,11,11);

CREATE TABLE mqttab3 AS (SELECT c1,c2,c3 FROM basetab3 WHERE c1 > 10)
DATA INITIALLY DEFERRED REFRESH DEFERRED;

CREATE TABLE mqttab3_stg FOR mqttab3 PROPAGATE IMMEDIATE;

SET INTEGRITY FOR mqttab3 MATERIALIZED QUERY IMMEDIATE UNCHECKED;
SET INTEGRITY FOR mqttab3_stg STAGING IMMEDIATE UNCHECKED;

Staging 表結構自動包含 MQT 全欄位 + GLOBALTRANSID、GLOBALTRANSTIME、OPERATIONTYPE 三個控制欄，完整捕獲基表變更。

基表 DML 操作：同步寫入 Staging

INSERT/UPDATE/DELETE 計劃與即時模式幾乎相同，差別僅在：

• 目標表從 MQT 改為 Staging 表 (MQTTAB3_STG)
• 基表操作同時觸發 Staging 表對應的 INSERT/UPDATE/DELETE

手動 REFRESH TABLE：純採 Staging 內容增量合併

INSERT INTO basetab3 VALUES (12,12,12,12),(13,12,12,12),(14,12,12,12),(15,12,12,12);
UPDATE basetab3 SET c2=100 WHERE c1=11;
RUNSTATS ON TABLE hqy.basetab3;
db2 "REFRESH TABLE mqttab3"

計劃極度複雜（見 refresh3.exfmt），核心邏輯：

1. DELETE Staging 資料 (Operator #18)：讀取並清空 Staging 表
2. NLJOIN 合併 (Operator #13)：Staging 變更與現有 MQT 做 Nested Loop Join，結果寫入 TEMP 表
3. 三階段 MQT 維護：依 TEMP 表依序執行 UPDATE (#9) → DELETE (#8) → INSERT (#6)
4. 全程零基表訪問：完全基於 Staging 表完成增量刷新

三模式關鍵差異總覽

結語與延伸建議

通過 Explain 計劃對比，我們清晰看到三種機制的本質差異。實際選型時請綜合考量：

• 基表寫入頻率與量級
• 查詢對資料實時性的容忍度
• 維護窗口長度與系統資源預算
• MQT 定義複雜度 (含 GROUP BY、JOIN 等聚合邏輯)

進階探索方向：含聚合函數的 MQT 刷新計劃、LOAD 操作對各模式的影響、多維度 MQT 的 Staging 表設計優化。