关于Oracle的三种连接方式

[tiankui6658]

嵌套循环（Nested Loops （NL））
（散列）哈希连接（Hash Join （HJ））
（归并）排序合并连接(Sort Merge Join (SMJ) )

在网上看了不少资料了，但仍然搞不懂这三种连接方式的使用环境，分别在何种情况会被使用呢？或者说使用的条件是什么？请从连接表的索引、数据量、内存、Hash等等相关角度进行说明，非常感谢

[lhdz_bj]

这三个join方式，如果细讲的话，内容会比较多，而且，这些东西，你真要掌握，需要很多的实践的。

[bonlog]

其实这个东西，主要是看使用的领域， 若就是一般的软体，就用join三种即可

[a879107861]

求大神求老师啊

[陈字文]

嵌套循环（Nested Loops （NL）） 假如有A、B两张表进行嵌套循环连接，那么Oracle会首先从A表中提取一条记录，然后去B表中查找相应的匹配记录，如果有的话，就把该条记录的信息推到等待返回的结果集中，然后再去从A表中提取第二条记录，去在B表中找第二条匹配的记录，如果符合就推到返回的结果集中，依次类推，直到A表中的数据全部被处理完成，将结果集返回，就完成了嵌套循环连接的操作。 （散列）哈希连接（Hash Join （HJ）） 假如有A、B两张表进行哈希连接，那么ORACLE会首先将B表在内存中建立一棵以散列表形式存在的查询二叉树C，然后开始读取A表的第一条记录，从C中去找匹配的记录，如果有，则推到结果集中。再提取A中的第二条记录，如果有，则推到结果集中，以此类推，直到A中没有记录，返回结果集。 （归并）排序合并连接(Sort Merge Join (SMJ) ) 假如有A、B两张表进行排序合并连接，ORACLE会首先将A表进行排序，形成一张临时的“表”C，然后将B进行排序，形成一张临时的“表”D，然后将C与D进行合并操作，返回结果集。 如果从预获取的数据量的角度而言，如果B表参与计算的数据量比较小的话，则嵌套循环连接的效率就是比较高的，因为可以很少的IO就可以获取到最终的结果集。但是如果数据量比较大的话，hash join和sort merge join是比较有优势的。 如果从索引的角度而言，索引可以提高nested loops的效率，因为从B表获取数据进行操作，就类似于从单表中查询数据一样，table access full和by index的效率肯定是不一样的，但是这个也取决于B的参与计算的数据量，如果B表的数据都在可以被一次抓取的数据块的大小之内的话，那么索引未必会被使用到。 如果从内存的角度上，同样的数据量nested loops的内存占用应该是最小的，sort merge 应该是最大的，而hash join内存消耗在中间。只是一种感官的直觉，具体没有测试过，因为sort merge 需要创建两个排序表，而hash join则需要对B表创建一棵查询树。 怎么从hash的角度上来看呢？估计三种表都有hash的使用，使用hash更多的是为了提高查询的效率，比如8=power（2,3），如果使用hash，可能需要创建一棵hash树，就增大了空间的消耗，如果table access full的话，需要最少扫描1次，最多扫描8次。如果使用hash，则最少1次，最多3次，就可以了，使用空间获取时间上的优势。在这个里面，至少感觉到使用到hash的有nested loops中的索引和hash join。

引用 楼主 tiankui6658 的回复:

嵌套循环（Nested Loops （NL）） （散列）哈希连接（Hash Join （HJ）） （归并）排序合并连接(Sort Merge Join (SMJ) ) 在网上看了不少资料了，但仍然搞不懂这三种连接方式的使用环境，分别在何种情况会被使用呢？或者说使用的条件是什么？请从连接表的索引、数据量、内存、Hash等等相关角度进行说明，非常感谢 ……

[sych888]

通常情况下会依据表中的记录数来选择使用哪种连接方式，主要用的是HJ和NL， MJ用的比较少 以两张表为例 一张小表（在内存张很容易就能把所有数据放进去） 一张大表 通常用HJ，把小表作为HASH表，另外HJ只用于等值连接 两张都是大表时通常用NL