深入理解Oracle—三大表连接方式详解之Nested loop join和Sort merge join

左手的ㄟ右手 2022-04-11 06:47 318阅读 0赞

关系数据库技术的精髓就是通过关系表进行规范化的数据存储，并通过各种表连接技术和各种类型的索引技术来进行信息的检索和处理，下面大家一起来学习分享Oracle的三大表连接技术。

在早期版本，Oracle提供的是nested-loop join，两表连接就相当于二重循环，假定两表分别有m行和n行，如果内循环是全表扫描，时间复杂度就是O(m\*n)；如果内循环是索引扫描，时间复杂度就是O(m\*㏒n)；而hash join的时间复杂度是O(m\*n)。因此10g后，hash join成为缺省的连接方法。

对于三种连接，我们都可以使用hint来强制让优化器走：use\_hash,use\_nl,use\_merge

**一.三种连接概述**

**1.nested loop**

从A表抽一条记录，遍历B表查找匹配记录，然后从a表抽下一条，遍历B表，就是一个二重循环。

**2.hash join**

将A表按连接键计算出一个hash表，然后从B表一条条抽取记录，计算hash值，根据hash到A表的hash来匹配符合条件的记录。

**3.sort merge join**

将A，B表都排好序，然后做merge，符合条件的选出。

**二各种连接详解**

**1.Nested Loop Join**

**a.执行原理**

例如：

select t1.*,t2.* from t1,t2 where t1.col1=t2.col2;

访问机制如下：

for i in (select * from t1) loop
      for j in (select * from t2 where col2=i.col1) loop
      display results;
      end loop;
      end loop;

类似一个嵌套循环，嵌套循环执行时，先是外层循环进入内层循环，并在内层循环终止之后，接着执行外层循环再由外层循环进入内层循环中，当外层循环全部终止时，程序结束。

**b.步骤如下**

① 确定驱动表

② 把inner 表分配给驱动表

③ 针对驱动表的每一行，访问被驱动表的所有行

**c.执行计划大致如下**

NESTED LOOPS
    outer_loop
    inner_loop

优化器模式为FIRST\_ROWS时，我们经常会发现有大量的NESTED LOOP，这时，在返回数据给用户时，我们没有必要缓存任何数据，这是nested loop的一大亮点。

**d.使用场景**

一般用在连接的表中有索引，并且索引选择性较好（也就是Selectivity接近1）的时候，也就是驱动表的记录集比较小（<10000）而且inner表需要有有效的访问方法（Index），需要注意的是：JOIN的顺序很重要，驱动表的记录集一定要小，返回结果集的响应时间是最快的。

**e.和索引的关系**

嵌套循环和索引就像一对孪生兄弟，一般需要共同考量与设计，这从优化器的执行机制可以看出，比如，存在2张表，一个10条记录，一个1000万条记录，以小表为驱动表，则代价为：10\*(通过索引在大表查询一条记录的代价)，如果1000万的大表没有索引的时候，那么COST的代价可想而知。

因此，在多表连接时，注意被驱动表的连接字段是否需要创建索引，或者连接字段与该表的其他约束条件字段上是否需要创建复合索引。

**2.Sort Merge Join**

**a.执行原理**

例如：

select t1.*,t2.* from t1,t2 where t1.id=t2.id;

访问机制如下：

访问t1，并order by t1\_1.id，这里的id代表连接字段；

访问t2，并order by t2\_1.id join t1\_1.id = t2\_1.id，依次交替比对归并，但无所谓驱动。

**b.使用场景**

虽说，hash join就是用来替代Sort Merge Join的，但如果你的服务器的CPU资源和MEM资源都很紧张的时候，建议用SORT MERGE JOIN，因为hash join比sort merge join需要的资源更多，特别是cpu。

10g sql tuning 文档上写道：

On the other hand, sort-merge joins can perform better than hash joins if both of the following conditions are met：

The row sources are already sorted.

A sort operation does not have to be done.

所以，Sort Merge Join大概就用在没有索引，并且数据已经排序的情况。

文章来源：[http://www.2cto.com/database/201301/186885.html][http_www.2cto.com_database_201301_186885.html]

[http_www.2cto.com_database_201301_186885.html]: http://www.2cto.com/database/201301/186885.html

发表评论取消回复

表情：

评论列表（有 0 条评论，318人围观）

还没有评论，来说两句吧...

相关阅读

相关 join表连接的三种算法思想：Nested-Loop Join和Index Nested-Loop Join和Block Nested-Loop Join和BKA

一.Nested-Loop Join 在Mysql中，使用Nested-Loop Join的算法思想去优化join，Nested-Loop Join翻译成中文则是“嵌套循

￡神魔★判官ぃ/ 2022年12月06日 15:54/ 0 赞/ 94 阅读

相关浅谈SQL Server中的三种物理连接操作(Nested Loop Join、Merge Join、Hash Join)

简介在SQL Server中，我们所常见的表与表之间的Inner Join,Outer Join都会被执行引擎根据所选的列，数据上是否有索引，所选数据的选择性转

Dear 丶/ 2022年11月25日 00:40/ 0 赞/ 107 阅读

相关 mysql nest loop伪代码_Nested Loop Join

我们都知道SQL的join关联表的使用方式，但是这次聊的是实现join的算法，join有三种算法，分别是Nested Loop Join，Hash join，Sort Merg

亦凉/ 2022年10月26日 01:21/ 0 赞/ 103 阅读

相关多表连接的三种方式详解 HASH JOIN MERGE JOIN NESTED LOOP

在多表联合查询的时候，如果我们查看它的执行计划，就会发现里面有多表之间的连接方式。之前打算在sqlplus中用执行计划的，但是格式看起来有点乱，就用Toad 做了3个截

深碍√TFBOYSˉ_/ 2022年08月10日 14:44/ 0 赞/ 95 阅读

相关 Oracle的Filter,Nest loop,Merge sort join和Hash join

文章转自 http://czmmiao.iteye.com/blog/1807571 Merge Sort Join 按照Merge Sort Join连接的两表地位完全

Dear 丶/ 2022年07月16日 11:51/ 0 赞/ 121 阅读

相关 Spark Join——Broadcast Join、Shuffle Hash Join、Sort Merge Join

1. Broadcast Join 在数据库的常见模型中（比如星型模型或者雪花模型），表一般分为两种：事实表和维度表。维度表一般指固定的、变动较少的表，例如联系人、物品种

你的名字/ 2022年05月12日 14:46/ 0 赞/ 173 阅读

相关深入理解Oracle—三大表连接方式详解之Nested loop join和Sort merge join

关系数据库技术的精髓就是通过关系表进行规范化的数据存储，并通过各种表连接技术和各种类型的索引技术来进行信息的检索和处理，下面大家一起来学习分享Oracle的三大

左手的ㄟ右手/ 2022年04月11日 06:47/ 0 赞/ 319 阅读

相关 MySQL多表关联之Block Nested-Loop Join

本文转载自：[MySQL多表关联之Block Nested-Loop Join][MySQL_Block Nested-Loop Join] 一、背景从事DBA工作

亦凉/ 2022年01月10日 13:23/ 0 赞/ 224 阅读

相关多表连接的三种方式详解 hash join、merge join、 nested loop

在多表联合查询的时候，如果我们查看它的执行计划，就会发现里面有多表之间的连接方式。多表之间的连接有三种方式：Nested Loops，Hash Join 和 Sort Merg

一时失言乱红尘/ 2021年06月11日 15:12/ 0 赞/ 462 阅读

相关 Nested loops、Hash join、Sort merge join（三种连接类型原理、使用要点）

nested loop 嵌套循环（原理）： oracle从较小结果集（驱动表、也可以被称为outer）中读取一行，然后和较大结果集（被侦查表，也可以叫做inner）中的所...

朱雀/ 2021年04月06日 13:17/ 0 赞/ 514 阅读