表连接 nested loop hash join sort merge

表连接

1 、优化器如何执行Join

• 访问路径

• 连接方法

   连接方法包括： nested loop, sort merge, cartesian, and hash joins.

• 连接顺序

2、连接方法

    Nested Loop Joins

        使用情景：

        连接小子集数据。

        连接条件可高效访问第二个表。

 

3、Nested Loop Join 包括以下步骤：

            优化器决定驱动表并指定为Outer table.

            另一个表指定为Inner table.

           驱动表中的每一行与inner表中的相应记录join。

 

 

4、何时使用Nested Loop Join  

   当连接很少的数据行，并且两表之间有很好的驱动条件时优化器使用Nested Loop Join.从外outer loop 到inner loop ,在执行计划中表的连接顺序是相当重要的。

   Outer loop是驱动行源，它产生一个驱动连接条件的集合。这些行源可以是通过索引或者全部扫描的表访问，也可以是其他操作产生。如：一个nested loop join 的输出可以作为令一个nested loop join的输入。

   inner loop 是outer loop 返回的每一行的迭代，理想状况是通过索引扫描。

 

 

对于被连接的数据子集较小的情况，嵌套循环连接是个较好的选择。

 

Example :

SQL> select cpc.prod_id ,cc.channel_id 
  from  xezf.cfg_channel cc,xezf.cfg_prod_channel cpc
     where cc.channel_id=cpc.channel_id  and cc.channel_id=101;   

108 rows selected.

Execution Plan

----------------------------------------------------------
Plan hash value: 1170742108

-------------------------------------------------------------------------------------------------
| Id  | Operation       | Name   | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |    |   148 |  1924 |    67   (3)| 00:00:01 |
|   1 |  NESTED LOOPS       |    |   148 |  1924 |    67   (3)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN   | PK_SALESCHANNEL1 |     1 |     4 |     0   (0)| 00:00:01 |
|*  3 |   INDEX FAST FULL SCAN| IDX_CFG_PROD_CHANNEL_PC |   148 |  1332 |    67   (3)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

 

   2 - access("CC"."CHANNEL_ID"=101)
   3 - filter("CPC"."CHANNEL_ID"=101)

 

 

 

5、Nested Loop Join Hints

如果优化器选择了其他的连接方式，也可以使用USE_NL(table1 table2)提示，table1,table2 都是连接表的别名。

 

 

6、Hash Joins

   做大数据集连接是采用 Hash Join.优化器使用小表或者数据源在内存中建立一张连接键的hash表。然后扫描大表，探测大表来找到与hash表匹配的列。

   这种方式适合小表完全可以放在内存中的情况。这样总成本就是访问两个表的成本之和。但是在表很大的情况下并不能完全放入内存，这时优化器会将它分割成若干不同的分区，不能放入内存的部分就把该分区写入磁盘的临时段，此时要有较大的临时段从而尽量提高I/O 的性能。

 

7、Hash Join Hints

   可以使用USE_HASH(table1 table2)提示来强制使用hash join。

 

 

SQL> select cpc.prod_id ,cc.channel_id 
  from  xezf.cfg_channel cc,xezf.cfg_prod_channel cpc
     where cc.channel_id=cpc.channel_id ;

83166 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 769605206

-------------------------------------------------------------------------------------------------
| Id  | Operation       | Name   | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |    | 79953 |  1015K|    69   (5)| 00:00:01 |
|*  1 |  HASH JOIN       |    | 79953 |  1015K|    69   (5)| 00:00:01 |
|   2 |   INDEX FULL SCAN     | PK_SALESCHANNEL1 |   218 |   872 |     1   (0)| 00:00:01 |
|   3 |   INDEX FAST FULL SCAN| IDX_CFG_PROD_CHANNEL_PC | 79953 |   702K|    66   (2)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("CC"."CHANNEL_ID"="CPC"."CHANNEL_ID")

 

8、什么情况下使用Hash Joins

          若果使用等值连接并下面的条件成立时优化器使用Hash Join:

               1）、 大数据连接

               2）、小表的大部分数据需要连接

 

----通常是两个表数据量差别很大时候

 

9、Sort Merge Joins

   排序合并连接可以连接来自两个独立源的列。Hash joins通常比排序合并连接好，但下列情况下，排序合并连接优于Hash Join

      行源已经排过序。

      不在需要排序操作。

  但是，如果排序合并连接牵涉到选择了一个较慢的访问方式（索引访问方式选择了全表扫描），此时排序合并连接的优势将失去。

 

 排序合并连接在当连接的两表是非等值条件时是非常有用的，例如：<=  、 >、>=、<。

 在合并连接中，没有驱动表的概念，连接经过如下两步：

          排序连接操作：两个输入都根据连接键进行排序。

          合并连接操作：将排序列表进行合并汇聚。

 

10、何时优化器使用 Sort Merge Joins

    两表 连接条件不是等值连接。

    由于排序所需的其他操作，优化器发现使用排序合并连接代价比Hash join小。

 

       通常使用在没有索引，并且数据已经排序的情况。

11、Sort Merge Join Hints

   通过使用USE_MERGE（table1  table2)提示强制使用排序合并连接。

 

 

SQL>  select /*+ USE_MERGE(cc cpc) */ cpc.prod_id ,cc.channel_id 
  from  xezf.cfg_channel cc,xezf.cfg_prod_channel cpc
     where cc.channel_id=cpc.channel_id ;  2    3 

83166 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 193704876

----------------------------------------------------------------------------------------------------------
| Id  | Operation        | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time  |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |    | 79953 |  1015K|  |   380   (3)| 00:00:05 |
|   1 |  MERGE JOIN        |    | 79953 |  1015K|  |   380   (3)| 00:00:05 |
|   2 |   INDEX FULL SCAN      | PK_SALESCHANNEL1  |   218 |   872 |  |     1   (0)| 00:00:01 |
|*  3 |   SORT JOIN        |    | 79953 |   702K|  3144K|   379   (3)| 00:00:05 |
|   4 |    INDEX FAST FULL SCAN| IDX_CFG_PROD_CHANNEL_PC | 79953 |   702K|  |    66   (2)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("CC"."CHANNEL_ID"="CPC"."CHANNEL_ID")
       filter("CC"."CHANNEL_ID"="CPC"."CHANNEL_ID")

 

 

12、Cartesian Joins

 

 

当连接的表没有任何连接条件时。