Oracle中表的连接及其调整

1、Oracle中表的连接及其调整只有对这些问题有了清晰的理解后，我们才能针对特定的查询需求选择合适的连 接方式，开发出健壮的数据库应用程序。选择合适的表连接方法对SQL语句运行 的性能有着至关重要的影响。下面我们就Oracle常用的一些连接方法及适用情景做一个简单的介绍。常用的表连接方式：a.嵌套循环连接(Nested Loop) b.排序合并连接(Sort Merge) c. 哈希连接(Hash join )一、嵌套循环连接(Nested Loop)嵌套循环连接的工作方式是这样的：1、 Oracle首先选择一张表作为连接的驱动表，这张表也称为外部表( Outer Table )。由驱动表进行驱

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

3、记录持续地循环连接下去。当然，如果单行记录跨越多个数据块的话，就是一次单条记录进行关联查询的。3、嵌套循环连接的过程如下所示:NESTED LOOP<Outer Loop>vlnner Loop>我们可以看出这里面存在着两个循环， 一个是外部循环，提取驱动表中符合条件 的每条记录。另外一个是内部循环，根据外循环中提取的每条记录对内部表进行 连接查询相应的记录。由于这两个循环是嵌套进行的，故此种连接方法称为嵌套 循环连接。嵌套循环连接适用于查询的选择性强、约束性高并且仅返回小部分记录的结果 集。通常要求驱动表的记录（符合条件的记录，通常通过高效的索引访问）较少, 且被驱动表连接

4、列有唯一索引或者选择性强的非唯一索引时，嵌套循环连接的效率是比较高的。比如下面这个查询是选用嵌套循环连接的典型例子：SQL> select e.emp no ,e.e name,e.job,d.d name2 from emp e,dept d3 where e.dept no=d.dept no4 and e.emp no=7900;EMPNO ENAME JOB DNAME7900 JAMES CLERK SALESExecuti on Pla n0 SELECT STATEMENT Optimizer=CHOOSE1 0 NESTED LOOPS2 1 TABLE ACCESS (

5、BY INDEX ROWID) OF 'EMP'3 2 INDEX (UNIQUE SCAN) OF 卩 K_EMP' (UNIQUE)4 1 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'5 4 INDEX (UNIQUE SCAN) OF 'PK DEPT' (UNIQUE)在这个查询中，优化器选择emp作为驱动表，根据唯一性索引 PK_EM快速返回 符合条件emp no为7900的记录，然后再与被驱动表dept的dept no关联查询相 应的dn ame并最终返回结果集。由于dept表上面的dept

6、no有唯一索引PK_DEPT 故查询能够快速地定位dept no对应dn ame为SALES的记录并返回。嵌套循环连接驱动表的选择也是连接中需要着重注意的一点，有一个常见的误区是驱动表要选择小表，其实这是不对的。假如有两张表A、B关联查询，A表有1000000条记录，B表有10000条记录，但是A表过滤出来的记录只有10条，这 时候显然用A表当做驱动表是比较合适的。因此驱动表是由过滤条件限制返回记 录最少的那张表，而不是根据表的大小来选择的。在外连接查询中，如果走嵌套循环连接的话，那么驱动表必然是没有符合条件关 联的那张表，也就是后面不加（+）的那张表。这是由于外连接需要提取可能另一 张表没符

7、合条件的记录，因此驱动表需要是那张我们要返回所有符合条件记录的 表。比如下面这个查询，就是选择了 emp表做为驱动表进行连接：RobyXUE5elect emp.e name,dept.d name2 from emp,dept3 where emp.dept no=dept.dept no (+);ENAMEDNAMESMITHALLENWARDSALESJONESRESEARCHMARTINSALESBLAKESALESCLARKACCOUNTINGSCOTTRESEARCHKINGACCOUNTINGTURNERSALESADAMSRESEARCHJAMESSALESFORDRESEAR

8、CHMILLERACCOUNTING14 rows selected.Executio n Pla n|0|SELECTSTATEMENT|114|308 |15|1 |NESTED LOOPSOUTER|114 |308|15| 2 |TABLE ACCESS FULL1EMP|14 |126 |3|3 |TABLEACCESBYINDEXROWIDDEPT11|13 |1|*4 |INDEX UNIQUE SCAN|DEPT_PK| 1 |1 0嵌套循环连接返回前几行的记录是非常快的， 这是因为使用了嵌套循环后，不需 要等到全部循环结束再返回结果集， 而是不断地将查询出来的结果集返回。 在

9、这 种情况下，终端用户将会快速地得到返回的首批记录，且同时等待 Oracle内部 处理其他记录并返回。如果查询的驱动表的记录数非常多， 或者被驱动表的连接 列上无索引或索引不是高度可选的情况，嵌套循环连接的效率是非常低的。二、排序合并连接(Sort Merge)排序合并连接的方法非常简单。在排序合并连接中是没有驱动表的概念的， 两个 互相连接的表按连接列的值先排序，排序完后形成的结果集再互相进行合并连接 提取符合条件的记录。相比嵌套循环连接，排序合并连接比较适用于返回大数据 量的结果。以下为排序合并连接的例子：RobyXUESelect emp.e name,dept.d name2 from

10、 emp,dept3 where emp.dept no=dept.dept no4 /ENAME DNAMECLARK ACCOUNTINGKING ACCOUNTINGMILLER ACCOUNTINGJONES RESEARCHSCOTT RESEARCHFORD RESEARCHADAMS RESEARCHTURNER SALES JAMES SALESWARD SALES MARTIN SALESBLAKE SALES12 rows selected.Executio n Pla n| Id | Operatio n | Name | Rows | Bytes | Cost (%CP

11、U)| 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

12、| 108 | 3 (0)| 00:00:01 |可以看得出来上述查询首先按dept、emp两张表的dept no先排序，然后排序好 的结果集再进行合并连接返回最终的记录。排序合并连接在数据表预先排序好的情况下效率是非常高的，也比较适用于非等值连接的情况，比如 、=、=等情况下的连接（哈希连接只适用于等值连接）。由于Oracle中排序操作的开销是非常消耗资源的，当结果集很大时排序合并连 接的性能很差，于是Oracle在7.3之后推出了新的连接方式一一哈希连接。三、哈希连接(Hash join )哈希连接分为两个阶段，如下。1、构建阶段：优化器首先选择一张小表做为驱动表，运用哈希函数对连接列进

13、行计算产生一张哈希表。通常这个步骤是在内存(hash_area_size )里面进行的， 因此运算很快。2、探测阶段：优化器对被驱动表的连接列运用同样的哈希函数计算得到的结果与前面形成的哈希表进行探测返回符合条件的记录。这个阶段中如果被驱动表的连接列的值没有与驱动表连接列的值相等的话，那么这些记录将会被丢弃而不进行探测。关于哈希连接更深层次的原理可以参考Itpub上网友发表的“ hash join算法原理”帖子()。以下为哈希连接的一个例子：RobyXUEselect /*/*+ use_hash(emp,dept) */ emp.e name,dept.d name2 from emp,de

14、pt3 where emp.dept no=dept.dept no;ENAMEDNAMEWARDSALESJONESRESEARCHMARTINSALESBLAKESALESCLARKACCOUNTINGSCOTTRESEARCHKINGACCOUNTINGTURNERSALESADAMSRESEARCHJAMESSALESFORDRESEARCHMILLERACCOUNTING12 rows selected.Executi on Pla n| Id| Operati on(%CPU)| Time| Name | Rows| Bytes | Cost|0 | SELECT STATEMEN

15、T |7(15)| 00:00:01 |112 |2641* 1 11HASH JOIN7(15)| 00:00:01 |1112 |2641 2 |13TABLE ACCESS FULL| DEPT |(0)| 00:00:01 |4 |521*3 |TABLE ACCESS FULL| EMP |12 |108|3(0)| 00:00:01 |在这个查询中优化器首先选择 dept这张表为驱动表，对列dept no运算哈希函数 构建一张哈希表，然后再对被驱动表emp的dept no列运算同样的哈希函数计算得到的结果进行探测，最终连接得出符合条件的记录。同嵌套循环外连接一样，哈希循环外连接的驱

16、动表同样是没有符合条件关联的那 张表。如下述例子：RobyXUESelect /*/*+ use_hash(emp,dept) */emp.e name,dept.d name2 from emp,dept3 where emp.dept no=dept.dept no (+);ENAME DNAMEMILLER ACCOUNTING KING ACCOUNTING CLARK ACCOUNTING FORD RESEARCH ADAMS RESEARCH SCOTT RESEARCH JONES RESEARCH JAMES SALESTURNER SALESBLAKE SALESMARTI

17、N SALESWARD SALESALLENSMITH14 rows selected.Executio n Pla n| Id | Operatio n | 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模式下(show parameter optimizer_mode) ，参数 hashoin_enabled 设置为 true，且只适用于等值连接。从 Oracle9i开始，哈希连接由于其良好的性能渐 渐取代了原来的排序合并连接。四、跟表连接有关的几