通過執行計畫優化 SQL 實戰（四）：Hints 控制連接順序與方法的艱辛嘗試與大表驅動小表反直覺案例

基礎環境與初始計畫

三表 A, B, C 已收集統計資訊。原始查詢：

SELECT A.col4 FROM C, A, B
WHERE C.col3 = 5 AND A.col1 = B.col1 AND A.col2 = C.col2 AND B.col3 = 10;

初始計畫：HASH JOIN → MERGE JOIN CARTESIAN (B × C) → FULL SCAN A，笛卡爾積風險極大。

Hints 試錯過程記錄

心得： USE_NL / USE_HASH **必須搭配 ORDERED** 才能精確控制驅動表，否則優化器可能忽略或重排。

實戰大案例：5000 萬行明細表 Join 2800 行用戶表

環境

• swd_billdetail (B) 5,392 萬行，PK id，索引 idx_detail_cn (cn 非唯一，大量重複)。
• super_user (A) 2,800 行，索引 idx_super_user_cn。
• 一對多：A.cn → 多行 B.cn。

方案 1：小表驅動大表 (優化器預設，符合教科書)

SELECT COUNT(A.channel || B.user_class)
FROM swd_billdetail B, super_user A
WHERE A.cn = B.cn;

• 計畫：NLJ，Outer=A (Full Scan)，Inner=B (Index Range Scan → Table Access by RowID)。
• 執行時間：**1 小時 10 分**。
• 統計：Consistent Gets 1,196,954；Physical Reads 1,165,726 (幾乎全物理讀)。
• **根因**：A 每行驅動 B 索引掃描返回大量 RowID (cn 重複極高)，隨機 RowID 導致 B 表大量單塊物理讀，緩衝區無法緩存。

方案 2：大表驅動小表 (反直覺，但極效)

SELECT /*+ ORDERED USE_NL(A) */ COUNT(A.channel || B.user_class)
FROM swd_billdetail B, super_user A
WHERE A.cn = B.cn;

• 計畫：NLJ，Outer=B (Full Scan，多塊讀)，Inner=A (Index Range Scan → Table Access by RowID)。
• 執行時間：**20 分鐘** (提速 3.5 倍)。
• 統計：Consistent Gets 56,650,250 (邏輯讀暴增)；Physical Reads 1,413,250 (與全表掃描持平)。
• **原理**：B 全表掃描順序讀、多塊讀 (db_file_multiblock_read_count) 高效；A 表極小 (2,800 行) 全緩存於記憶體，索引/表存取近乎零成本。

核心結論

1. 驅動表選擇無絕對標準：取決於「內表存取成本 × 外表行數」vs「外表掃描成本 + 內表緩存命中率」。
2. 大表全掃 + 多塊讀 往往優於 大量隨機單塊讀。
3. 小表若能全緩存，作為內表極高效。
4. 若 cn 唯一，方案 1 可能更好；若 super_user 也有 500 萬行，方案 2 會變差。**具體問題具體分析，無統一標準。**

附錄：優化器選擇規則速查表 (RBO vs CBO 觸發條件)

無論 OPTIMIZER_MODE 如何設定，滿足下列任一條件**強制 CBO**：

1. 使用 IOT (Index Organized Table)。
2. 表設定 PARALLEL DEGREE > 1 (7.3+)。
3. SQL 含非 RULE、ALL_ROWS、FIRST_ROWS 之外的 Hint。

優先級由高到低：Non-RBO Object → Parallelism → RULE Hint → ALL_ROWS Hint → FIRST_ROWS Hint → Other Hint → OPTIMIZER_GOAL=RULE/ALL_ROWS/FIRST_ROWS/CHOOSE (CHOOSE 視物件是否分析決定 RULE/ALL_ROWS)。PL/SQL 內部固定 ALL_ROWS。

CBO 選錯計畫時的標準排查單

1. 統計資訊是否最新、完整 (含字典表，10g 前預設不分析字典)。
2. 嘗試 /*+ RULE */ 對比。
3. 開啟 10046 Trace + TKPROF 深度分析。

綁定變數雙刃劍： 減少 Hard Parse 但隱藏列統計 (直方圖) 導致計畫固化偏差。11g+ Adaptive Cursor Sharing 部分緩解。