Oracle 执行计划中的连接方式（Join Methods）有哪些？有什么特点？

最新推荐文章于 2025-11-24 14:21:40 发布

原创最新推荐文章于 2025-11-24 14:21:40 发布 · 775 阅读

28 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #数据库

AI辅助_面试宝典专栏收录该内容

584 篇文章

订阅专栏

在这里插入图片描述
好的，我们将对 Oracle 数据库执行计划中的各种连接方式（Join Methods）进行一次非常深入和全面的剖析。连接是 SQL 优化的核心，理解其底层原理对于解决性能问题至关重要。

第一部分：官方技术详解

一、核心概念与连接基础

在关系型数据库中，连接（Join）是将两个或多个表（或数据集）中的数据基于一定的关联条件组合起来的操作。Oracle 优化器会根据表的大小、索引、选择性、可用资源（内存、CPU）等因素，为每个连接操作选择它认为最高效的连接方法。

二、六大连接方式：内部原理与使用场景

1. 嵌套循环连接 (Nested Loops Join, NLJ)

算法原理：
1. 优化器确定驱动表（外部表，通常返回结果集更小的表）和被驱动表（内部表）。
2. 对于驱动表结果集中的每一行（Outer Loop）：
  a. 根据连接列的值，去被驱动表中查找（Inner Loop）。
  b. 查找通常通过索引唯一扫描（Index Unique Scan）或索引范围扫描（Index Range Scan） 完成，这是 NLJ 高效的关键。
  c. 返回所有匹配的行。

伪代码表示：

for each row R1 in (driving table) {  -- 外部循环
  for each row R2 in (driven table) where R1.join_key = R2.join_key { -- 内部循环（索引查找）
    output (R1, R2);
  }
}

适用场景：
- 驱动表很小（或经过条件过滤后结果集很小）。
- 被驱动表连接列上有高效索引。
- 返回的结果集总量较小（例如，OLTP 系统中点查询）。
- 如果要求快速返回第一批数据，NLJ 通常响应最快。
相关 Hint: USE_NL(<table_alias>)

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

算法原理（经典的两阶段模型）：
1. 构建阶段 (Build Phase)：
  - 优化器选择两个表中较小的一个作为构建表（Build Table）。
  - 在 PGA（如果 WORKAREA_SIZE_POLICY=AUTO 且内存足够，否则在磁盘临时表空间）中，根据连接列的值构建一个哈希表。哈希表包括连接列的值和该行可能需要回表的列（如果无法在哈希表中直接满足查询）。
2. 探测阶段 (Probe Phase)：
  - 逐行扫描较大的那个表（探测表，Probe Table）。
  - 对探测表的每一行，使用相同的哈希函数对其连接列进行计算，得到哈希值。
  - 用这个哈希值到第一步构建的哈希桶（Hash Bucket）中去查找。
  - 如果找到，再进一步比较实际的连接列值（解决哈希冲突），如果匹配，则返回结果。
适用场景：
- 适用于大数据集的连接，是数据仓库（OLAP）系统的首选。
- 尤其当连接条件是等值连接（=）时。
- 通常在没有索引或索引效率不高时，比 NLJ 更有效。
- 需要足够的 PGA 内存来容纳哈希表，否则会产生大量的磁盘 I/O（哈希外溢），性能急剧下降。
相关 Hint: USE_HASH(<table_alias>)

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

算法原理：
1. 排序阶段：对两个需要连接的表分别基于连接列进行排序。如果连接列上有索引，并且索引本身是有序的，则可以避免排序。
2. 合并阶段：像拉链一样，将两个已排序的结果集合并在一起。
  - 在两个排序集上各放置一个指针。
  - 比较当前指针所指行的连接列值。
  - 如果相等，输出连接行。
  - 如果不等，则将值较小的那个集合的指针向前移动一行。
  - 重复直到任一集合遍历完毕。
适用场景：
- 当连接条件是非等值连接（如 <, <=, >, >=, BETWEEN）时，哈希连接无效，SMJ 是主要选择。
- 当两个表都已经在连接列上预先排序好了（例如有索引），可以跳过排序阶段，效率很高。
- 如果数据需要排序且内存不足，排序操作会使用磁盘临时表空间，性能会受影响。
相关 Hint: USE_MERGE(<table_alias>)

4. 笛卡尔连接 (Cartesian Join)

算法原理：对两个表进行无条件的连接，驱动表的每一行都会与被驱动表的每一行进行组合。结果行数是 M * N（M 和 N 是两个表的行数）。
适用场景：
- 几乎总是错误的，除非你确实需要产生所有组合（例如生成测试数据）。
- 有时优化器会因为缺少连接条件或统计信息严重失真而错误地选择笛卡尔连接，这通常会导致灾难性的性能问题。
注意：应极力避免非预期的笛卡尔连接。

5. 位图连接 (Bitmap Join)

算法原理：
1. 从维度表（Dimension Table）中获取过滤条件，为每个维度表上的过滤条件创建一个位图（Bitmap）。
2. 使用位图操作（如 AND, OR）将这些条件组合起来。
3. 使用组合后的位图去访问事实表（Fact Table）上的位图索引，快速定位到事实表中需要的事实行。
适用场景：
- 几乎专用于数据仓库环境的星型模式（Star Schema）查询。
- 事实表巨大，维度表较小，且在事实表的外键列上建有位图索引。
- 查询包含多个维度上的过滤条件。

6. 外连接 (Outer Joins)
外连接（LEFT/RIGHT/FULL）是一种语义，而不是一种独立的物理连接算法。上述的 NLJ, HJ, SMJ 都可以用来实现外连接。

例如，一个 LEFT OUTER JOIN 可能通过 NESTED LOOPS OUTER 或 HASH JOIN OUTER 来实现。优化器会选择它认为最高效的物理连接方法来满足外连接的逻辑要求。

三、连接的管理与优化器因素

优化器选择连接方法的依据主要来自：

统计信息：表的行数（CARDINALITY）、直方图（Histograms）、聚簇因子（Clustering Factor）等。陈旧或缺失的统计信息是错误选择连接方式的首要原因。
初始化参数：OPTIMIZER_MODE (ALL_ROWS vs FIRST_ROWS), HASH_JOIN_ENABLED, PGA_AGGREGATE_TARGET（为哈希连接分配内存）等。
查询结构：连接条件（等值 vs 非等值）、Hints 的存在、子查询等。

第二部分：场景、争用、排查与解决

四、性能争用与排查

不同的连接方式会引发不同类型的性能问题。

1. 场景：嵌套循环连接 (NLJ) 效率低下

问题：驱动表过大，或者对被驱动表的每次索引查找效率太低（逻辑读/物理读过高）。
等待事件：
- db file sequential read：最常见的等待事件，表示正在进行的单块读（通常是索引查找或回表）。
- db file scattered read：如果发生了全表扫描（通常意味着内部循环没有走索引）。
排查：
1. 查看执行计划，确认驱动表是否确实是小表。
2. 检查执行计划中对于被驱动表的访问方式是否是高效的索引访问（INDEX UNIQUE SCAN 或 INDEX RANGE SCAN）。
3. 检查 A-Rows（实际返回行数）是否与 E-Rows（估算行数）相差巨大，这可能意味着统计信息不准，导致优化器错误地选择了 NLJ。
解决：
- 确保被驱动表连接列上有选择性高的索引。
- 刷新统计信息：EXEC DBMS_STATS.GATHER_TABLE_STATS(...);
- 考虑使用 Hint USE_HASH 或 USE_MERGE 来强制优化器选择更适合大数据集的连接方式。
- 优化 SQL，添加条件以减少驱动表返回的行数。

2. 场景：哈希连接 (HJ) 性能问题 - 哈希外溢 (Hash Overflow)

问题：构建哈希表的阶段因为 PGA 工作区（Work Area）内存不足，不得不将部分哈希桶写入磁盘临时表空间（TEMP）。这会导致大量的磁盘 I/O，性能急剧下降。
等待事件：
- direct path read temp / direct path write temp：这是哈希外溢的典型标志，表示进程正在从临时表空间读取或写入数据。
排查：
1. 检查执行计划中的 OOM（Operation Memory）列，或者查看 V$SQL_WORKAREA_ACTIVE 视图，确认工作区是否最优尺寸（OPTIMAL）还是单遍（ONEPASS）甚至多遍（MULTIPASS）执行。
2. 监控 V$SYSSTAT 中的 workarea executions - optimal/onepass/multipass 统计信息。
3. 查看 AWR 报告的 PGA Memory Stats 部分。
解决：
- 增大 PGA_AGGREGATE_TARGET 参数，为哈希连接分配更多内存。
- 确保 WORKAREA_SIZE_POLICY 设置为 AUTO。
- 如果无法增加内存，尝试减少构建表的大小（通过添加过滤条件或子查询）。

3. 场景：排序合并连接 (SMJ) 中的排序操作昂贵

问题：排序阶段需要的内存不足，导致在磁盘上进行排序。
等待事件：
- direct path read temp / direct path write temp（同哈希外溢）。
- memory compaction：内存压缩，也是内存压力的一种表现。
排查与解决：与哈希连接的内存问题类似，增大 PGA_AGGREGATE_TARGET。

五、常用查询与管理 SQL

查看SQL的执行计划（多种方法）：

-- 1. 使用 EXPLAIN PLAN
EXPLAIN PLAN FOR SELECT * FROM employees e, departments d WHERE e.department_id = d.department_id;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 2. 查询动态性能视图 (适用于当前正在运行的SQL)
SELECT sql_id, child_number FROM v$sql WHERE sql_text LIKE '%...%';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', &child_number));

-- 3. 自动工作负载仓库 (AWR) 中的历史执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('&sql_id'));

监控SQL的内存使用：

SELECT sid, operation_type, policy, estimated_optimal_size, estimated_onepass_size,
       last_memory_used, last_execution, last_degree
FROM v$sql_workarea_active;

收集统计信息：

EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT', tabname => 'EMP', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

第三部分：通俗易懂的解释

想象一下你有两份名单：一份是所有员工的名单（大表），另一份是所有部门的名单（小表）。你需要找出每个员工属于哪个部门。

嵌套循环连接 (NLJ)：
- 你拿着部门名单（驱动表），从第一个部门开始。
- 对于“销售部”，你跑去巨大的员工花名册（被驱动表）的索引（比如按部门ID排好的目录）里，快速找到所有属于销售部的员工。
- 然后对“市场部”、“财务部”…重复同样的过程。
- 优点：如果部门很少（驱动表小），且员工花名册的索引很好用，速度飞快。
- 缺点：如果部门有1000个，你就得跑去找1000次索引。效率很低。
哈希连接 (HJ)：
1. 建哈希表：你拿过部门名单（构建表，小），在办公室的白板（PGA内存）上画了一个表格（哈希表）。你把部门ID（例如101）通过一个公式（哈希函数）计算一下，算出应该写在白板的哪个格子里（哈希桶）。你在那个格子里写上“部门ID: 101, 部门名: 销售部”。
2. 探测：然后你开始扫描员工名单（探测表，大）。对于员工“张三”，他的部门ID是101。你用同一个公式计算101，得到它应该对应白板上的哪个格子。你走过去一看，格子里果然有101，于是你就知道张三属于销售部。
- 优点：对于海量数据，这种方法极其高效，你只需要把部门表读一遍（建表），把员工表读一遍（探测）。
- 缺点：白板（内存）必须足够大，能写下整个部门表。如果部门太多，白板写不下，你就得把部分内容记到小本本（磁盘）上，来回翻看，速度就慢多了（哈希外溢）。
排序合并连接 (SMJ)：
1. 排序：你让两个助手分别把员工名单和部门名单都按照部门ID重新抄写一遍，并且按顺序排好。
2. 合并：现在你有两叠排好序的纸。你并排摆好，从左边的员工表取第一行（部门ID=101），从右边的部门表取第一行（部门ID=101）。匹配，输出。然后员工表取下一行（101），部门表不动，再匹配…直到员工表出现102，而部门表当前还是101。这时，你把部门表推进到下一行（102），再继续比较。
- 优点：非常适合两边都已经排好序的情况，或者需要处理>、<这类范围条件时。
- 缺点：如果数据没排序，排序本身很耗时，也需要占用桌面空间（内存/磁盘）。