深度详解 Oracle 表连接 (Join) 三大核心算法：SMJ、NL、Hash Join

Join 基础概念

• 一次仅连接两个行源，多表连接按顺序两两合并。
• 驱动表 (外部表)：优先访问、应用过滤条件产生小行源的表，显著降低后续连接开销。
• 连接条件分类：等值连接 (=)、非等值连接 (>, < 等)、外连接 (+)。

三大连接算法详解

1. 排序合并连接 (Sort Merge Join, SMJ)

流程：
1. 生成 Row Source 1，按连接键 (A.COL3) 排序
2. 生成 Row Source 2，按连接键 (B.COL4) 排序
3. 双指针合并：同步游标扫描两有序流，匹配行输出

• 适用：非等值连接、大表连接且双方均有索引/已排序、内存不足以建 Hash 表时。
• 缺点：排序开销大 (可能落盘 TEMP)，返回行源过大导致后续 ROWID 回表 I/O 激增。

2. 嵌套循环连接 (Nested Loops, NL)

流程：
FOR each row in Outer (驱动表) LOOP
    在 Inner (被驱动表) 查找匹配行 (索引/全扫)
    输出匹配行
END LOOP

• 优势：流水线输出，首行极快，适合 FIRST_ROWS 场景。
• 最佳条件：驱动表小 (过滤后行数少)、被驱动表有高选择性索引 (UK/高选择性非唯一索引)。
• 并行查询倾向大表作驱动表以利用并行度，但需硬件多 CPU/多磁盘控制器支撑。

3. 哈希连接 (Hash Join, HJ)

流程：
1. 用较小行源构建 Hash Table + Bitmap (内存中)
2. 扫描较大行源，计算连接键 Hash，探测 Hash Table
3. Bitmap 快速过滤无匹配行，减少 Hash 桶探测
4. 匹配行输出

• 理论效率最高 (O(M+N))，仅 CBO 支持，需 HASH_JOIN_ENABLED=TRUE (默认开)。
• 关键参数：HASH_AREA_SIZE (或 PGA_AGGREGATE_TARGET 自动管理)，内存不足时分区溢写 TEMP，性能断崖下跌。
• 驱动表 = 构建 Hash 表的表 (通常为较小行源)。

执行计划示例

NL Join

SELECT A.DNAME, B.SQL FROM DEPT A, EMP B WHERE A.DEPTNO = B.DEPTNO;
-- NESTED LOOPS
--   TABLE ACCESS FULL DEPT
--   TABLE ACCESS FULL EMP

Hash Join (Hint 强制)

SELECT /*+ USE_HASH(EMP) */ EMPNO FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO;
-- HASH JOIN
--   TABLE ACCESS FULL DEPT
--   TABLE ACCESS FULL EMP

算法选择决策树

核心提示：统计信息准确性是优化器选对算法的前提，定期 DBMS_STATS.GATHER_TABLE_STATS 并设置 METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO' 收集直方图。