DB2 優化器 JOIN 基數估算深度解析（二）：統計資訊驅動的成本模型實測驗證

前言：從理論公式到實際執行計劃的驗證之旅

接續上篇理論推導，本文通過構建受控測試環境（已知資料分布、已收集統計資訊），實測 DB2 優化器對 JOIN 結果集基數的估算值，並與理論公式對比，揭示統計資訊（COLCARD、NUMNULLS、HIGH2KEY/LOW2KEY 等）如何驅動成本模型決策。

一、測試環境建立：精確控制的資料分布

表結構與資料特徵

DDL 與資料生成腳本（關鍵片段）

-- T1
CREATE TABLE db2inst1.t1 (filter1 INT, join1 INT, v1 INT, padding1 CHAR(1))
NOT LOGGED INITIALLY;

INSERT INTO db2inst1.t1 (filter1, join1, v1, padding1)
WITH TEMP (counter, filter1, join1, v1, padding1) AS (
  VALUES (0, MOD(INT(RAND()*1000),25), MOD(INT(RAND()*1000),30), 0, 'A')
  UNION ALL
  SELECT counter+1, MOD(INT(RAND()*1000),25), MOD(INT(RAND()*1000),30), counter+1, 'A'
  FROM TEMP WHERE counter+1 < 10000
) SELECT filter1, join1, v1, padding1 FROM TEMP;

-- T2 (join2 範圍 0~39, filter2 範圍 0~49)
CREATE TABLE db2inst1.t2 (filter2 INT, join2 INT, v2 INT, padding2 CHAR(1))
NOT LOGGED INITIALLY;
-- 同樣遞歸 CTE 生成 10000 行...

統計資訊收集（僅基本統計，無分布統計）

db2 "runstats on table db2inst1.t1 on all columns"
db2 "runstats on table db2inst1.t2 on all columns"

db2look 提取的核心統計值

二、測試查詢：驗證 JOIN 基數估算公式

SELECT COUNT(*)
FROM (
  SELECT t1.v1, t2.v2
  FROM t1, t2
  WHERE t1.filter1 = 1
    AND t2.join2 = t1.join1
    AND t2.filter2 = 1
) AS b;

邏輯步驟分解

1. T1 過濾： filter1 = 1，選擇率 = 1/25 = 0.04 → 預估 400 行
2. T2 過濾： filter2 = 1，選擇率 = 1/50 = 0.02 → 預估 200 行
3. JOIN 條件： t2.join2 = t1.join1
4. JOIN 選擇率 (無分布統計時)： 1 / MAX(COLCARD(join1), COLCARD(join2)) = 1 / MAX(30, 40) = 1/40
5. 理論 JOIN 結果基數： 400 × 200 × (1/40) = 2,000 行

三、實測與對比：解讀 db2exfmt 輸出

db2exfmt -d TESTDB -g TIC -o query.exfmt -w -1

關鍵輸出片段（簡化）

...
  3) HSJOIN (High-Fast Join)
     Estimated Rows: 2,000.00
     Estimated Cost: 15.34
     ...
     Predicate: (T1.JOIN1 = T2.JOIN2)
     Filter Factor: 0.025 (1/40)
...
  2) FETCH (T1)
     Estimated Rows: 400.00
     Predicate: (T1.FILTER1 = 1)
     Filter Factor: 0.04 (1/25)
...
  1) FETCH (T2)
     Estimated Rows: 200.00
     Predicate: (T2.FILTER2 = 1)
     Filter Factor: 0.02 (1/50)

對比結論

• 優化器估算值 (2,000) 完全等於理論計算值 (2,000)，驗證了「無分布統計時，JOIN 選擇率 = 1 / MAX(雙邊 COLCARD)」的預設假設。
• 單表過濾估算 (400, 200) 亦與選擇率公式 (1/COLCARD) 完全吻合。
• 此時優化器假設：雙邊 JOIN 欄值獨立、均勻分布、無相關性。

四、進階場景：當統計資訊缺失或不準時

五、最佳實踐：統計資訊管理策略

1. 基線收集： 上線前 RUNSTATS ON TABLE ... ON ALL COLUMNS WITH DISTRIBUTION DEFAULT
2. 自動維護： 啟用 AUTO_RUNSTATS、AUTO_STATS_PROF、AUTO_PROF_UPD，讓 DB2 依查詢回饋自動調整收集策略
3. 關鍵表手動干預： 核心大表、頻繁 JOIN 表，排程收集分布統計（非高峰期）
4. 統計版本控制： 重大變更前 db2look -m 導出統計 DDL，便於回滾比對
5. 驗證閉環： 定期抽查核心查詢 db2exfmt 估算值 vs 實際行數 (EXPLAIN SNAPSHOT 或應用層計數)，偏差 > 10x 觸發統計重收集

總結

本實測確認：在「基本統計完備、無分布統計、資料均勻」的理想條件下，DB2 優化器的 JOIN 基數估算精確遵循 1/MAX(COLCARD) 公式。現實環境中，分布統計的有無、統計的新鮮度、資料傾斜程度是決定估算準確性的三大關鍵。建立「收集→驗證→修正」的統計管理閉環，是穩定執行計劃的根本保障。