三种表连接方式 优化总结

4个角度剖析经典连接         NL                    hashjoin                     MERGE  sort JOIN
表的访问次数           NL驱动表访问多少次        两表各自只访问1次               与hashjoin一样
                        被驱动表就访问多少次      或0次


表的驱动顺序与性能     与驱动表顺序有关          类似NL，也与驱动顺序有关         与驱动顺序无关


表连接是否有排序       NL无排序                  hash join无排序，（消耗内存）    有排序产生   


各连接的使用限制        支持各种写法              不支持><like<>等，限制最多     支持><但不支持like<>;


3中连接优化要点：
NL：1.请确保用在扫描的OLTP场景
    2.驱动表的限制条件要考虑建立索引
    3.被驱动表的连接条件要考虑建立索引
    4.确保小结果集先驱动，大的被驱动
Hash Join
    1.适用场景：请确保用在扫描的OLAP场景；明确该sql是否限制Hash join；两表无任何索引倾向hash join；
    2.被驱动表的连接条件要有索引，被驱动表的限制条件是用不上索引；
    3.两表的限制条件有索引（看返回量)
    4.保证小结果集先驱动，大的再驱动
    5.尽量保证pga能容纳hash运算；
merge sort join
   适用场景：请确保用在全扫描的OLAP场景，sql是否限制Merge sort join
   1.两表的限制条件有索引（看返回量）；
   2.连接条件索引消除排序（不完美）；
   3.避免取多余列致排序的尺寸太大
   4.pga能完成排序，避免磁盘排序；
  
    








1、嵌套循环链接（USE_NL）
主要消耗的相关资源：CPU，磁盘I/O
特点：在网上看到很多关于这方面的帖子，说小表作为驱动表，其实这种说话不完全正确，其实带条件的大表返回较少行的结果集也可以作为驱动表（外部表）。此表连接在当有高选择性索引或进行限制性搜索时候效率较高，可以快速返回第一次的搜索结果。
缺点：当索引丢失或查询条件限制不够时，效率很低。
优化要点：




2、排序合并连接（USE_MERGE）
主要消耗的相关资源：内存，临时表空间
特点：在排序合并连接中，ORACLE分别将第一个源表、第二个源表按他们各自要连接的列排序，然后将两个已经排序的源表合并。由于没有索引，排序合并连接在合并两表钱将强制对他们进行排序，两表都需要进行全表扫描，所以表名在FROM自居中出现的次序并不重要。当缺乏索引或搜索条件模糊时，该类型的连接比嵌套循环连接更加有效。
缺点：所有表都需要排序，它为最优化的吞吐量而设计，并且在结果没有全部找到之前不返回数据。
3、散列连接（USE_HASH）
主要消耗的相关资源：内存，临时表空间
特点：在散列连接中，ORACLE访问第一张表（通常是连接结果中较小的表），并且在内存中建立一张基于连接键的散列表，然后他扫描另外一张表（较大的表），并根据散列表检测是否有匹配的记录。当缺乏索引或搜索条件模糊时，该类型的连接比嵌套循环连接更加有效。通常比排序合并连接更快。
缺点：为建立散列表，需要大量的内存，第一次结果返回慢，如果在磁盘上操作，速度将更慢。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25462274/viewspace-2117190/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25462274/viewspace-2117190/