Oracle表連接五大模式深度解析：等值、外部、非等值、自連接、Hash Join原理與Hint控制

多表關聯時Oracle遞歸兩兩連接產生結果集再關聯下一表。五大連接模式完整解析：

1. 等值連接

共同欄位值相等的行出現在結果中。

SELECT * FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO;
-- ANSI標準
SELECT * FROM EMP JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO;

2. 外部連接

除匹配行外，保留單邊不匹配行。

3. 非等值連接

比較運算子> < BETWEEN ... AND等。典型案例：薪資等級表SALGRADE（LOSAL/HISAL）。

SELECT EMPNO, ENAME, SAL, GRADE
FROM EMP, SALGRADE
WHERE EMP.SAL BETWEEN LOSAL AND HISAL;

4. 自連接

表自我關聯，取別名視為兩表。案例：員工與主管關係。

SELECT W.ENAME, W.MGR, M.EMPNO, M.ENAME
FROM EMP W, EMP M
WHERE W.MGR = M.EMPNO;

5. Hash Join

運作機制

1. 優化器依統計資訊選小表，在記憶體建立Join Key Hash Table
2. 掃描大表，與Hash Table比對，匹配則加入結果集
3. 小表完全Cache於記憶體時效能最佳，成本僅兩表讀入記憶體

溢寫處理

• Hash Table過大 → 分區，逐區Cache，超出寫入臨時表空間
• 建議臨時表空間UNIFORM SIZE 1M提升I/O
• 大表同樣分區處理，最終歸併結果

啟用條件

• 等值連接 + 大資料量
• HASH_JOIN_ENABLED=TRUE + PGA_AGGREGATE_TARGET足夠大
• HASH_AREA_SIZE為9i前相容參數
• ORDERED Hint時，FROM第一張表建Hash Table

強制Hash Join Hint

SELECT /*+ USE_HASH(a b) */ a.user_name, b.dev_no
FROM user_info a, dev_info b
WHERE a.user_id = b.user_id;

適用場景

缺乏有用索引時優於Nested Loop；記憶體Hash比對快於B-Tree索引檢索。

掌握五大連接模式與Hash Join調優參數，是SQL調優從入門到精通的必經之路。