善用 DB2 Design Advisor 打造高效能數據倉庫：從工作負載收集到 MQT/MDC/分區鍵優化建議

前言：Design Advisor 為何是數據倉庫的利器？

DB2 UDB V8.2 內建的 Design Advisor，專為解決數據倉庫（DW）獨特的物理設計挑戰而生：

• 特性協同優化：MQT（物化查詢表）、MDC（多維叢集）、DPF 雜湊分區三大 DW 導向特性必須協同，單純索引優化不足。
• TB 級存儲成本敏感：發現「收益不足以抵消成本」的索引並建議刪除，同樣重要。
• 變更代價高：調整物理設計意味著導出/導入海量資料，決策前必須仰賴工具評估。
• Ad-hoc 工作負載難建模：需邊監控邊調優，Design Advisor 支援增量迭代。
• 生態互補：Query Patroller 提供工作負載輸入；Cube Views 專司 OLAP 模式 MQT 設計。
• 邏輯視圖轉實體 MQT：發現可透過將視圖物化為 MQT 獲益的場景。

案例研究：升級至 V8.2 並啟用 MDC/MQT

場景：既有 DW 已使用 DPF 與 Query Patroller，欲利用 V8.2 新特性，願意停機重建表/索引實施建議。

步驟 1：收集代表性工作負載

從 Query Patroller 歷史查詢中抽樣，策略包括：

• 前 10% 最耗資源查詢
• 特定應用（如訪問核心事實表+維度表）的查詢
• 各終端用戶工具（Business Objects、SAS）查詢樣本
• 覆蓋各表的查詢樣本

建議樣本量 10–100 條，平衡分析品質與資源消耗。導出至檔案供 Design Advisor 讀取。

步驟 2：啟動 Design Advisor 並選擇優化特性

GUI 中勾選 Index、MQT、MDC、Partitioning 全部特性，確保發現全域最優解（特性間存在協同效應）。

漸進式替代策略：若難以解釋全特性建議，可單一應用/單一表/單一特性迭代優化；建議考慮 DEFERRED REFRESH MQT 更適配 ETL 窗口。

步驟 3：審查建議與報告

• 效能提升預估：案例中整體提升 60%。
• 具體建議：建立 3 個 MQT、CUSTOMER 表新增 2 個索引、LINEITEM 表變更雜湊分區鍵。
• 詳細報告（V8.2 新增）：顯示各查詢受益程度、未使用物件（可刪除以省空間）。
• ETL 影響評估：工作負載未含 ETL，實施前必須評估建議對 ETL 效能的影響。

步驟 4：實施前的準備工作

• 以命名慣例重命名系統產生的索引名。
• 確認物件目標表空間。
• 添加註釋說明建立原因。
• 善用 V8.2 Online Index Create 與 ALTER TABLE 線上變更，降低停機時間。

命令列模式：db2advis 完整參數實戰

db2advis -d TPCD -i tpch_queries.in -m IMCP -k LOW -l 700 -c DB2ADVIS_TBSP -f

參數詳解：

• -m IMCP：啟用 Index、MQT、MDC Convert、Partitioning 四大優化引擎（預設僅 Index）。
• -k LOW：工作負載壓縮等級 Low（分析更多查詢），預設 Medium。
• -l 700：新物件空間上限 700 MB（預設資料庫 20%）。
• -c DB2ADVIS_TBSP：DPF 環境下 MQT 建議所需臨時工作表空間（必填）。
• -f：輸出可執行 DDL 腳本。

輸出解讀範例

Cost of workload with all recommendations included [1306186] timerons
27 indexes in current solution
3 partitionings in current solution
8 MQTs in current solution

3 clustering dimensions in current solution
[12305400] timerons (without any recommendations)
[1042873] timerons (with current solution)
[91.53%] improvement

• 3 clustering dimensions：建議 3 個 MDC 維度（可跨表）。
• DDL 輸出順序：基本表（含 MDC/分區）→ MQT（新/保留/未用）→ 叢集索引 → 一般索引（新/保留/未用）。
• LINEITEM 表範例：建議新分區鍵 L_ORDERKEY 取代舊 L_PARTKEY；MDC 維度含生成欄 INT(L_SHIPDATE/7) 與既有欄 L_SHIPINSTRUCT。

結語

Design Advisor 能同時給出索引、MQT、MDC、DPF 分區鍵建議，並能分析 Ad-hoc 工作負載，是數據倉庫物理設計優化的不可或缺利器。