DB2 物化查詢表 (MQT) 刷新機制深度解析：即時、延遲與 Staging Table 三模式實測

MQT 刷新分類

實測環境

• Linux x64, DB2 9.7, 資料庫 MYDB。
• 建立說明表：db2 -tvf ~/sqllib/misc/EXPLAIN.DDL。
• 啟用解釋：db2 set current explain mode explain，db2exfmt 產出訪問計劃。

模式一：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 訪問計劃：同時出現 INSERT INTO basetable (Op#3) 與 INSERT INTO mqttab (Op#2)，Estimated rows: 1 確認增量。

手動 REFRESH TABLE mqttab：計劃僅 GENROW → TBSCAN，無實際操作 —— 因同步已保證最新。

模式二：DEFERRED 無 Staging (全量刷新)

CREATE TABLE basetab2 ...;  -- 同結構
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：計劃僅基表操作，MQT 無動作。

手動 REFRESH TABLE mqttab2：計劃含 TBSCAN basetab2 → FETCH → INSERT mqttab2 及 TBSCAN mqttab2 → DELETE，全掃基表重建，無 UPDATE 算子 (以 DELETE+INSERT 替代)。

模式三：DEFERRED + Staging Table (增量刷新)

CREATE TABLE basetab3 ...;
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。

基表 INSERT：計劃同步寫入 mqttab3_stg (Op#3)，不觸碰 MQT 本體。

手動 REFRESH TABLE mqttab3：極複雜計劃，核心流程：

1. DELETE FROM mqttab3_stg (讀取並清空 Staging)
2. NLJOIN Staging 與 MQT 合併 → TEMP
3. 依 TEMP 依序 UPDATE → DELETE → INSERT 作用於 MQT

選型建議

• 高頻寫入、強一致性需求 → IMMEDIATE。
• 大表、批次維護窗口、可容忍延遲 → DEFERRED + Staging (最佳增量效能)。
• 小表、極簡場景 → DEFERRED 無 Staging (全量亦可接受)。

後續可擴展驗證：複雜 MQT (GROUP BY/COUNT)、LOAD 載入、異構複寫等場景下的刷新行為。