您现在的位置：程式碼之家 >> 數據庫知識 > >> DB2 資料庫 >> DB2 教學

DB2 物化查詢表（MQT）完整指南：建立、維護與查詢重寫實戰範例

編輯：DB2 教學

什麼是 MQT（Materialized Query Table）？

MQT 是基於查詢結果定義的實體表，資料來源於一個或多個基礎表。可視為物化視圖：一般視圖每次引用即時執行查詢，MQT 則預先計算並持久化結果，查詢優化器可自動重寫 SQL 導向 MQT，大幅提升複雜查詢效能。

MQT 維護模式

模式	維護者	刷新時機	允許直接 DML
SYSTEM MAINTAINED + REFRESH IMMEDIATE	系統	基礎表每次 INSERT/UPDATE/DELETE 即時同步	否
SYSTEM MAINTAINED + REFRESH DEFERRED	系統	手動執行 `REFRESH TABLE` 或 `SET INTEGRITY`	否
USER MAINTAINED	使用者	使用者自行 INSERT/UPDATE/DELETE	是

實戰範例：建立 REFRESH IMMEDIATE 系統維護 MQT

場景：SAMPLE 資料庫中，結合 EMPLOYEE 與 DEPARTMENT 建立員工-部門明細 MQT EMP。

前置條件：基礎表需有唯一鍵並出現在 SELECT 列表

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

建立 MQT

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;

DATA INITIALLY DEFERRED：建表時不填入資料。
REFRESH IMMEDIATE：承諾系統即時維護。

初始化並驗證

SET INTEGRITY FOR EMP IMMEDIATE CHECKED NOT INCREMENTAL;

IMMEDIATE CHECKED：依定義查詢檢查並刷新資料。
NOT INCREMENTAL：全表完整性檢查（首次必須）。

查詢重寫驗證

建立完成後，執行類似定義的查詢：

SELECT EMPNO, FIRSTNME, LASTNAME, DEPARTMENT
FROM EMPLOYEE E, DEPARTMENT D
WHERE E.WORKDEPT = D.DEPTNO;

優化器將自動重寫導向 EMP MQT，避免 Join 與函數計算。可用 EXPLAIN 驗證存取計畫是否掃描 EMP 而非基礎表。

進階應用：Staging 表支援增量刷新

對於 REFRESH DEFERRED MQT，建立 Staging 表可僅處理變更資料：

CREATE TABLE EMP_STAGING FOR EMP PROPAGATE IMMEDIATE;

基礎表變更自動記錄於 Staging 表，執行 REFRESH TABLE EMP INCREMENTAL 時僅套用增量，大幅縮短刷新窗口。

使用建議與注意事項

適用場景：複雜聚合、多表 Join、頻繁執行、資料變更頻率可接受的報表/分析查詢。
權衡：儲存空間、維護開銷（IMMEDIATE 影響基礎表寫入效能）、Staging 表額外空間。
監控：定期檢查 SYSCAT.TABLES 中 REFRESH_TIME、STATUS（CHECK PENDING 等）。
版本相容：DB2 9.7+ 引入更豐富的 MQT 功能（如外部表、Column-organized 表支援）。

總結

MQT 是 DB2 查詢加速的利器，透過預計算與自動查詢重寫，將複雜分析查詢從「秒級/分級」降至「毫秒級」。掌握 IMMEDIATE vs DEFERRED、系統 vs 使用者維護、Staging 增量刷新 三大維度組合，並依業務變更頻率與查詢模式選擇最適模式，即可在資料倉儲、BI 報表、操作分析等場景獲得極佳投報比。