Oracle SQL优化③——表的连接方式

前言

表（结果集）与表（结果集）之间的连接方式非常重要，如果CBO选择了错误的连接方式，本来几秒就能出结果的SQL可能执行一天都执行不完。如果想要快速定位超大型SQL性能问题，就必须深入理解表连接方式。在多表关联的时候，一般情况下只能是两个表先关联，两表关联之后的结果再和其他表/结果集关联，如果执行计划中出现了Filter,这时可以一次性关联多个表。

一.嵌套循环（NESTED LOOPS）

嵌套循环的算法：驱动表返回一行数据，通过连接列传值给被驱动表，驱动表返回多少行，被驱动表就要被扫描多少次。
嵌套循环可以快速返回两表关联的前几条数据，如果SQL中添加了HINT:FIRST_ROWS,在两表关联的时候，优化器更倾向于嵌套循环。
嵌套循环驱动表应该返回少量数据。如果驱动表返回了100万行，那么被驱动表就会被扫描100万次。这个时候SQL会执行很久，被驱动表会被误认为热点表，被驱动表连接列的索引也会被误认为热点索引。
嵌套循环被驱动表必须走索引。如果嵌套循环被驱动表的连接列没包含在索引中，那么被驱动表就只能走全表扫描，而且是反复多次全表扫描。当被驱动表很大的时候，SQL就执行不出结果。
嵌套循环被驱动表走索引只能走INDEX UNIQUE SCAN或者INDEX RANGE SCAN。
嵌套循环被驱动表不能走TABLE ACCESS FULL，不能走INDEX FULL SCAN，不能走INDEX SKIP SCAN，也不能走INDEX FAST FULL SCAN。
嵌套循环被驱动表的连接列基数应该很高。如果被驱动表连接列的基数很低，那么被驱动表就不应该走索引，这样一来被驱动表就只能进行全表扫描了，但是被驱动表也不能走全表扫描。
两表关联返回少量数据才能走嵌套循环。前面提到，嵌套循环被驱动表必须走索引，如果两表关联，返回100万行数据，那么被驱动表走索引就会产生100万次回表。回表一般是单块读，这个时候SQL性能极差，所以两表关联返回少量数据才能走嵌套循环。

二.HASH连接（HASH JOIN）

上面提到，两表关联返回少量数据应该走嵌套循环，两表关联返回大量数据应该走HASH连接。
HASH连接的算法：两表等值关联，返回大量数据，将较小的表选为驱动表，将驱动表的“select列和join列”读入PGA中的work area,然后对驱动表的连接列进行hash运算生成hash table,当驱动表的所有数据完全读入PGA中的work area之后，再读取被驱动表（被驱动表不需要读入PGA中的work area),对被驱动表的连接列也进行hash运算，然后到PGA中的work area去探测hash table，找到数据就关联上，没找到数据就没关联上。哈希连接只支持等值连接。

再次强调，嵌套循环被驱动表需要扫描多次，HASH连接的被驱动表只需要扫描一次。
HASH连接，执行计划中的Used-Mem表示HASH连接消耗了多少PGA,当驱动表太大、PGA不能完全容纳驱动表时，驱动表就会溢出到临时表空间，进而产生磁盘HASH连接，这时候HASH连接性能会严重下降。嵌套循环不需要消耗PGA。
嵌套循环每循环一次，会将驱动表连接列传值给被驱动表的连接列，也就是说嵌套循环会进行传值。HASH连接没有传值的过程。在进行HASH连接的时候，被驱动表的连接列会生成HASH值，到PGA中去探测驱动表所生成的hash table。HASH连接的驱动表与被驱动表的连接列都不需要创建索引。
OLTP环境一般是高并发小事物居多，此类SQL返回结果很少，SQL执行计划多以嵌套循环为主，因此OLTP环境SGA设置较大，PGA设置较小（因为嵌套循环不消耗 PGA)。而OLAP环境多数SQL都是大规模的ETL,此类SQL返回结果集很多，SQL执行计划通常以HASH连接为主，往往要消耗大量PGA，所以OLAP系统PGA设置较大。

三.排序合并连接（SORT MERGE JOIN）

上文提到HASH连接主要用于处理两表等值关联返回大量数据。
排序合并连接主要用于处理两表非等值关联，比如>，>=，<， <=,< ，但是不能用于instr、substr、like、regexp_like关联，instr、substr、like、regexp_like关联只能走嵌套循环。现有如下SQL。

select * from a