Oracle中表的连接及其调整

转载 2007年09月18日 20:29:00

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



一、嵌套循环连接(Nested Loop)

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

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

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

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

NESTED LOOP

<Outer Loop>

<Inner Loop>

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

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

SQL> select e.empno,e.ename,e.job,d.dname

2 from emp e,dept d

3 where e.deptno=d.deptno

4 and e.empno=7900;

EMPNO ENAME JOB DNAME

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

7900 JAMES CLERK SALES

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 NESTED LOOPS

2 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.dname

2  from emp,dept

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

ENAME      DNAME

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

SMITH

ALLEN

WARD       SALES

JONES      RESEARCH

MARTIN     SALES

BLAKE      SALES

CLARK      ACCOUNTING

SCOTT      RESEARCH

KING       ACCOUNTING

TURNER     SALES

ADAMS      RESEARCH

JAMES      SALES

FORD       RESEARCH

MILLER     ACCOUNTING

14 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.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)ordered:要求优化器按from列出的表顺序进行连接。

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

 

相关文章推荐

Oracle中表的连接及其调整.

  • 2010年03月02日 12:20
  • 49KB
  • 下载

Oracle中表的连接及其调整

http://www.51cto.com/art/200702/41041.htm 摘要:在日常基于数据库应用的开发过程中,我们经常需要对多个表或者数据源进行关联查询而得出我们需要的...

Oracle中表的四种连接方式讲解

  • 2013年11月14日 09:30
  • 36KB
  • 下载

Oracle中表的四种连接方式

表的连接是指在一个SQL语句中通过表与表之间的关连,从一个或多个表中检索相关的数据,大体上表与表之间的连接主要可分四种,分别为相等连接,外连接,不等连接和自连接,本文将主要从以下几个典型的例子来分析O...

Oracle中表连接的运行原理

Oracle优化器会自动选择以下三种方式的一种运行表连接,但在数据环境上配合强化选择合适的方式或强制使用某种方式是SQL优化的需要:    NESTED LOOP对于被连接的数据子集较小的情况,nes...
  • njyky
  • njyky
  • 2011年02月19日 13:21
  • 656

oracle中表连接查询 和 分页查询

多表查询的条件是至少不能少于 表的个数 - 1  1 . 自连接  解释 : 自连接是指在同一张表的连接查询 。  例子 : select worker.ename,boss.e...

Oracle中表之间的连接方式及Hint清单

实验环境: ·OS Microsoft Winsows XP Professional 版本 2002 Service Pack 3 ·DB Oracle Database 10g Ente...

Oracle中表的连接方式查询讲解

  表的连接是指在一个SQL语句中通过表与表之间的关连,从一个或多个表中检索相关的数据,大体上表与表之间的连接主要可分四种,分别为相等连接,外连接,不等连接和自连接,本文将主要从以下几个典型的例子来分...

oracle中表的(+)左外连接,右外连接,全外连接

oracle中表的左外连接,右外连接,全外连接   左外连接的driver table是from后面的第一个表(外连接的表)  右外连接的driver table是from后面的第二个表(外连接...
  • aicon
  • aicon
  • 2012年02月23日 09:29
  • 553

查看Oracle中表的索引是否存在

用user_indexes和user_ind_columns系统表查看已经存在的索引 对于系统中已经存在的索引我们可以通过以下的两个系统视图(user_indexes和user_ind_co...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Oracle中表的连接及其调整
举报原因:
原因补充:

(最多只允许输入30个字)