表（结果集）与表（结果集）之间的连接方式非常重要，如果CBO选择了错误的连接方式，本来几秒就能出结果的SQL可能跑一天都跑不完。如果想要快速定位超大型SQL性能问题，就必须深入理解连接方式。在多表JOIN的时候，只能是2个表先JOIN，JOIN之后的结果再和其它表/结果集关联，也就是说任何时候都是只参2个表在做JOIN

嵌套循环：

ORACLE从较小结果集（驱动表/外部表）中读取一行，然后和较大结果集（被探查表/内部表）中的所有数据逐条进行比较（嵌套循环可以用于非等值连接），如果符合规则，就放入结果集中，然后取较小结果集的下一条数据继续进行循环，直到结束。嵌套循环只适合输出少量结果或者是用于快速输出结果集。

select /*+use_nl(e d)*/ e.ename,e.job,d.dname from emp e,dept d where e.deptno=d.deptno and e.sal<2000;

被驱动表一定要走索引，并且索引要建立在JOIN列上

嵌套循环无需等到所有操作执行完毕才返回结果，它只需要循环匹配上就可以返回结果。如果某个查询返回的结果小于等于驱动表的10%，那么oracle很有可能选择嵌套循环来处理数据。在嵌套循环中，驱动表/外部表一般很小(这样利用全表扫描）,或者是表比较大，然后利用高度可选的索引过滤数据，得到的结果集（这个结果集一般小于源表10%）来作为驱动表，内部表通常较大，而且在连接列上面建立有唯一索引/或者高度可选的非唯一索引（如果

内部表上面缺乏索引，CBO通常会选择哈希连接)。

为什么要用小表/结果集较小的表作为驱动表？

因为如果表/结果集很大，那么返回的数据就多了，这样循环次数就会增加，会导致多次

index unique/range scan，那么效率就下去了，这个时候CBO会考虑交换驱动表或者干脆选择哈希连接

为什么内部表的连接上面要有索引？

因为如果内部表很大，如果连接列上面没有索引，那么会导致oracle对内部表进行全表扫描，而且是多次（根据外部表返回记录数），这样效率极其低下

所以如果内部表的连接列上面没有索引CBO会选择哈希边接

如何识别错误的nested loop?

1.可以通过比较E-ROWS和A-ROWS，如果两者相关太大，说明执行计划出问题了，这种方法适合OLTP系统

2.如果是OLAP，可以人工计算驱动表返回行数，然后比较CBO计算的返回行数，如果2者相关太大，说明执行计划出了问题

3.除了查看驱动表之外，我们还要查看被驱动表。被驱动表上面的连接列上通常有唯一索引或都高度可选的索引，如果你看到被驱表走了bitmap index，那么你要注意了，另外被驱动也可能是走的full table scan，这个时候我们要select count(*)然后比较CBO计算的值，如果两者相关太大，说明执行计划有问题，当然了也说明统计信息有问题

哈希连接（HASH JOIN)

前面提到，嵌套循环只适合输出少量结果集。如果要返回大量结果集（比如返回100W数据），根据嵌套循环算法，被驱动表有可能被扫描100W次，显然这是不对的。

----示例----

select /*+ full(dept) */ ename,job,sal,dname,loc from emp,dept where emp.deptno=dept.deptno;

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

嵌套循环不消耗PGA，HASH JOIN消耗PGA

其中User-Mem 799K (0) 这个“0”表是PGA够大，1表示PGA不太大

0mem,1Mem,Use-Mem,它表示HASH JOIN会消耗PGA，当驱动表太大，PGA不能容纳驱动表时，就会产生On-disk HASH JOIN.

ID=1这步，它有*号，执行计划中有*表示这个操作有过滤(filter)或者有access.HASH JOIN属于access。通过谓词过滤信息我们可以知道HASH JOIN的JOIN是哪些列在做JOIN这里就是emp.deptno和dept.dept.deptno做JOIN

HASH JOIN需要注意的地方：

1.利用等待事件监控HASH JOIN的时候，如果发现在做on-disk HASH JOIN(direct path read/write temp),  可以加大PGA，或者手工设置work area分配较大的PGA内存

2.检查HASH JOIN的JOIN列（通过HASH JOIN前面的ID找ACCESS)选择性，如果HASH JOIN连接列选择性很低，那么HASH JOIN可能跑很久，这个时候可以自己尝试构造伪列进行JOIN，如果无法构造伪列，这个时候看看能否从业务上优化，就不要想着用SQL优化了

3.HASH JOIN选择小表做驱动表，小表指的不是表的行数，而是指的是 行数*列宽度

  例子中，选择dept作为驱动表是因为dept大小 4*（dname+loc+deptno)宽度 < emp大小 14*(ename,job,sal,deptno)宽度

排序合并连接（SORT MERGE JOIN)

前文提到嵌套循环以及哈希连接，他们都有驱动表概念，排序合并连接没有驱动表一说，两个表/行源是对等关系。排序合并连接原理是先对两个表/行源根据JOIN列进行排序（当然了排序的时候要踢出不符合where条件的列）,然后再进行连接。排序合并连接可以处理非等值JOIN.有时候，出现了非等值JOIN还非得走SORT MERGE JOIN不可。根据排序合并的原理，我们知道排序合并连接其实很耗费资源，因为要对2个表/结果进行排序。

wx