DB2 進階 SELECT 應用：建立結果表、物化查詢表 (MQT) 與複製表結構

前言

DB2 的 CREATE TABLE ... AS (SELECT ...) 語法提供三種強大變體：定義式結果表、物化查詢表 (MQT)、複製表結構。本文詳細說明語法、用途與範例。

一、建立定義式結果表 (Definition Only)

僅依查詢結果的欄位定義建立空表，不複製資料：

CREATE TABLE new_table_name AS
    (SELECT * FROM source_table)
    DEFINITION ONLY;

• 適用於：快速建立與查詢結果結構相同的暫存表、階段表。
• 不包含索引、主鍵、約束、註解等中繼資料。

二、建立物化查詢表 (MQT, Materialized Query Table)

MQT 類似「預先計算並儲存的查詢結果」，查詢優化器可自動重導向查詢至 MQT 提升效能：

CREATE TABLE mqt_sales_summary AS
    (SELECT region, YEAR(sale_date) AS yr, SUM(amount) AS total_sales
     FROM sales
     GROUP BY region, YEAR(sale_date))
    DATA INITIALLY DEFERRED
    REFRESH DEFERRED;

-- 首次填入資料
REFRESH TABLE mqt_sales_summary;

關鍵選項：

• DATA INITIALLY DEFERRED：建立時不立即填入資料。
• REFRESH DEFERRED：手動 REFRESH TABLE 更新 (非即時)。
• REFRESH IMMEDIATE：基表異動時自動同步 (需定義 STAGING TABLE，限制較多)。
• 系統目錄 SYSCAT.TABLES 中 TYPE='S' 表示 MQT。

應用場景：資料倉庫彙總表、複雜連接預計算、報表加速。優化器在 CURRENT REFRESH AGE = ANY 時會自動考慮使用 MQT。

三、複製表結構 (LIKE 子句)

完整複製來源表的欄位定義、資料型別、預設值、NOT NULL、隱含約束 (不含索引、主鍵、外鍵、觸發器、權限)：

CREATE TABLE new_table_name LIKE source_table;

• 適用於：建立臨時表、測試表、分區表範本。
• 若需連同索引/約束一起複製，請使用 db2look 擷取 DDL 後執行。

四、比較總結

五、進階：MQT 查詢重導向驗證

-- 開啟 MQT 重導向
SET CURRENT REFRESH AGE = ANY;
SET CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION = ALL;

-- 執行查詢
EXPLAIN PLAN FOR
SELECT region, yr, total_sales FROM mqt_sales_summary WHERE yr = 2023;

-- 檢視執行計畫是否存取 MQT
SELECT * FROM TABLE(EXPLAIN_STREAM('EXPLAIN_INSTANCE', '...'));

結語

善用 CREATE TABLE ... AS 三種變體，能在「快速建表」、「報表預計算」、「結構複製」三大場景中事半功倍。MQT 是 DB2 資料倉庫效能優化的利器，建議配合 REFRESH DEFERRED 排程更新，並在查詢工作負載中開啟重導向。