Oracle 表连接的三种方式，带你深入了解表连接

原创已于 2024-11-20 22:10:02 修改 · 1.4k 阅读

17 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#oracle #数据库

于 2024-11-20 17:34:44 首次发布

写在前面：今天在一个近两千人的群里面看到有个人在说今天面试的题目，他说当面试官问他表连接的三种方式和他们的区别，他没答上来，我不禁问了问自己表连接的三种方式能说个七七八八，他们有什么区别？所以有了此文。

一.表连接的三种方式

1.NL（嵌套循环）

驱动表（Driving Table）

定义：驱动表是查询中首先被扫描的表。在连接操作中，数据库会从驱动表中逐行读取记录，然后根据连接条件去被驱动表中查找匹配的记录。
选择依据：一般来说，查询优化器会优先选择行数较少或过滤条件效果较好的表作为驱动表，这样可以减少连接操作的次数，提高查询效率。
特点：
驱动表的每一条记录都会触发一次对被驱动表的扫描。
优先扫描小表或过滤条件有效的表，能有效减少嵌套循环的次数。
被驱动表（Driven Table）

定义：被驱动表是指在连接过程中由驱动表触发查询的表。驱动表的每一条记录都会对被驱动表进行查找，以满足连接条件。
选择依据：被驱动表通常是行数较多的表，或者在连接条件的字段上有索引的表。使用索引可以提升查询速度。
特点：
每次驱动表中的记录都会触发一次对被驱动表的条件查询。
被驱动表的大小和索引情况直接影响查询效率。对于较大的被驱动表，如果缺少有效的索引，查询性能可能会下降显著。
驱动表和被驱动表的选择原则

小表驱动大表：通常会选择记录数较少的表作为驱动表，以减少嵌套循环的次数。
过滤条件强的表作为驱动表：如果某表的过滤条件可以显著减少扫描记录的数量，那么数据库通常会优先选择该表为驱动表。
索引因素：如果被驱动表的连接字段上有索引，则在嵌套循环中可以更快速地定位匹配记录，从而提高连接效率。

示例：假设有两个表：orders（订单表，有上百万条记录）和 customers（客户表，有几千条记录），我们需要查询每个订单对应的客户信息。查询如下：

SELECT o.order_id, o.order_date, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

在这种情况下，customers 表可能会被选择为驱动表，因为它记录数较少，每个客户的信息在 orders 表中可能会有多条记录。通过将小表 customers 作为驱动表，可以减少嵌套次数，从而提升连接效率。

2.Merge Sort Join（排序合并连接）

数据库从两个表的第一个记录开始逐行扫描。
每次从表A和表B取出一条记录，比较连接字段值：
如果记录匹配连接条件（例如连接字段值相等），则将两条记录合并输出。
如果表A的连接字段值小于表B，则移动表A的指针到下一条记录。
如果表B的连接字段值小于表A，则移动表B的指针到下一条记录。
按照这种方式顺序扫描并比较直到任意一个表达到末尾。

Merge Join示例：

SELECT o.order_id, o.order_date, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
ORDER BY o.customer_id, c.customer_id;

步骤1：如果 orders 和 customers 表已经按 customer_id 排序或有索引，则可以直接进入下一步；否则，数据库会先对它们进行排序。
步骤2：Merge Join从 orders 和 customers 表的第一个记录开始顺序扫描，并逐行比较 customer_id：
当找到相同 customer_id 的记录时，将匹配的记录合并输出。
如果 orders 中的 customer_id 小于 customers 中的 customer_id，则移动 orders 的指针到下一条记录。
如果 customers 中的 customer_id 小于 orders 中的 customer_id，则移动 customers 的指针到下一条记录。

看上去合并排序连接要比NL连接更耗时，其实不然，对于相当大的表来说，对于NL，驱动表返回多少条记录就等于访问了多少次被驱动表，但对于排序合并连接来说，顺序扫描减少了随机访问的需求，适合大数据集的连接。

那么merge sort join的优点如下：

效率高：对于已排序的表，Merge Join可以避免排序的开销，只需一次扫描。
适用于大表连接：顺序扫描减少了随机访问的需求，适合大数据集的连接。
支持范围连接：与Hash Join不同，Merge Join支持等值和范围连接。

3.Hash Join

Hash连接与merge join有点类似，都是基于吞吐量的操作，返回大量的数据的时候要明显比NL要高效，但Hash和merge join还是有区别的，Hash连接不算排序，由PGA中的 HASH_AREA_SIZE参数来控制,而排序合并连接则是由PGA中的SORT_AREA_SIZE参数控制的。HASH连接使用的是HASH算法,比排序合并连接更高效一些。