Oracle中表的连接及其调整(2)-优快云博客

本文详细介绍了SQL中的三种连接方法：嵌套循环连接、排序合并连接和哈希连接，并通过实例展示了每种连接方式的工作原理及执行计划。同时，还列举了一些用于指定连接类型的提示（hint），帮助读者更好地理解并应用到实际工作中。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

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

Roby@XUE> select emp.ename,dept.dname 2 from emp,dept 3 where emp.deptno=dept.deptno 4 /

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

12 rows selected.

Execution Plan

--------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------- | 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 |

可以看得出来上述查询首先按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）。

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

Roby@XUE> select /**//*+ use_hash(emp,dept) */ emp.ename,dept.dname 2 from emp,dept 3 where emp.deptno=dept.deptno;

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

12 rows selected.

Execution Plan --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 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 |

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

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

Roby@XUE> select /**//*+ use_hash(emp,dept) */ emp.ename,dept.dname 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 -------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------- | 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 | --------------------------------------------------------------

哈希连接比较适用于返回大数据量结果集的连接。使用哈希连接必须是在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将不会生效。