DB2 數據庫核心操作百問精解 (第三輯)：概要表、臨時表、視圖、別名與序列實戰語法

概要表 (Summary Table / MQT) —— 物化視圖的 DB2 實現

創建語法全解

CREATE SUMMARY TABLE schema.summary_table_name AS (
  FULLSELECT  -- 定義數據來源的完整 SELECT 查詢
)
DATA INITIALLY DEFERRED      -- 不在創建時填充數據
REFRESH DEFERRED             -- 手動刷新模式 (非即時)
[IN tablespace_name]         -- 可選：指定表空間
[DISTRIBUTE BY HASH(col)]    -- DPF 環境分佈鍵
[ORGANIZE BY DIMENSIONS(...)] -- MDC 多維聚簇

實戰範例：銷售彙總表

CREATE SUMMARY TABLE sales.monthly_summary AS (
  SELECT 
    YEAR(sale_date) AS sales_year,
    MONTH(sale_date) AS sales_month,
    product_id,
    region_id,
    SUM(quantity) AS total_qty,
    SUM(amount) AS total_amt,
    COUNT(*) AS txn_count
  FROM sales.fact_sales
  WHERE sale_date >= '2020-01-01'
  GROUP BY YEAR(sale_date), MONTH(sale_date), product_id, region_id
)
DATA INITIALLY DEFERRED
REFRESH DEFERRED
IN USERSPACE1;

刷新與維護

-- 完全刷新 (截斷重載)
REFRESH TABLE sales.monthly_summary;

-- 增量刷新 (需定義 STAGING TABLE 並啟用 QUERY OPTIMIZATION)
-- ALTER SUMMARY TABLE ... ADD REPLICATED ... (V9.7+ 特性)

關鍵差異：Oracle Materialized View 支持 FAST REFRESH (增量)，DB2 REFRESH DEFERRED MQT 僅支持完全刷新。增量維護需配合 Staging Table + Trigger/Application 邏輯實現。

修改限制

• 不能直接 ALTER TABLE 修改結構 (列/索引/約束)
• 需 DROP 重建，或使用 ALTER SUMMARY TABLE ... SWITCH (V10.5+ 雙表切換零停機)
• 可創建索引優化查詢：CREATE INDEX idx_ms_ym ON sales.monthly_summary(sales_year, sales_month)

全局臨時表 (DGTT) —— 會話級高性能暫存

聲明語法詳解

DECLARE GLOBAL TEMPORARY TABLE session.temp_table_name 
  [LIKE source_table | AS (FULLSELECT) DEFINITION ONLY]
  [EXCLUDING IDENTITY COLUMN ATTRIBUTES]
  [INCLUDING COLUMN DEFAULTS]
  [ON COMMIT {PRESERVE | DELETE} ROWS]
  [NOT LOGGED]                    -- 必須指定，否則默認 LOGGED 性能極差
  [ON ROLLBACK {PRESERVE | DELETE} ROWS]

實戰範例：ETL 中間表

DECLARE GLOBAL TEMPORARY TABLE session.etl_staging AS (
  SELECT 
    customer_id,
    transaction_date,
    CAST(amount AS DECIMAL(15,2)) AS amount,
    'CRM' AS source_system
  FROM crm.raw_transactions
  WHERE process_flag = 'PENDING'
) DEFINITION ONLY
EXCLUDING IDENTITY COLUMN ATTRIBUTES
ON COMMIT PRESERVE ROWS
NOT LOGGED;

關鍵最佳實踐

• 前置條件：數據庫必須存在 用戶臨時表空間 (CREATE USER TEMPORARY TABLESPACE ...)
• NOT LOGGED 必加：避免重做日誌開銷，大批量操作性能提升 10x+
• 連接池環境必清理：調用 DROP TABLE SESSION.temp_name 或使用 ON COMMIT DELETE ROWS
• 統計信息：大表操作前執行 RUNSTATS ON TABLE SESSION.temp_name WITH DISTRIBUTION

視圖管理精要

創建與刪除

-- 標準視圖
CREATE VIEW sales.v_active_customers AS
SELECT c.cust_id, c.name, c.email, c.region
FROM sales.customers c
JOIN sales.orders o ON c.cust_id = o.cust_id
WHERE o.order_date >= CURRENT DATE - 1 YEAR
  AND c.status = 'ACTIVE'
WITH CHECK OPTION;  -- 限制 DML 必須符合視圖條件

-- 刪除
DROP VIEW sales.v_active_customers;

查看定義

-- 系統編目視圖
SELECT TEXT FROM SYSCAT.VIEWS 
WHERE VIEWSCHEMA = 'SALES' AND VIEWNAME = 'V_ACTIVE_CUSTOMERS';

-- 或使用 db2look 導出 DDL
db2look -d sample -z sales -t v_active_customers -e -o view_ddl.sql

別名 —— 位置透明化利器

創建語法

CREATE ALIAS schema.alias_name FOR target_object;
-- target_object 可為：TABLE, VIEW, NICKNAME, ALIAS (鏈式)

典型應用場景

• 跨 Schema 簡化：CREATE ALIAS app.emp FOR hr.employees; 應用層無感知 Schema 變更
• 聯邦數據庫透明化：CREATE ALIAS local.sales FOR oracle_nick.sales@ora_db;
• 藍綠部署切換：修改別名指向 v1_table / v2_table 實現零代碼變更切換

序列 —— 分布式唯一鍵生成器

完整創建語法

CREATE SEQUENCE schema.seq_name
  [AS {SMALLINT | INT | BIGINT | DECIMAL}]  -- 默認 INT
  [START WITH start_value]                  -- 默認 MINVALUE
  [INCREMENT BY increment_value]            -- 默認 1，可負數 (遞減)
  [MINVALUE min_value | NO MINVALUE]        -- 默認 1 / -9223372036854775808
  [MAXVALUE max_value | NO MAXVALUE]        -- 默認 9223372036854775807 / 無上限
  [CYCLE | NO CYCLE]                        -- 默認 NO CYCLE (達極值報錯)
  [CACHE cache_size | NO CACHE]             -- 默認 CACHE 20 (性能關鍵)
  [ORDER | NO ORDER]                        -- 默認 NO ORDER (RAC 環境 ORDER 保證全局有序)

實戰範例：訂單號生成器

CREATE SEQUENCE sales.order_seq
  AS BIGINT
  START WITH 1000000000
  INCREMENT BY 1
  NO MAXVALUE
  NO CYCLE
  CACHE 1000;  -- 高併發環境建議 CACHE 1000+，減少日誌同步

-- 使用
INSERT INTO sales.orders (order_id, ...) 
VALUES (NEXT VALUE FOR sales.order_seq, ...);

序列 vs Identity 列選擇指南

綜合實戰：報表系統架構

-- 1. 序列生成報表任務 ID
CREATE SEQUENCE rpt.task_seq AS BIGINT START WITH 1 CACHE 100;

-- 2. 臨時表暫存中間聚合
DECLARE GLOBAL TEMPORARY TABLE session.rpt_agg AS (
  SELECT task_id, region, product, SUM(sales) AS total
  FROM sales.fact
  WHERE date_key BETWEEN ? AND ?
  GROUP BY region, product
) DEFINITION ONLY NOT LOGGED ON COMMIT PRESERVE ROWS;

-- 3. 概要表預計算月度彙總 (每日刷新)
CREATE SUMMARY TABLE rpt.monthly_sales AS (...)
DATA INITIALLY DEFERRED REFRESH DEFERRED;

-- 4. 視圖統一對外接口
CREATE VIEW rpt.v_dashboard AS
SELECT * FROM rpt.monthly_sales
UNION ALL
SELECT * FROM session.rpt_agg;  -- 實時補齊當月數據

-- 5. 別名屏蔽底層變更
CREATE ALIAS app.rpt_data FOR rpt.v_dashboard;