查看sql执行计划时的表连接方式

查看sql执行计划时的表连接方式

在查看sql执行计划时，我们会发现表的连接方式有多种，本文对表的连接方式进行介绍以便更好看懂执行计划和理解sql执行原理。

一、连接方式：
      嵌套循环（Nested  Loops （NL））
      （散列）哈希连接（Hash Join （HJ））
      （归并）排序合并连接(Sort Merge Join (SMJ) )

二、连接说明：
      1.Oracle一次只能连接两个表。不管查询中有多少个表，Oracle 在连接中一次仅能操作两张表。
      2.当执行多个表的连接时，优化器从一个表开始，将它与另一个表连接；然后将中间结果与下一个表连接，以此类推，直到处理完所有表为止。

三、表连接详解：

        1.嵌套循环（Nested  Loops （NL））：
     
              嵌套循环实现机制(伪代码)：
                  For r1 in (select rows from table_1 where colx={value})
                  loop
                              for r2 in (select rows from table_2 that match current row from table_1)
                            loop
                                  output values from current row of table_1 and current row of table_2;
                            end loop;
                End loop;
           
    这段代码由两个循环构成。
    嵌套循环中的这两个表通常称为外部表（outer table)和内部表(inner table)。
    在嵌套循环连接中,外部表又称为驱动表(driver table)
  伪代码中：table_1为驱动表，table_2为内表
  从伪代码中可以看出该连接过程就是一个2层嵌套循环，所以外层循环的次数越少越好，这也就是我们为什么将小表或返回较小结果集的表作为驱动表的原因。
 
  NEST LOOP JOIN COST =    从第一个表取得数据的成本       从第一个表得到结果的基数 Х 对第二个表访问一次的成本
所以嵌套循环一般适合于驱动表记录集比较少（<10000）且内表有高效索引访问方式。
  使用USE_NL(table_1 table_2)可强制CBO 执行嵌套循环连接。
 
  驱动表确定：驱动表【select rows from table_1 where colx={value} 】一般为根据where条件能得到较小结果集的表，而不一定是整个表记录比较小的表。
               
      2.（散列）哈希连接（Hash Join （HJ））：
              Hash join一般用于一张小表和一张大表进行join时。在绝大多数情况下，hash join效率比其他join方式效率更高。
             
            对于hash join的详细理解，可参看网上一篇文章写的比较透彻：http://www.hellodba.com/reader.php?ID=144&lang=cn

           
      3.排序合并连接(Sort Merge Join (SMJ) )：
            通常情况下散列连接的效果都比排序合并连接要好，然而如果行源已经被排过序，在执行排序合并连接时不需要再排序了，这时排序合并连接的性能会优于散列连接。可以使用USE_MERGE(table_1 table_2)来强制使用排序合并连接。
         
            过程：将两个表排序，然后将排序后两个表合并。

四、连接方式总结：

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

五、连接方式应用场景：

1.　哈希连接只适用于等值连接。
2.　嵌套循环是行源连接方式，只适合小量数据连接。
      哈希连接和排序合并连接是集合连接方式，适合大量数据连接。
3.　在等值连接方式下，返回少量记录（<10000）且内部表在连接列上存在索引，适合嵌套循环连接。若返回大量记录则适合哈希连接。
4.　在等值连接方式下，两个行源集合都很大，若连接列是高基数列，则适合哈希连接，否则适合排序合并连接。
5.　嵌套循环连接可以先返回已经连接的行，而不必等待所有的连接操作处理完才返回数据。而其它两种连接方式则不行。
6.  排序合并连接的两个数据集可以并行处理,而嵌套循环和哈希连接不能.




NLJ：
根据连接键，把小表的每一行，和大表的每一行做对比。 一般情况下会对大表连接键上建index。
成本计算：读小表的行 （小表的每一行×读取大表的行）

SMJ:
读取小表和大表的行，根据连接键排序，然后根据排序后的数据集（小表的和大表的）合并进行连接。
理想状态：2个表的排序操作都能在内存进行
常规情况：2阶段进行：
                  1.sort run阶段：数据读取到内存，排序，写出到临时表空间。直到所有的row sourse完成排序。
                  2.merge阶段：之前每次写到临时表空间的数据（即sort run）被重新读入到内存，进行merge。
成本计算：读取小表的行 写小表的run sort到temp表空间
                  读取大表的行 写大表的run sort到temp表空间
                  cpu对小表和大表的排序消耗

join连接中的并行机制：
能在NLJ和SMJ中使用。并发查询的执行计划是一个树形结构（DFO），每个树上的DFO节点是一个sql操作过程，并且能把该操作过程能指派到一个query slave进程中。

Hash Join：
用在条件为等号的环境下，hash连接的效率要比SMJ和NLJ要高（如果索引的blevel比较高），且hash join不要求一定要有索引。
hash join的基本算法是在内存中建立hash table，小表叫做build input，理想状态下，build input在内存中；大表叫做probe input。
在实际的情况下，build input不一定能完全放在内存中，此时，和probe input一样，build input的溢出部分，会在磁盘上用hash函数分割成小的不连续的分区。

hash连接分2个阶段进行：
1.partitioning阶段：即在内存中存放build input，若放不下，则和probe input一样，在磁盘上利用hash函数将input分割成小的不连续的分区。
1.join阶段：在相同的键值上，将build input和probe input的分区进行一一配对，并且join。
以上的hash连接的算法也叫grace join。