Oracle 多表關聯查詢優化實戰：破解優化器誤選笛卡爾積與連接順序錯誤

前言

Oracle 優化器 (CBO) 在多表關聯時，偶爾會誤判連接順序，導致大表之間產生笛卡爾積 (Cartesian Product) 或選擇極差的驅動表。本文基於真實案例 (A 表 2000 行、B 表 15 萬行、C 表 18 萬行)，深度剖析優化器誤選 Merge Join (A-C) 的成因，並提供 4 種修復方案對比驗證。

問題場景重現

-- 表結構
-- A: 2,000 行，主鍵 COL_A
-- B: 150,000 行，主鍵 COL_B1，外鍵 COL_B2 關聯 C
-- C: 180,000 行，主鍵 COL_C
-- 所有關聯列均有獨立索引

-- 業務 SQL：A 驅動 B，B 驅動 C
SELECT a.* 
FROM a, b, c
WHERE a.col_a = b.col_b1
  AND b.col_b2 = c.col_c;

優化器誤選執行計劃

---------------------------------------------------------------------------
| Id | Operation            | Name | Rows  | Bytes | Cost | Time     |
---------------------------------------------------------------------------
|  0 | SELECT STATEMENT     |      |       |       | 1250K|          |
|  1 |  MERGE JOIN CARTESIAN|      | 360M  |  12G  | 1250K| 04:10:00 |
|  2 |   TABLE ACCESS FULL  | A    |  2000 |       |   10 | 00:00:01 |
|  3 |   BUFFER SORT        |      | 180K  |       | 1250K| 04:10:00 |
|  4 |    TABLE ACCESS FULL | C    | 180K  |       |  500 | 00:00:06 |
|  5 |   TABLE ACCESS BY INDEX ROWID| B | 150K |       |   20 | 00:00:01 |
|  6 |    INDEX RANGE SCAN  | IDX_B_COL_B1 | 150K |       |   5  | 00:00:01 |
---------------------------------------------------------------------------

災難本質：優化器選擇 A FULL SCAN → C FULL SCAN → MERGE JOIN CARTESIAN (2000 * 180,000 = 3.6 億行) → 再去關聯 B。臨時表空間爆滿、查詢掛起數小時。

根因分析：為什麼優化器「看不見」A-B-C 路徑？

1. 統計信息缺失/過期：B 表的 COL_B2 與 C 表 COL_C 的關聯選擇性 (NDV、直方圖) 未收集，優化器預設選擇性 1% 或 0.01%，嚴重高估 B-C 關聯結果集。
2. 基數估算錯誤傳播：高估 B-C 結果 → 認為 B-C 先關聯代價大 → 嘗試 A-C 先關聯 (因 A 小、C 有索引？實則無關) → 產生笛卡爾積。
3. 連接順序搜索空間限制：_OPTIMIZER_MAX_PERMUTATIONS (默認 2000) 限制了 3 表全排列 (6 種) 的搜索，但誤導代價模型使最優路徑被剪枝。

四種修復方案對比驗證

方案一：Hint 強制連接順序 (最快見效、侵入性最小)

SELECT /*+ LEADING(a b c) USE_NL(b) USE_NL(c) */ a.*
FROM a, b, c
WHERE a.col_a = b.col_b1
  AND b.col_b2 = c.col_c;

• 效果：執行計劃變為 NESTED LOOPS (A→B→C)，Cost 從 1250K 降至 ~50，耗時 < 0.1s。
• 優點：無需 DDL、即時生效、可精確控制。
• 缺點：硬編碼計劃，統計信息更新後可能非最優，需定期審視。

方案二：補充統計信息 (治本、零侵入)

-- 關鍵：為關聯列收集直方圖，修正選擇性估算
EXEC DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'SCOTT',
    tabname => 'B',
    method_opt => 'FOR COLUMNS SIZE 254 COL_B2',  -- 關聯列收集高度直方圖
    cascade => TRUE,
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);

EXEC DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'SCOTT',
    tabname => 'C',
    method_opt => 'FOR COLUMNS SIZE 254 COL_C',
    cascade => TRUE);

• 效果：優化器自動識別 B-C 高選擇性，自動選擇 LEADING(A B C) + NESTED LOOPS。
• 優點：根治、受益所有相關 SQL、無代碼變更。
• 缺點：需維護窗口、大表收集耗時、直方圖可能導致綁定變量窺探問題。

方案三：ANSI JOIN 語法重寫 (引導優化器)

-- 寫法 1：明確 B 先關聯 C
SELECT a.*
FROM a
JOIN (b JOIN c ON b.col_b2 = c.col_c) ON a.col_a = b.col_b1;

-- 寫法 2：明確 A 先關聯 B
SELECT a.*
FROM (a JOIN b ON a.col_a = b.col_b1)
JOIN c ON b.col_b2 = c.col_c;

• 效果：部分版本優化器尊重 ANSI 語法隱含的連接順序提示，但**不保證**，視為弱干預。
• 缺點：不可靠，複雜查詢可讀性反而下降。

方案四：虛擬列 / 擴展統計 (11g+ 進階)

-- 若 B 表經常按 (COL_B1, COL_B2) 組合查詢
EXEC DBMS_STATS.CREATE_EXTENDED_STATS('SCOTT', 'B', '(COL_B1, COL_B2)');
-- 再收集統計信息，優化器掌握組合 NDV/相關性，大幅提升基數估算準確度

• 適用：多列相關性導致的基數誤估 (如城市-郵編、品牌-類別)。

驗證腳本：對比執行計劃關鍵指標

SET AUTOTRACE TRACEONLY EXPLAIN STATISTICS
-- 或
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST'));

-- 關注指標：
-- 1. Operation: NESTED LOOPS vs MERGE JOIN CARTESIAN
-- 2. Cost: 應從百萬級降至百/千級
-- 3. Buffers (Consistent Gets): 應從百萬級降至千級
-- 4. Temp Space: 應為 0 (無笛卡爾積)
-- 5. A-Rows (Actual Rows): 驗證基數估算準確度 (E-Rows vs A-Rows)

生產環境修復決策樹

發現笛卡爾積/連接順序錯誤
    │
    ├─► 緊急止血 (分鐘級) → 加 Hint (LEADING/USE_NL) → 上線驗證
    │
    ├─► 根因排查 (小時級) → 檢查統計信息時間、直方圖、NDV、_OPTIMIZER_MAX_PERMUTATIONS
    │
    ├─► 根治 (天級) → 補直方圖/擴展統計 → 測試驗證 → 移除 Hint
    │
    └─► 長效機制 → 
        1. 關鍵表鎖定統計信息收集策略 (DBMS_STATS.LOCK_TABLE_STATS + 定制 Job)
        2. 建立 SQL 基線 (SPM) 固化優質計劃
        3. 核心 SQL 納入「性能基線庫」定期回歸測試

結語

優化器誤選連接順序產生笛卡爾積，本質是**統計信息不足以支撐準確基數估算**。工程上採用 **「Hint 止血 → 統計信息根治 → SPM 固化 → 基線庫回歸」** 四步走策略。核心動作：為關聯列收集高度直方圖 (SIZE 254)、啟用擴展統計捕捉列組相關性、鎖定核心表統計信息防止波動、建立 SQL Plan Baseline 保護優質計劃。切記：Hint 是拐杖，統計信息才是腿。沒有準確統計信息，優化器就是瞎子騎瞎馬。