DB2 從查詢結果創建表：CTAS 與物化查詢表 (MQT) 實戰

兩大核心場景

DB2 支持兩種「以查代建」模式：

1. 普通表複製 (CTAS 風格)：一次性複製數據與結構，後續互不影響。
2. 物化查詢表 (MQT)：定義基於查詢的表，支持 REFRESH TABLE 增量/全量同步源數據變更。

場景一：普通表複製 (CREATE TABLE ... AS ...)

語法：CREATE TABLE <新表> AS (SELECT ...) DATA INITIALLY DEFERRED REFRESH DEFERRED

關鍵字說明：

• DATA INITIALLY DEFERRED：創建時不填充數據（結構同步）。
• REFRESH DEFERRED：不自動同步源表變更，需手動 REFRESH TABLE。

實戰演示

-- 1. 準備源表
CREATE TABLE T1 (ID INT, NAME VARCHAR(12), ADDRESS VARCHAR(50));
INSERT INTO T1 VALUES (1, 'wjz', 'sdfsfds'), (2, 'lw', 'v4');

-- 2. 基於查詢創建新表 T3（僅複製 ID, NAME）
CREATE TABLE T3 AS (SELECT ID, NAME FROM T1)
DATA INITIALLY DEFERRED REFRESH DEFERRED;

-- 3. 首次填充數據
REFRESH TABLE T3;
-- 結果：T3 含 2 行數據

-- 4. 源表新增數據
INSERT INTO T1 VALUES (3, 'wxb', 'csk');

-- 5. 同步刷新
REFRESH TABLE T3;
-- 結果：T3 自動新增第 3 行，實現數據同步

場景二：物化查詢表 (MQT) 進階用法

若需更強的同步能力（如 REFRESH IMMEDIATE 觸發器級實時同步、查詢重寫優化），需創建真正的 MQT：

CREATE TABLE T3_MQT AS (SELECT ID, NAME FROM T1)
DATA INITIALLY DEFERRED REFRESH IMMEDIATE
ENABLE QUERY OPTIMIZATION
MAINTAINED BY SYSTEM;

差異點：

• REFRESH IMMEDIATE：源表提交事務時自動同步（需觸發器支持，有性能開銷）。
• ENABLE QUERY OPTIMIZATION：優化器可自動將查詢 T1 的 SQL 重寫為查詢 T3_MQT，大幅提升聚合/連接查詢速度。
• MAINTAINED BY SYSTEM：由數據庫維護數據一致性（對比 MAINTAINED BY USER 需手動維護）。

常見操作對比表

最佳實踐建議

• **只讀報表/歸檔**：優先用 REFRESH DEFERRED 普通表，維護成本低。
• **實時分析/高頻聚合**：評估寫入吞吐後考慮 REFRESH IMMEDIATE MQT，並啟用 ENABLE QUERY OPTIMIZATION。
• **大表構建**：創建時加 DATA INITIALLY DEFERRED，再並行 REFRESH TABLE 或分區並行載入。
• **索引策略**：MQT 可單獨建立針對查詢模式的索引，獨立於源表。