1. 表连接方式

1.1. Nested Loop Join

循环嵌套连接是最基本的连接,正如其名所示那样，需要进行循环嵌套，嵌套循环是三种方式中唯一支持不等式连接的方式，这种连接方式的过程可以简单的用下图展示:

由上面两个图不难看出，循环嵌套连接查找内部循环表的次数等于外部循环的行数，当外部循环没有更多的行时，循环嵌套结束。另外，还可以看出，这种连接方式需要内部循环的表有序（也就是有索引），并且外部循环表的行数要小于内部循环的行数。对一些大表的关联，使用此方式连接，效率就会很低下，试想，如果内层循环可以使用索引，但外层表有很大的数据量，尽管内存查询一次是1毫秒，外层有100万条数据，那么语句的时间也会变成100万*1毫秒=16分钟，由此就出现了Merge join.

1.2. Merge Join

Merge Join其实上就是将两个有序队列进行连接，需要两端都已经有序，所以不必像Loop Join那样不断的查找循环内部的表。其次，Merge Join需要表连接条件中至少有一个等号查询分析器才会去选择Merge Join。

Merge Join的过程我们可以简单用下面图进行描述:

假如有A、B两张表进行排序合并连接，ORACLE会首先将A表进行排序，形成一张临时的“表”C，然后将B进行排序，形成一张临时的“表”D，然后将C与D进行合并操作，返回结果集。

1.2.1. 步骤：

1. 对连接的每个表做table access full;

2. 对table access full的结果进行排序。

3. 进行merge join对排序结果进行合并。

在全表扫描比索引范围扫描再通过rowid进行表访问更可取的情况下，merge join会比nested loops性能更佳。当表特别小或特别巨大的时候，实行全表访问可能会比索引范围扫描更有效。mrege join的性能开销几乎都在前两步。

当两个表的关联列都没有索引时，那么在使用merge join进行关联时则会先进行排序，这一步会消耗大量的时间，效率不高，并且会非常消耗CPU，由此出现了Hash join.

1.3. Hash Join

Hash Join正式进入优化器执行计划生成，只有CBO才能使用Hash Join操作。本质上说，Hash Join连接是借助Hash算法，连带小规模的Nest Loop Join，同时利用内存空间进行高速数据缓存检索的一种算法。

1.3.1. 步骤

1 Hash Join连接对象依然是两个数据表，首先选择出其中一个“小表”。这里的小表，就是参与连接操作的数据集合数据量小。对连接列字段的所有数据值，进行Hash函数操作。Hash函数是计算机科学中经常使用到的一种处理函数，利用Hash值的快速搜索算法已经被认为是成熟的检索手段。Hash函数处理过的数据特征是“相同数据值的Hash函数值一定相同，不同数据值的Hash函数值可能相同”。

2 经过Hash处理过的小表连接列，连同数据一起存放到Oracle PGA空间中。PGA中存在一块空间为hash_area，专门存放此类数据。并且，依据不同的Hash函数值，进行划分Bucket操作。每个Bucket中包括所有相同hash函数值的小表数据。同时建立Hash键值对应位图。

3 之后对进行Hash连接大表数据连接列依次读取，并且将每个Hash值进行Bucket匹配，定位到适当的Bucket上（应用Hash检索算法）。

4 在定位到的Bucket中，进行小规模的精确匹配。因为此时的范围已经缩小，进行匹配的成功率精确度高。同时，匹配操作是在内存中进行，速度较Merge Sort Join时要快很多。

1.3.2. hash join实现的几个细节

　　1.hash join本身的实现不要去判断哪个是小表，优化器生成执行计划时就已经确定了表的连接顺序，以左表为小表建立hash table,那对应的代价模型就会以左表作为小表来得出代价，这样根据代价生成的路径就是符合实现要求的。

　　2.hash table的大小、需要分配多少个桶这个是需要在一开始就做好的，那分配多少是一个问题，分配太大会造成内存浪费，分配太小会导致桶数过小开链过长性能变差，一旦超过这里的内存限制，会考虑dump到外存，不同数据库有它们自身的实现方式。

2. 实例

2.1. 环境准备

create table segs as select * from dba_segments where owner='SYS';

create table objts as select * from dba_objects where owner='SYS';

select count(*) from segs; --小表 ;

select count(*) from objts; --大表

create index idx_segs_name on segs(segment_name);

create index idx_objts_name on objts(object_name);

call dbms_stats.gather_table_stats(user,'SEGS');

call dbms_stats.gather_table_stats(user,'OBJTS');

2.2. Nested loop

2.3. Merge join

2.4. Hash join

详细对比见下图：

3. 总结

下面我们通过一个表格简单总结这几种连接方式的消耗和使用场景:

1 嵌套循环(nest loop):

对于被连接的数据子集较小的情况，嵌套循环连接是较好的选择。在嵌套循环中，外表驱动内表，外表返回的每一行都要在内表中检索找到它匹配的行，因此整个查询返回的结果集不能太大（大于10000不合适），要把返回子集较小的表作为外表（驱动表），而且在内表的连接字段上一定要有索引。

2 哈希连接(hash join):

哈希连接是大数据集连接时常用的方式，优化器使用两个表中较小的表，利用连接键在内存中建立散列表，然后扫描较大的表并探测散列表，找出与散列表匹配的行。

这种方式适用于较小的表完全可以放入内存的情况，这样成本就是访问两个表的成本之和。但是在表很大的情况下并不能完全放入内存，这时优化器将它分割成若干不同的分区，不能放入内存的部分就把该分区写入磁盘的临时段。

哈希连接只能应用于等值连接(如WHERE A.COL3 = B.COL4)、非等值连接(WHERE A.COL3 > B.COL4)、外连接(WHERE A.COL3 = B.COL4(+))。

3 排序合并连接（Sort Merge Join ）

通常情况下哈希连接的效果都比排序合并连接要好。然而如果行源已经被排过序，在执行排序合并连接时不需要再排序了，这时排序归并连接的性能会忧于哈希连接。

下几篇帖子逐步介绍一下Oracle的动态性能视图，欢迎关注