Oracle中表的连接及其调整

http://www.51cto.com/art/200702/41041.htm

• 摘要：在日常基于数据库应用的开发过程中，我们经常需要对多个表或者数据源进行关联查询而得出我们需要的结果集。那么Oracle到底存在着哪几种连接方式？优化器内部又是怎样处理这些连接的？哪种连接方式又是适合哪种查询需求的？
• 标签：Oracle  表  连接  调整

只有对这些问题有了清晰的理解后，我们才能针对特定的查询需求选择合适的连接方式，开发出健壮的数据库应用程序。选择合适的表连接方法对SQL语句运行的性能有着至关重要的影响。下面我们就Oracle常用的一些连接方法及适用情景做一个简单的介绍。

一、嵌套循环连接（Nested Loop）

嵌套循环连接的工作方式是这样的：

1、Oracle首先选择一张表作为连接的驱动表，这张表也称为外部表（Outer Table）。由驱动表进行驱动连接的表或数据源称为内部表（Inner Table）。

2、提取驱动表中符合条件的记录，与被驱动表的连接列进行关联查询符合条件的记录。在这个过程中，Oracle首先提取驱动表中符合条件的第一条记录，再与内部表的连接列进行关联查询相应的记录行。在关联查询的过程中，Oracle会持续提取驱动表中其他符合条件的记录与内部表关联查询。这两个过程是并行进行的，因此嵌套循环连接返回前几条记录的速度是非常快的。在这里需要说明的是，由于Oracle最小的IO单位为单个数据块，因此在这个过程中Oracle会首先提取驱动表中符合条件的单个数据块中的所有行，再与内部表进行关联连接查询的，然后提取下一个数据块中的记录持续地循环连接下去。当然，如果单行记录跨越多个数据块的话，就是一次单条记录进行关联查询的。

3、嵌套循环连接的过程如下所示：

NESTED LOOP 

我们可以看出这里面存在着两个循环，一个是外部循环，提取驱动表中符合条件的每条记录。另外一个是内部循环，根据外循环中提取的每条记录对内部表进行连接查询相应的记录。由于这两个循环是嵌套进行的，故此种连接方法称为嵌套循环连接。

嵌套循环连接适用于查询的选择性强、约束性高并且仅返回小部分记录的结果集。通常要求驱动表的记录（符合条件的记录，通常通过高效的索引访问）较少，且被驱动表连接列有唯一索引或者选择性强的非唯一索引时，嵌套循环连接的效率是比较高的。比如下面这个查询是选用嵌套循环连接的典型例子：

SQL> select e.empno,e.ename,e.job,d.dname2 from emp e,dept d3 where e.deptno=d.deptno4 and e.empno=7900;
EMPNO ENAME JOB DNAME---------- ---------- --------- -------------- 7900 JAMES CLERK SALES
Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE1 0 NESTED LOOPS2 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'3 2 INDEX (UNIQUE SCAN) OF 'PK_EMP' (UNIQUE)4 1 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'5 4 INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE) 

在这个查询中，优化器选择emp作为驱动表，根据唯一性索引PK_EMP快速返回符合条件empno为7900的记录，然后再与被驱动表dept的deptno关联查询相应的dname并最终返回结果集。由于dept表上面的deptno有唯一索引PK_DEPT，故查询能够快速地定位deptno对应dname为SALES的记录并返回。

嵌套循环连接驱动表的选择也是连接中需要着重注意的一点，有一个常见的误区是驱动表要选择小表，其实这是不对的。假如有两张表A、B关联查询，A表有1000000条记录，B表有10000条记录，但是A表过滤出来的记录只有10条，这时候显然用A表当做驱动表是比较合适的。因此驱动表是由过滤条件限制返回记录最少的那张表，而不是根据表的大小来选择的。

在外连接查询中，如果走嵌套循环连接的话，那么驱动表必然是没有符合条件关联的那张表，也就是后面不加(+)的那张表。这是由于外连接需要提取可能另一张表没符合条件的记录，因此驱动表需要是那张我们要返回所有符合条件记录的表。比如下面这个查询，就是选择了emp表做为驱动表进行连接：

Roby@XUE> select emp.ename,dept.dname2  from emp,dept3  where emp.deptno=dept.deptno(+);ENAME      DNAME---------- --------------SMITHALLENWARD       SALESJONES      RESEARCHMARTIN     SALESBLAKE      SALESCLARK      ACCOUNTINGSCOTT      RESEARCHKING       ACCOUNTINGTURNER     SALESADAMS      RESEARCHJAMES      SALESFORD       RESEARCHMILLER     ACCOUNTING14 rows selected.Execution Plan----------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    14 |   308 |    15|   1 |  NESTED LOOPS OUTER          |         |    14 |   308 |    15|   2 |   TABLE ACCESS FULL          | EMP     |    14 |   126 |     3|   3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    13 |     1|*  4 |    INDEX UNIQUE SCAN         | DEPT_PK |     1 |       |     0

嵌套循环连接返回前几行的记录是非常快的，这是因为使用了嵌套循环后，不需要等到全部循环结束再返回结果集，而是不断地将查询出来的结果集返回。在这种情况下，终端用户将会快速地得到返回的首批记录，且同时等待Oracle内部处理其他记录并返回。如果查询的驱动表的记录数非常多，或者被驱动表的连接列上无索引或索引不是高度可选的情况，嵌套循环连接的效率是非常低的。

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

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

Roby@XUE> select emp.ename,dept.dname2 from emp,dept3 where emp.deptno=dept.deptno4 /
ENAME DNAME---------- -------------- CLARK ACCOUNTINGKING ACCOUNTINGMILLER ACCOUNTINGJONES RESEARCHSCOTT RESEARCHFORD RESEARCHADAMS RESEARCHTURNER SALESJAMES SALESWARD SALESMARTIN SALESBLAKE 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.dname2  from emp,dept3  where emp.deptno=dept.deptno;
ENAME      DNAME---------- --------------WARD       SALESJONES      RESEARCHMARTIN     SALESBLAKE      SALESCLARK      ACCOUNTINGSCOTT      RESEARCHKING       ACCOUNTINGTURNER     SALESADAMS      RESEARCHJAMES      SALESFORD       RESEARCHMILLER     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.dname2 from emp,dept3 where emp.deptno=dept.deptno(+);ENAME DNAME---------- -------------- MILLER ACCOUNTINGKING ACCOUNTINGCLARK ACCOUNTINGFORD RESEARCHADAMS RESEARCHSCOTT RESEARCHJONES RESEARCHJAMES SALESTURNER SALESBLAKE SALESMARTIN SALESWARD SALESALLENSMITH14 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将不会生效。

原文:http://www.51cto.com/art/200702/41041.htm