/*+leading（t1）use_nl（t2）*/ 之类的HINT提示。

t1表和 t2表根本就没建索引，所以所有试验脚本的执行计划都是全表扫描！

在表连接的研究中，索引是非常重要的一部分，对提升表连接性能起到至关重要的作用。

t1.id=t2.t1_id就是连接条件，而t1.n=19和t2.n=5932就是限制条件。

嵌套循环连接与索引

Oracle认为执行计划走散列连接更合理而选择了散列连接，其实从执行计划来分析，两者实际的逻辑读和执行时间都差不多，应该说 Oracle 选择散列连接也是一个‘艰难’的选择，

索引正是快速找到某记录的利器，高度比较低正适合于此。

系统性能稍稍有些提升，Buffers从1013减少到1009,因为t1表的扫描方式从全表扫描转化为索引读了。不过由于t1表中的记录才100条，索引除了只能单块读这个劣势外，还有TABLE ACCESS BY INDEX ROWID的回表查询负担，所以Buffers是3，才略胜全表扫描的Buffers为7,7-3正好等于1013-1009

Buffers居然从1009变为7了。

最适合嵌套循环连接的场景如下所示：

① 两表关联返回的记录不多，最佳情况是驱动表结果集仅返回1条或少量几条记录，而被驱动表仅匹配到1条或少量几条记录，这种情况即便t1表和t2表的记录奇大无比，速度也是非常快的。

② 遇到一些不等值查询导致散列连接和排序合并连接被限制使用，不得不使用嵌套循环连接。

① 驱动表的限制条件所在的列有索引。

② 被驱动表的连接条件所在的列有索引。

t1表是驱动表，t2表是被驱动表，因为在执行计划的嵌套循环范围内，t1在前，t2在后。

驱动表t1的限制条件是t1.n=19，限制条件所在的列就是 n 列。

对t1表和t2表来说，连接条件都是一样的吧，都是t1.id=t2.t1_id

“那连接条件就是t1.id=t2.t1_id,t2表的t1_id列就是被驱动表的连接条件。”得到老师的肯定答复后，曾祥自信地回答。“大家都回答得这么正确，老师很高兴，千万别小看了老师前面说的关于索引的精良武器装备，工作中依赖这个的优化可谓数不胜数，现在请大家自行观察学习材料一分钟，体会一下老师的索引建在了什么位置。”“这一节老师讲课的风格和以前略有不同，我先说了结论，不过原理没事先说明，大家说说，为什么嵌套循环连接要在驱动表的限制条件上加索引，在被驱动表的连接条件上加索引呢？”“我知道！”善于动脑的晶晶早就思考过这个问题了，“在驱动表的限制条件上建索引是为了减少扫描驱动表的时间，如果在驱动表的连接条件上建索引就没任何意义了，所有列关联到另一张表的所有列，等同于每条记录都要关联。而在驱动表的限制条件上建了索引，只快速返回1条或者几条，然后再等传递给t2表的t2_id列，一般情况下t2表对应t1表的记录返回不多，所以在t2表的t1_id列建索引是有意义的。”“说得非常好，同学们都听明白了吗，因为嵌套循环查询的使用范围太广了，实用性非常强，所以老师再次多说两句，做一个详细的推理总结。我们就以如下SQL语句来推理，假如返回的结果不多，也就一条到几条：

t1表的n in（19,20）的条件，比如返回2条记录，首先获取n=19传递给id列，发现id列值为199，传递给t2表的t1_id列，于是t1_id也等于199，这时等同于对t2表进行了一个WHERE t2.t1_id=199的查询。因为根据嵌套循环连接的适用场景，这种业务一般返回不多，1条到几条，所以用索引快速返回，这时t2表的其他列的索引就无意义了。

t1表的n=20再次传递给t1表自己的id列，比如发现值为299，再次传递给t2表的t1_id列，于是t1_id也等于299，这时等同于对t2表进行一个WHEREt2.t1_id=299的查询，剩下的情况和n=19的情况完全类似，t2表的t1_id列非常有用，而t2表的其他列的索引依然无意义。

如果n in（19,20）返回很多记录，刚才的动作就要做很多遍，效率就比较低；t1表传递给t2表后匹配的记录越少，t2表的连接条件的列的索引就越能发挥作用。

散列连接与索引

散列连接、排序合并连接和嵌套循环连接最大的区别在于，连接条件的索引对它们起不到传递的作用，

对于散列连接和排序合并连接来说，索引的连接条件起不到快速检索的作用，但是限制条件列如果有适合的索引可以快速检索到少量记录，还是可以提升性能的。

关于散列连接与索引的关系我们就不多说了，可以理解为单表索引的设置技巧

两表关联等值查询，在没有任何索引的情况下，Oracle倾向于走散列连接这种算法，因为散列连接的算法本身是比较高效且先进的。散列连接需要在 PGA中的 HASH_AREA_SIZE中完成，因此增大HASH_ARAE_SIZE也是优化散列连接的一种有效途径，一般在内存自动管理的情况下，

只要加大PGA区的大小即可。

排序合并连接与索引

索引对于嵌套循环连接来说非常重要，既要考虑驱动表的限制条件上的索引，又要考虑被驱动表的连接条件上的索引；而索引对于散列连接来说，仅需考虑限制条件上的索引是否能用上索引，连接条件上的索引是不能发挥作用的；排序合并连接和散列连接又有差别，排序合并连接上的连接条件虽然没有检索的作用，却有消除排序的作用

发现排序不可避免，而且是t1表和t2表分别进行排序。

排序减少了1次！

索引本身排序，所以可以有效地避免排序合并连接中的排序。不过Oracle的排序合并连接本身是有缺陷的，在连接条件的两个列都建过索引，却只能消除一张表的排序，这点Oracle一直不承认，直到Oracle 11g，其官方文档中才承认了这一点。

在某些特定的场合下，还是可以考虑在排序合并连接场合中，对连接条件列建索引，以消除一张表的排序，提升效率。

和散列连接类似的优化思路，就是增大内存排序区，避免在排序尺寸过大时在磁盘中排序。”