Oracle Join机制

oracle join 学习  
级别：基本功


内容：


查询优化器如何执行连接语句
查询优化器如何为连接语句选择优化方案
嵌套循环连接(nested loop)
哈希连接(hash)
排序合并连接(sort merge)
笛卡尔连接(cartesian)
外连接
 


 


   一、查询优化器如何执行连接语句


   为了给连接语句选择执行计划，优化器必须确定相关的决定：


   访问路径
           关于简单的语句，优化器必须选择一个访问路劲从每个表格中返回数据.


   连接方法
           为连接每对行源，oracle必须执行连接操作。连接的方法包含前文列出的4种。


   连接顺序
           连接总是需要顺序的。例如三个表A,B,C。A JOIN B产生结果集合D，D再和C连接产生最终结果。连接就是这么完成的。oracle从来不会多个表同时连接的。ORACLE11g或者是优化器是否有那个功能，暂时不清楚。


   二、查询优化器如何为连接语句选择优化方案


   在选择执行计划的时候，优化器需要考虑下列因素：


优化器首先确定:是否两个或者更多个表的连接的确定结果的行源最多包含一行。(有点拗口）。意思就是说哪些表直接的连接他们的行源最多只有一行的。判断的依据是：表格包含UNIQUE以及PRIMARY KEY。如果存在这样的情况，那么优化器把这些表放置在连接顺序的首位。然后优化器优化剩余的表格。
如果存在外连接条件，带外连接操作的表格的顺序必须排在其它连接的后面。优化器不会破坏这种连接顺序的。类似地，当一个子查询已经转化为antijoin或者semijoin的时候，来自子查询的表格的必须排在外查询块中连接的或者相关的表格后（既不连接也不相关的，暂不考虑）。无论如何hash antijoin和semijoin在特定的环境下是可以覆盖这种排序条件的。
     优化器针对可能的连接顺序，连接方法和可行的访问路径，产生一些执行计划。然后优化器估计每个计划计划并选择具有最低消耗（成本)的那个。优化器估计成本的有以下的途径：


循环嵌套连接操作的成本基于：读外表选中的每行以及对应匹配的内表行到内存中。优化器利用数据字典中的统计信息来估计这些成本.
排序合并连接的成本基于（很大程度地）：读取所有的源到内存，并排序。
哈希连接的成本很大程度基于：为连接的输入方建立一个哈希表和使用连接中另外一个表的行在哈希表中做检索。
      优化器也会考虑其它的因素。例如：


一个较小的排序区域（内存）可能提高排序合并连接的成本 ，因为在一个较小的排序区域中排序需要花费更多的CPU时间和I/O   。
相对于一个循环嵌套连接而言，一个较大的多块读计数可能减少一个排序合并连接的成本。如果大量的连续块能够在一次I/O中读入，那么循环嵌套连接中内表上的索引就较不可能提高性能，相对全表扫描而言(这是明显的）。多块读计数通过初始化参数DB_FILE_MULTIBLOCK_READ_COUNT来设置。
     对于优化器而言，优化器的默认连接顺序可以被ORDERED提示覆盖。但如果ORDERED提示所设置的连接顺序破坏了外连接的规则，那么优化器会忽略这个提示。同样地，连接的方法也可以通过提示来覆盖。


     三、嵌套循环连接(nested loop)


     谨记！


     循环嵌套连接在连接小数据集合，且连接条件是个高效访问另外一个表的途径的时候，尤其有效。


     有一点非常重要：确保内表是依赖外表的。


     内外（inner outer)是语句访问的次序来定的，在前的叫OUTER，在后的叫INNER。有点类似于循环中的内外访问方式。


     如果内表的访问路径和外表无关，那么每次的迭代外部循环都需要提取同样的行，这会客观地降低性能。如果是这样的情况，用哈希连接来连接两个互不想干的行源会更好一些。


     一个循环嵌套连接包含下列步骤：


     1.优化器确定驱动表，并确定为外表。


     2.另外一个表格定位为内表。


     3.对于外表的每一行，oracle访问内表的所有行。外部的循环访问外表的每一行，内部循环访问内表的每一行。在执行计划中，外表循环出现在内表循环中，形如：


        NESTED LOOPS 
            outer_loop 
            inner_loop


    什么时候优化器使用嵌套循环连接


    当连接的数据量较小的时候，且两个表之间存在较好的驱动条件，优化器会使用循环嵌套连接 。由于自外向内驱动，所以执行计划中表的顺序也很重要。


    外部循环是驱动的行源。它负责产生一系列的行来驱动连接条件。行源可以是通过索引扫描或者全表扫描的表。同样，行可以通过任意其它的操作来产生。例如，一个循环嵌套连接的输出可以作为另外一个循环嵌套连接的行源。


    内部循环是基于外部循环的每一行进行迭代的，如果有索引扫描，那么就理想了。如果内部循环的访问路径和外部循环无关，那么可以使用笛卡尔结果；对于外部循环的每个迭代，内部循环产生相同的一系列行。因此，当两个互不依赖的行源要连接的时候，最好采取其它的连接方法。


    循环嵌套连接提示


    如果优化器采取其它连接方法，那么我们可以使用USER_NL( T1 T2)的提示来采取循环嵌套连接。


    针对一些SQL例子，数据足够小，以至于优化器宁可执行全表扫描并使用哈希连接。


    基于循环嵌套的循环嵌套


    前文有提过，一个循环嵌套的输出可以作为另外一个循环嵌套的行源（通常是外部）。


   


    四、哈希连接(hash)


    哈希连接用于连接大数据集合。优化器使用两个表格（或者数据源）中较小的那个来在内存中建立一个基于连接键的哈希表。然后扫描更大的那个，并在哈希表中找到连接的行。


    这个方法最好在较小的那个能够被塞到内存的情况下用。成本将限制在对两个源的单独存取上。


    什么时候用哈希连接


    当两个表格使用同等连接（= ？），且下列任意条件成立的时候：


    大量的数据需要连接
    小表（源）的大量部分需要连接
     这时候，会采取哈希连接。


     还是有点混浊。


    哈希连接提示


    使用USER_HASH来建议优化器使用哈希连接。


    五、排序合并连接(sort merge)


    排序合并连接可用于连接两个互不相关的源。哈希连接通常比排序合并连接好。另外一方面，如果下列的条件都成立，那么排序合并连接优于哈希连接：


行源已经排序
没有必要一定排序
    无论如何，如果排序合并连接中使用较慢的存取（访问）方式（用索引扫描而不是全表扫描），那么使用排序合并连接的好处就可能没有了。


    当两个行源之间的连接条件是不等（但不是空值），例如<,<=,>=,>.排序合并连接在大数据集合上的连接做得比循环嵌套好。这种情况下，不能用哈希连接，除非有相等条件（=？）


    在合并连接中，不存在驱动表（源）的概念。连接包含两个步骤：


    1.排序连接操作：两个输入都是基于连接键进行排序。


    2.合并连接操作：排序后的列表合并在一起。


    如果输入早已按照连接列排序，那么排序的操作也可以忽略掉了。


    什么时候使用排序合并连接


    如果以下任意条件成立，优化器会采取排序合并而不是哈希连接：


连接条件不是等值连接
发现其它的操作需要排序，那么优化器会认为排序合并连接比哈希连接来得便宜。
    排序合并连接提示


    USE_MERGE提示。给出这个提示的时候，也许需要同时给出访问路径提示。


    存在这样情况：使用USER_MERGE提示所产生的计划比默认的计划好。例如，优化器会选择全表扫描以避免在一个查询上做排序操作。无论如何，在一个大表上使用索引和单块读比全表扫描来得费劲。


   


    六、笛卡尔连接(cartesian)


    如果两个表之间不存在连接条件，那么就用这个。


    什么时候用笛卡尔连接


    没有连接条件。某些情况下，两个表之间的普通过滤条件会被当作连接条件，而在另外一些情况下，例如如果有两个表都是和一个同样大的表连接，那么优化器可能会现在这两个小表之间产生一个笛卡尔乘积。（毫无疑问，这会大大减少I/O).


    笛卡尔连接提示


    使用ORDERED提示，可以让优化器使用笛卡尔连接。   


    七、外连接


     A LEFT JOIN B ON  B.COL1=A.COL2


     除了返回常规的，还需要返回B中没有匹配值的A的列。


     结合NESTED LOOP,HASH,SORT MERGE,FULL OUTER JOIN有多种OUTER join


     1.NESTED LOOP OUTER


     可能的情况：


能通过外表驱动内表
数据量够小，以至于循环嵌套方法是高效的。
 


     2.HASH OUTER


不能通过外表驱动内表
数据量够大，以至于哈希的方法才（？）是高效的。
    


     3.SORT MERGE OUTER


循环嵌套连接是低效的。因为数据量较大。
发现其它操作需要排序，于是SORT Merge比HASH来得好。
----------------------------