DB2 物化查詢表 (MQT) 完整指南：系統/用戶維護、刷新策略、STAGING 表與查詢重寫實戰

MQT 核心定義與分類

Materialized Query Table (MQT) 以查詢結果為基礎定義的表，物理存儲數據。分類：

• 系統維護 (System-maintained)：DB2 自動同步底層表變更。
  • REFRESH IMMEDIATE：事務提交時同步更新，要求底層表有唯一鍵，查詢無外連接/聚合限制。
  • REFRESH DEFERRED：手動 REFRESH TABLE 更新，查詢語法無限制。
• 用戶維護 (User-maintained)：REFRESH DEFERRED + MAINTAINED BY USER，應用負責 ETL 同步，DB2 不驗證一致性，查詢重寫需 CURRENT REFRESH AGE=ANY。

創建系統維護 IMMEDIATE MQT 範例

CONNECT TO SAMPLE;

ALTER TABLE EMPLOYEE ADD UNIQUE (EMPNO);
ALTER TABLE DEPARTMENT ADD UNIQUE (DEPTNO);

CREATE TABLE EMP AS (
    SELECT E.EMPNO, E.FIRSTNME, E.LASTNAME, E.PHONENO,
           D.DEPTNO, SUBSTR(D.DEPTNAME,1,12) AS DEPARTMENT, D.MGRNO
    FROM EMPLOYEE E, DEPARTMENT D
    WHERE E.WORKDEPT = D.DEPTNO
)
DATA INITIALLY DEFERRED
REFRESH IMMEDIATE;

SET INTEGRITY FOR EMP IMMEDIATE CHECKED NOT INCREMENTAL;

SELECT * FROM EMP;  -- 驗證數據已填充

關鍵子句解析

總結表 (AST) 與 STAGING 表

• AST：GROUP BY 聚合的特殊 MQT，適用報表預聚合。
• STAGING 表：記錄底層表增量變更 (INSERT/UPDATE/DELETE)，用於 REFRESH DEFERRED MQT 的增量刷新，避免全量重算。創建：CREATE STAGING TABLE FOR mqt_name。

查詢重寫驗證

-- 原始查詢
SELECT WORKDEPT, COUNT(*) FROM EMPLOYEE GROUP BY WORKDEPT;

-- 啟用重寫
SET CURRENT REFRESH AGE = ANY;
SET CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION = ALL;

-- 再次執行，EXPLAIN 顯示訪問 EMP MQT 而非 EMPLOYEE/DEPARTMENT

運維最佳實踐

• IMMEDIATE MQT 影響寫入性能，僅用於「讀多寫少、實時性要求高」場景。
• DEFERRED MQT 配合 STAGING 表 + 定時 REFRESH TABLE，適合數據倉庫日報表。
• 監控 SYSCAT.TABLES.REFRESH_TIME 確認刷新時效性。
• 大表 MQT 刷新並行化：SET CURRENT DEGREE = ANY 或 DB2_WORKLOAD=ANALYTICS。