DB2 SELECT 進階用法實戰：建立結果表、MQT 與複製結構

前言

DB2 的 CREATE TABLE ... AS (SELECT ...) 語法家族功能強大，不僅能一次完成「查詢 + 建表」動作，更支援唯讀定義表、物化查詢表 (MQT) 及純結構複製三大進階模式。本文以實戰範例逐一解析，助您靈活運用。

1. 建立結果表 (Result Table)

將查詢結果直接持久化為實體表，包含資料與結構：

CREATE TABLE new_table_name AS
(SELECT * FROM source_table)
WITH DATA;

• WITH DATA (預設)：複製資料與結構，獨立於來源表。
• DEFINITION ONLY：僅複製結構 (欄位、資料型態、Nullable)，不帶資料，等同 CREATE TABLE ... LIKE ...。

適用場景：臨時分析表、資料備份、ETL 中繼站。

2. 建立物化查詢表 (Materialized Query Table, MQT)

MQT 是預先計算並儲存查詢結果的特殊表，查詢優化器可自動重導查詢至 MQT，大幅加速彙總報表：

CREATE TABLE sales_summary AS
(SELECT region, SUM(amount) AS total_sales
 FROM sales_fact
 GROUP BY region)
DATA INITIALLY DEFERRED REFRESH DEFERRED;

關鍵字解析：

• DATA INITIALLY DEFERRED：建立時不立即填入資料。
• REFRESH DEFERRED：手動或排程刷新 (REFRESH TABLE sales_summary)，適合資料變動頻率低、查詢頻率高的彙總表。
• 亦可指定 REFRESH IMMEDIATE，資料異動即時同步，但會增加寫入負擔。

注意：MQT 在系統目錄中 TYPE 顯示為 'S' (Summary Table)，但可像一般表被 SELECT、JOIN、建立索引。

3. 僅複製表結構 (LIKE 子句)

最輕量的結構複製方式，不涉及 SELECT 查詢計畫：

CREATE TABLE new_table_name LIKE source_table;

• 複製欄位定義、主鍵、唯一索引、檢查約束、預設值。
• 不複製：外鍵、觸發器、權限、MQT 定義、資料分區細節。

適用場景：建立暫存表、測試環境同步結構、分區表模板。

三種模式快速比較

最佳實踐建議

1. 大量資料初始化 MQT 時，先 DATA INITIALLY DEFERRED 再 REFRESH TABLE，避免長鎖定。
2. MQT 刷新後執行 RUNSTATS，確保優化器有最新統計資訊。
3. 僅需結構時優先用 LIKE，語意最清晰、效能最好。

善用這三大語法，能讓 DB2 開發與維運更具彈性與效能。