Materialized Query Table (MQT) 是 DB2 查詢優化的核心武器：預先計算並儲存複雜查詢結果，優化器自動重寫 SQL 導向 MQT，實現數量級效能提升。本文詳細解析 MQT 類型、建立語法、維護策略、查詢重寫機制與實戰優化案例。
| 類型 | 定義特徵 | 維護方式 | 適用場景 |
|---|---|---|---|
| 系統維護 MQT (REFRESH IMMEDIATE) | 基於查詢定義，含 GROUP BY | 基礎表變更時自動同步更新 | 高頻讀取、低頻寫入、需強一致性 |
| 系統維護 MQT (REFRESH DEFERRED) | 基於查詢定義 | 手動/排程 REFRESH TABLE | 資料倉庫、彙總報表、可容忍延遲 |
| 用戶維護 MQT | 由應用程式負責填充數據 | 應用程式 INSERT/UPDATE/DELETE | 複雜邏輯無法用 SQL 表達、跨系統彙總 |
| 階段表 (Staging Table) | 記錄基礎表變更的輔助表 | 自動/手動維護 | 支援 REFRESH DEFERRED MQT 的增量刷新 |
關鍵區別： MQT = 物化視圖 (Materialized View)。視圖每次查詢重新計算，MQT 儲存實體數據。
-- 1. 連線範例資料庫
CONNECT TO SAMPLE;
-- 2. 前置條件：REFRESH IMMEDIATE 要求每個引用表有唯一鍵出現在 SELECT 列表
ALTER TABLE EMPLOYEE ADD UNIQUE (EMPNO);
ALTER TABLE DEPARTMENT ADD UNIQUE (DEPTNO);
-- 3. 建立 MQT：員工部門彙總視圖
CREATE TABLE EMP_DEPT_MQT AS (
SELECT
E.EMPNO,
E.FIRSTNME,
E.LASTNAME,
E.PHONENO,
D.DEPTNO,
SUBSTR(D.DEPTNAME, 1, 12) AS DEPARTMENT,
D.MGRNO
FROM EMPLOYEE E
INNER JOIN DEPARTMENT D ON E.WORKDEPT = D.DEPTNO
)
DATA INITIALLY DEFERRED -- 建立時不填充數據
REFRESH IMMEDIATE -- 基礎表變更自動同步
;
-- 4. 初始化數據並驗證完整性
SET INTEGRITY FOR EMP_DEPT_MQT IMMEDIATE CHECKED NOT INCREMENTAL;
語法關鍵字解析：
DATA INITIALLY DEFERRED：建立元數據不填數據，進入「檢查暫掛」狀態REFRESH IMMEDIATE：承諾基礎表變更時自動維護 (觸發器機制)SET INTEGRITY ... IMMEDIATE CHECKED NOT INCREMENTAL：全量檢查並填充初始數據NOT INCREMENTAL：對整表檢查 (而非增量)，初始化必用
-- 建立延遲刷新 MQT：月度銷售彙總
CREATE TABLE MONTHLY_SALES_MQT AS (
SELECT
YEAR(SALES_DATE) AS SALES_YEAR,
MONTH(SALES_DATE) AS SALES_MONTH,
PRODUCT_ID,
SUM(QUANTITY) AS TOTAL_QTY,
SUM(AMOUNT) AS TOTAL_AMT,
COUNT(*) AS TRANS_CNT
FROM SALES_FACT
GROUP BY YEAR(SALES_DATE), MONTH(SALES_DATE), PRODUCT_ID
)
DATA INITIALLY DEFERRED
REFRESH DEFERRED
;
-- 初始化
SET INTEGRITY FOR MONTHLY_SALES_MQT IMMEDIATE CHECKED NOT INCREMENTAL;
-- 定期刷新 (排程作業)
REFRESH TABLE MONTHLY_SALES_MQT;
-- 增量刷新 (需建立階段表)
CREATE TABLE MONTHLY_SALES_STG FOR MONTHLY_SALES_MQT PROPAGATE IMMEDIATE;
-- 基礎表變更自動記錄到階段表
REFRESH TABLE MONTHLY_SALES_MQT INCREMENTAL; -- 僅處理階段表變更
QUERY_HEURISTICS、CURRENT REFRESH AGE 允許使用延遲數據
-- 方法 1：EXPLAIN 計劃查看
EXPLAIN PLAN FOR
SELECT DEPTNO, COUNT(*) FROM EMPLOYEE JOIN DEPARTMENT ... GROUP BY DEPTNO;
db2exfmt -d SAMPLE -1 -o plan.txt
# 檢查輸出中是否出現 EMP_DEPT_MQT 表存取
-- 方法 2：當前查詢優化層級
SET CURRENT QUERY OPTIMIZATION 5; -- 預設 5，啟用 MQT 重寫
SET CURRENT REFRESH AGE ANY; -- 允許使用 DEFERRED MQT
SELECT
YEAR(s.sales_date) AS yr,
MONTH(s.sales_date) AS mon,
p.category,
SUM(s.amount) AS revenue,
COUNT(DISTINCT s.customer_id) AS cust_cnt
FROM sales_fact s
JOIN product_dim p ON s.product_id = p.product_id
JOIN customer_dim c ON s.customer_id = c.customer_id
WHERE s.sales_date >= '2023-01-01'
GROUP BY YEAR(s.sales_date), MONTH(s.sales_date), p.category
ORDER BY yr, mon, revenue DESC;
CREATE TABLE SALES_MONTHLY_CAT_MQT AS (
SELECT
YEAR(sales_date) AS yr,
MONTH(sales_date) AS mon,
category,
SUM(amount) AS revenue,
COUNT(DISTINCT customer_id) AS cust_cnt
FROM sales_fact s
JOIN product_dim p ON s.product_id = p.product_id
JOIN customer_dim c ON s.customer_id = c.customer_id
GROUP BY YEAR(sales_date), MONTH(sales_date), category
)
DATA INITIALLY DEFERRED REFRESH DEFERRED;
-- 應用程式 SQL 無需修改，優化器自動重寫導向 MQT
SELECT yr, mon, category, revenue, cust_cnt
FROM SALES_MONTHLY_CAT_MQT
WHERE yr >= 2023
ORDER BY yr, mon, revenue DESC;
REFRESH_TIME、CARDINALITY、QUERY_REWRITE_HIT_RATIO| 限制 | 影響 | 替代方案 |
|---|---|---|
| REFRESH IMMEDIATE 不允許直接 DML | 無法手工修正數據 | 改用 DEFERRED + 階段表增量刷新 |
| 基礎表結構變更需重建 MQT | DDL 變更導致 MQT 失效 | 變更管理流程納入 MQT 重建步驟 |
| 外部表/暫存表不可引用 | 跨資料庫查詢無法建立 MQT | 用戶維護 MQT + ETL 填充 |
| 遞歸查詢、非確定性函數不可用 | 限制 MQT 定義複雜度 | 應用層預計算 + 用戶維護 MQT |
MQT 是 DB2 「以空間換時間、以預計算換即時計算」的經典實踐。系統維護 REFRESH DEFERRED + 階段表增量刷新是資料倉庫場景的黃金組合：平衡了查詢效能、維護成本與數據新鮮度。建立「MQT 設計 → 驗證重寫 → 監控刷新」閉環流程，讓 MQT 持續產出價值。
