提升商業智能環境中 DB2 查詢效能的六大策略 (下)

方法 1：定義參考完整性約束消除冗餘聯結

在星型結構中，事實表 SALES_FACT 的外鍵 (DATE_ID, PRODUCT_ID, STORE_ID) 指向對應維度表主鍵。建立外鍵約束後，優化器可識別無損聯結並移除。例如查詢未引用 PRODUCT_DIM 非鍵值欄位時，加上外鍵即可消除該聯結。

ALTER TABLE SALES_FACT ADD CONSTRAINT DATE_DIM_FK FOREIGN KEY (DATE_ID) REFERENCES DATE_DIM;
ALTER TABLE SALES_FACT ADD CONSTRAINT STORE_DIM_FK FOREIGN KEY (STORE_ID) REFERENCES STORE_DIM;
ALTER TABLE SALES_FACT ADD CONSTRAINT PRODUCT_DIM_FK FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCT_DIM;
CREATE INDEX IND_DATE_STORE ON SALES_FACT (DATE_ID, STORE_ID);

執行 RUNSTATS 收集統計資訊，再用 db2exfmt 檢視存取計畫，確認 PRODUCT_DIM 已被移除。效能測試使用 db2batch，記錄編譯/執行/擷取時間。

練習：若 DML 效能敏感，可將外鍵定義為 NOT ENFORCED 僅供優化器參考，避免執行期檢查開銷。

方法 2：使用重複 MQT 實現維度表共置聯結

原架構中維度表分佈於不同分區，跨分區傳輸資料造成開銷。建立重複物化查詢表 (MQT) 將維度表複寫至事實表所在分區組 (FACT_SMS)，優化器即可選擇共置聯結 (collocated join) 避免資料移動。

CREATE TABLE store_dim_rep AS (SELECT * FROM store_dim) DATA INITIALLY DEFERRED REFRESH DEFERRED IN FACT_SMS REPLICATED;
CREATE TABLE product_dim_rep AS (SELECT * FROM product_dim) DATA INITIALLY DEFERRED REFRESH DEFERRED IN FACT_SMS REPLICATED;
CREATE TABLE date_dim_rep AS (SELECT * FROM date_dim) DATA INITIALLY DEFERRED REFRESH DEFERRED IN FACT_SMS REPLICATED;
REFRESH TABLE store_dim_rep; REFRESH TABLE product_dim_rep; REFRESH TABLE date_dim_rep;
-- 建立索引並 RUNSTATS

設定資料庫配置 dft_refresh_age = ANY 讓優化器考慮 MQT。檢視存取計畫確認改用 *_rep 表，無表佇列 (TQ) 操作符。

練習：嘗試壓縮基本維度表或重複 MQT，觀察優化器選擇變化。

方法 3：多維叢集 (MDC) 事實表搭配重複 MQT

MDC 依指定維度自動叢集資料。以 (DATE_ID, STORE_ID) 為維度，計算 EXTENTSIZE：

• 唯一組合數 (CELL_COUNT) = 73,097
• 平均每單元列數 (RPC) = 298
• 平均列長 126 bytes → 每頁 29 列 → 需 12 頁 → EXTENTSIZE = 12

CREATE REGULAR TABLESPACE FACT_SMS_MDC_EX ... EXTENTSIZE 12 PREFETCHSIZE 24;
CREATE TABLE SALES_FACT_MDC_1 (...) ORGANIZE BY (DATE_ID, STORE_ID) IN FACT_SMS_MDC_EX;
INSERT INTO SALES_FACT_MDC_1 SELECT * FROM SALES_FACT ORDER BY DATE_ID, STORE_ID;
-- 加外鍵、RUNSTATS

修改查詢引用新表，檢視計畫確認使用塊索引，測試效能。

方法 4：表分區搭配重複 MQT

DB2 9 新功能：依 DATE_ID 按月分區。先建單一分區 (1 月)，再逐月 ATTACH 分區。分區消除可避免掃描無關分區。

CREATE TABLE SALES_FACT_TPART (...) PARTITION BY (DATE_ID) (PART Jan STARTING '1/1/2006' ENDING '1/31/2006');
-- 建立各月暫存表、載入資料、ATTACH PARTITION、SET INTEGRITY

存取計畫中 DPLSTPRT 顯示僅存取分區 10 (11 月)，DPNUMPRT=1 確認分區消除生效。測試效能並嘗試加索引優化。

方法 5：表分區 + MDC + 重複 MQT

結合方法 3 與 4：建立分區 MDC 表，同時享受分區消除與塊索引雙重優勢。

CREATE TABLE SALES_FACT_TPART_MDC (...) PARTITION BY RANGE (DATE_ID) (...) ORGANIZE BY (DATE_ID, STORE_ID);
INSERT FROM SALES_FACT_MDC_1; RUNSTATS;

檢視計畫確認同時使用分區表與塊索引，測試效能。

方法 6：使用 MQT 預先計算彙總結果

建立彙總層級 MQT，包含查詢所需的 GROUP BY 欄位與彙總值，查詢直接掃描 MQT 並聯結兩個 MQT 實例，極大減少運算量。

CREATE TABLE MQT2 AS (SELECT D.MONTH, S.STORE_ID, S.DISTRICT, S.REGION, SUM(F1.QUANTITY*F1.PRICE) AS AMOUNT, F1.DATE_ID
FROM SALES_FACT F1, DATE_DIM D, PRODUCT_DIM P, STORE_DIM S
WHERE F1.DATE_ID=D.DATE_ID AND F1.PRODUCT_ID=P.PRODUCT_ID AND F1.STORE_ID=S.STORE_ID AND P.MODEL LIKE '%MODEL%'
GROUP BY S.STORE_ID, S.DISTRICT, S.REGION, D.MONTH, F1.DATE_ID)
DATA INITIALLY DEFERRED REFRESH DEFERRED IN FACT_SMS;
REFRESH TABLE MQT2;
CREATE INDEX MQT2_IND3 ON MQT2 (MONTH, DATE_ID);
CREATE INDEX MQT2_IND4 ON MQT2 (DATE_ID, STORE_ID, DISTRICT, REGION, MONTH, AMOUNT);

設定 dft_refresh_age = ANY，檢視計畫確認使用 MQT，測試效能。

效能比較總結

預先彙總 MQT 效能提升最顯著；MDC 與 表分區+MDC 組合次之。重複 MQT 在邏輯分區環境提升有限，物理多機器下效果更明顯。純表分區若無索引反而較慢，屬預期現象。

結語

本文展示六種 BI 查詢優化策略，實際效果取決於資料分佈、硬體架構與工作負載。建議在測試環境逐一驗證，並完成文中練習深化理解。