DB2 物化查詢表 (MQT) 完整指南：概念、優勢與實作範例

DB2 物化查詢表 (Materialized Query Table, MQT) 概述

DB2 的物化查詢表 (MQT) 是一種基於查詢結果定義的資料表。MQT 中的資料來源於定義它的一張或多張基礎表。利用 MQT 可以顯著提升查詢效能，特別是針對複雜查詢的優化效果明顯。

MQT 與一般視圖的差異

資料庫視圖與 MQT 皆以查詢定義。每當引用視圖時，其基礎查詢都會即時執行；但 MQT 會實際儲存查詢結果資料。查詢優化器若判斷某查詢 (或片段) 可由 MQT 解決，會自動重寫查詢以利用 MQT，從而避免重複掃描基礎表與複雜運算。

MQT 維護模式

• SYSTEM MAINTAINED：由資料庫系統自動維護。建立時可指定 REFRESH IMMEDIATE (基礎表異動即時同步) 或 REFRESH DEFERRED (需手動執行 REFRESH TABLE 更新)。
• USER MAINTAINED：由使用者自行負責資料同步。

系統維護的 MQT (無論 IMMEDIATE 或 DEFERRED) 皆不允許直接對其執行 INSERT/UPDATE/DELETE。但 REFRESH IMMEDIATE 型 MQT 會隨基礎表異動自動更新。

關鍵子句說明

DATA INITIALLY DEFERRED 表示執行 CREATE TABLE 時不立即填入資料，需後續手動刷新或等待下次自動維護週期。

實作範例：建立系統維護、即時刷新的 MQT

-- 1. 建立基礎表
CREATE TABLE T (
  ID INTEGER NOT NULL,
  COL1 VARCHAR(128),
  COL2 VARCHAR(128),
  COL3 VARCHAR(128),
  COL4 VARCHAR(128),
  COL5 VARCHAR(128),
  PRIMARY KEY (ID)
) ORGANIZE BY ROW;

-- 2. 建立 MQT：僅投影 ID, COL1, COL2, COL3
CREATE TABLE T_MQT (ID, COL1, COL2, COL3) AS (
  SELECT ID, COL1, COL2, COL3 FROM T
) DATA INITIALLY DEFERRED REFRESH IMMEDIATE MAINTAINED BY SYSTEM;

-- 3. 啟用完整性檢查，使 MQT 可被查詢使用
SET INTEGRITY FOR T_MQT IMMEDIATE CHECKED FULL ACCESS;

資料驗證

-- 寫入基礎表資料
INSERT INTO T (ID, COL1, COL2, COL3, COL4, COL5) VALUES (1, 'col1', 'col2', 'col3', 'col4', 'col5');
INSERT INTO T (ID, COL1, COL2, COL3, COL4, COL5) VALUES (2, 'col1', 'col2', 'col3', 'col4', 'col5');

-- 查詢基礎表與 MQT
SELECT * FROM T;
SELECT * FROM T_MQT;

此時 T_MQT 已自動同步基礎表的異動，查詢 T_MQT 即可獲得與基礎表一致的投影資料，且效能遠優於直接查詢 T 後再投影。

適用場景與建議

• 頻繁執行的複雜彙總、聯結查詢 (如報表、OLAP)。
• 基礎表資料量大、查詢模式固定。
• 若對資料即時性要求極高，選 REFRESH IMMEDIATE；可容忍延遲則用 REFRESH DEFERRED 配合排程刷新以降低寫入負擔。