Oracle中表的连接及其调整(2)

浅浅的花香味﹌ 2022-08-27 05:43 187阅读 0赞

**二、排序合并连接(Sort Merge)**

排序合并连接的方法非常简单。在排序合并连接中是没有驱动表的概念的，两个互相连接的表按连接列的值先排序，排序完后形成的结果集再互相进行合并连接提取符合条件的记录。相比嵌套循环连接，排序合并连接比较适用于返回大数据量的结果。以下为排序合并连接的例子：

<table> 
 <tbody> 
  <tr> 
   <td> <pre><p><a href="mailto:Roby@XUE" rel="nofollow"><font style="FONT-SIZE: 14px">Roby@XUE</font></a><font style="FONT-SIZE: 14px">&gt; select emp.ename,dept.dname

2 from emp,dept

3 where emp.deptno=dept.deptno

4 /</font></p><p><font style="FONT-SIZE: 14px">ENAME DNAME

---------- --------------

CLARK ACCOUNTING

KING ACCOUNTING

MILLER ACCOUNTING

JONES RESEARCH

SCOTT RESEARCH

FORD RESEARCH

ADAMS RESEARCH

TURNER SALES

JAMES SALES

WARD SALES

MARTIN SALES

BLAKE SALES</font></p><p><font style="FONT-SIZE: 14px">12 rows selected.</font></p><p><font style="FONT-SIZE: 14px">Execution Plan</font></p><p><font style="FONT-SIZE: 14px">---------------------------------------------------------------

---------------------------------------------------------------

| 0 | SELECT STATEMENT | | 12 | 264 | 8 (25)| 00:00:01 |

| 1 | MERGE JOIN | | 12 | 264 | 8 (25)| 00:00:01 |

| 2 | SORT JOIN | | 4 | 52 | 4 (25)| 00:00:01 |

| 3 | TABLE ACCESS FULL| DEPT | 4 | 52 | 3 (0)| 00:00:01 |

|* 4 | SORT JOIN | | 12 | 108 | 4 (25)| 00:00:01 |

|* 5 | TABLE ACCESS FULL| EMP | 12 | 108 | 3 (0)| 00:00:01 |</font></p></pre> </td> 
  </tr> 
 </tbody> 
</table>

可以看得出来上述查询首先按dept、emp两张表的deptno先排序，然后排序好的结果集再进行合并连接返回最终的记录。

排序合并连接在数据表预先排序好的情况下效率是非常高的，也比较适用于非等值连接的情况，比如>、>=、<=等情况下的连接（哈希连接只适用于等值连接）。由于Oracle中排序操作的开销是非常消耗资源的，当结果集很大时排序合并连接的性能很差，于是Oracle在7.3之后推出了新的连接方式——哈希连接。

**三、哈希连接（Hash join）**

哈希连接分为两个阶段，如下。

1、构建阶段：优化器首先选择一张小表做为驱动表，运用哈希函数对连接列进行计算产生一张哈希表。通常这个步骤是在内存（hash\_area\_size）里面进行的，因此运算很快。

2、探测阶段：优化器对被驱动表的连接列运用同样的哈希函数计算得到的结果与前面形成的哈希表进行探测返回符合条件的记录。这个阶段中如果被驱动表的连接列的值没有与驱动表连接列的值相等的话，那么这些记录将会被丢弃而不进行探测。关于哈希连接更深层次的原理可以参考Itpub上网友logzgh发表的“hash join算法原理”帖子（[http://www.itpub.net/showthread.php?threadid=315494][http_www.itpub.net_showthread.php_threadid_315494]）。

以下为哈希连接的一个例子：

<table> 
 <tbody> 
  <tr> 
   <td> <pre><p><a href="mailto:Roby@XUE" rel="nofollow"><font style="FONT-SIZE: 14px">Roby@XUE</font></a><font style="FONT-SIZE: 14px">&gt; select /**//*+ use_hash(emp,dept) */ emp.ename,dept.dname

2  from emp,dept

3  where emp.deptno=dept.deptno;</font></p><p><font style="FONT-SIZE: 14px">ENAME      DNAME

---------- --------------

WARD       SALES

JONES      RESEARCH

MARTIN     SALES

BLAKE      SALES

CLARK      ACCOUNTING

SCOTT      RESEARCH

KING       ACCOUNTING

TURNER     SALES

ADAMS      RESEARCH

JAMES      SALES

FORD       RESEARCH

MILLER     ACCOUNTING</font></p><p><font style="FONT-SIZE: 14px">12 rows selected.</font></p><p><font style="FONT-SIZE: 14px">Execution Plan

---------------------------------------------------------------------------

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |    12 |   264 |     7  (15)| 00:00:01 |

|*  1 |  HASH JOIN         |      |    12 |   264 |     7  (15)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| DEPT |     4 |    52 |     3   (0)| 00:00:01 |

|*  3 |   TABLE ACCESS FULL| EMP  |    12 |   108 |     3   (0)| 00:00:01 |</font></p></pre> </td> 
  </tr> 
 </tbody> 
</table>

在这个查询中优化器首先选择dept这张表为驱动表，对列deptno运算哈希函数构建一张哈希表，然后再对被驱动表emp的deptno列运算同样的哈希函数计算得到的结果进行探测，最终连接得出符合条件的记录。

同嵌套循环外连接一样，哈希循环外连接的驱动表同样是没有符合条件关联的那张表。如下述例子：

2 from emp,dept

3 where emp.deptno=dept.deptno(+);

ENAME DNAME

---------- --------------

MILLER ACCOUNTING

KING ACCOUNTING

CLARK ACCOUNTING

FORD RESEARCH

ADAMS RESEARCH

SCOTT RESEARCH

JONES RESEARCH

JAMES SALES

TURNER SALES

BLAKE SALES

MARTIN SALES

WARD SALES

ALLEN

SMITH

14 rows selected.

Execution Plan

--------------------------------------------------------------

--------------------------------------------------------------

| 0 | SELECT STATEMENT | | 14 | 308 | 7 (15)| 00:00:01 |

|* 1 | HASH JOIN OUTER | | 14 | 308 | 7 (15)| 00:00:01 |

| 2 | TABLE ACCESS FULL| EMP | 14 | 126 | 3 (0)| 00:00:01 |

| 3 | TABLE ACCESS FULL| DEPT | 4 | 52 | 3 (0)| 00:00:01 |

--------------------------------------------------------------</font></p></pre> </td> 
  </tr> 
 </tbody> 
</table>

哈希连接比较适用于返回大数据量结果集的连接。使用哈希连接必须是在CBO模式下，参数hash\_join\_enabled设置为true，且只适用于等值连接。从Oracle9i开始，哈希连接由于其良好的性能渐渐取代了原来的排序合并连接。

**四、跟表连接有关的几个HINT**

（1）use\_nl（t1,t2）：表示对表t1、t2关联时采用嵌套循环连接。 （2）use\_merge（t1,t2）：表示对表t1、t2关联时采用排序合并连接。 （3）use\_hash（t1,t2）：表示对表t1、t2关联时采用哈希连接。 （4）leading(t)：表示在进行表连接时，选择t为驱动表。 （5）ordred：要求优化器按from列出的表顺序进行连接。

需要注意的是在Oracle使用hint时,如果SQL语句中表用别名的话,那么hint中必须使用表的别名,否则hint将不会生效。

[http_www.itpub.net_showthread.php_threadid_315494]: http://www.itpub.net/showthread.php?threadid=315494