核心機制比較
| 連接方式 | 核心邏輯 | 資源消耗 | 適用場景 |
|---|
| Nested Loop Join | 外部表每行探測內部表 (如巢狀迴圈) | CPU低、I/O看內部表索引 | 外部表小(<10K)、內部表有高選擇性索引 |
| Sort Merge Join | 雙表各自排序後合併 | I/O高(排序)、臨時空間大 | 無索引但資料已排序、大表連大表 |
| Hash Join | 小表建Hash表在記憶體，大表探測 | CPU高(Hash計算)、記憶體大 | 雙表大小差異大、無索引、記憶體充足 |
詳細運作原理
Nested Loop Join (NLJ)
- 驅動表 (Outer) → 探測表 (Inner)
- Cost = Outer存取成本 + (Inner存取成本 × Outer基數)
- 強制提示：
USE_NL(table1 table2)
- 可用
ORDERED指定驅動順序
Sort Merge Join (SMJ)
- 兩表皆需排序 (若已有序可省略)
- Cost = (Outer存取成本 × Hash分區數) + Inner存取成本
- 強制提示：
USE_MERGE(table1 table2)
- 資料預先有序時優於Hash Join
Hash Join (HJ)
- 小表在記憶體建Hash表 (Join Key)，大表掃描探測
- 小表超過
HASH_AREA_SIZE → 分區寫入臨時表空間，多一次I/O
- 平行環境：Join結果先存記憶體/臨時表空間，最後一次輸出
- 強制提示：
USE_HASH(table1 table2)
- 9i+建議：
WORKAREA_SIZE_POLICY=AUTO + 調整PGA_AGGREGATE_TARGET
- 禁用：
HASH_JOIN_ENABLED=FALSE
效能比較總結
- Hash Join通常最快：僅掃描兩表各一次，CPU換I/O
- Nested Loop次之：驅動表小時極快，大表驅動則退化
- Sort Merge最慢：雙重排序開銷大，除非資料天然有序
資源權衡決策
- CPU緊張：限制Hash Join，改用NLJ或SMJ
- I/O緊張：優先Hash Join (順序讀取)、避免SMJ排序寫入
- 記憶體不足：Hash Join退化為Grace Hash (多次I/O)，考慮NLJ
實務建議：收集完整統計資訊讓CBO自動選擇，僅在計畫明顯錯誤時使用Hint干預。
