`
czxiyj
  • 浏览: 10223 次
  • 来自: ...
最近访客 更多访客>>
社区版块
存档分类
最新评论

Oracle中表的连接及其调整

阅读更多
只有对这些问题有了清晰的理解后,我们才能针对特定的查询需求选择合适的连接方式,开发出健壮的数据库应用程序。选择合适的表连接方法对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.dname2 from emp e,dept d3 where e.deptno=d.deptno4 and e.empno=7900;EMPNO ENAME JOB DNAME---------- ---------- --------- -------------- 7900 JAMES CLERK SALESExecution 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 SALES12 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     ACCOUNTING12 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将不会生效。


本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/liuya1985liuya/archive/2008/03/08/2158415.aspx
分享到:
评论

相关推荐

    Oracle中表的连接及其调整.

    Oracle 数据库中的表连接是构建复杂 SQL 查询的基础,它允许从多个表中组合数据。连接方法的选择直接影响到查询性能,因此了解各种连接类型及其适用场景至关重要。本文将深入探讨 Oracle 中的三种主要连接方式:嵌套...

    Oracle中导出表结构

    本文详细介绍了如何在Oracle数据库中导出表结构及其注释信息。通过合理的SQL查询设计,可以高效地获取所需的表结构详情。这对于数据库管理员来说是一项非常实用的技能。希望本文能为读者提供有价值的信息和指导。

    oracle数据库表中修改字段的顺序

    在Oracle数据库中,我们常常会遇到需要调整表中字段顺序的情况。例如,为了优化查询效率或是更好地符合业务逻辑,我们可能希望更改某些字段的位置。在过去,如果想要改变一个表中列的顺序,唯一的方法就是创建一个新...

    SQL-SERVER-64位配置ORACLE连接-中文乱码问题

    ### SQL-SERVER-64位配置ORACLE连接-中文乱码问题 在IT行业中,不同数据库之间的连接配置是一项常见的任务,特别是在需要实现跨平台数据交换的场景下。本文将详细介绍如何解决64位系统下的SQL Server连接Oracle...

    oracle共享连接和专用连接模式

    本文将详细探讨Oracle数据库中的两种主要连接模式:共享连接(Shared Server)与专用连接(Dedicated Server)。这两种模式各有优势和适用场景,了解它们的工作原理及差异有助于优化Oracle数据库的应用。 #### 二、...

    odbc连接oracle11g数据库

    在Windows 7 64位操作系统中,配置ODBC连接Oracle 11g数据库涉及到以下几个关键步骤和知识点: 1. **安装Oracle客户端**:压缩包中可能包含Oracle的客户端软件,这是连接Oracle数据库的基础。Oracle客户端通常包括...

    ASP.NET Oracle连接字符串

    ### ASP.NET Oracle连接字符串详解 在开发基于ASP.NET的应用程序时,经常需要与Oracle数据库进行交互。为了确保数据能够正确地被读取、写入,一个正确的连接字符串至关重要。本文将详细探讨如何设置一个适用于ASP...

    Oracle RAC数据库连接负载均衡配置研究.pdf

    本文主要研究 Oracle RAC 数据库连接负载均衡配置,探讨了连接负载均衡的重要性及其实现方式。连接负载均衡是 Oracle RAC 数据库中的一种重要机制,旨在提高数据库的可用性和性能。文章首先介绍了连接负载均衡的概念...

    Plsqldeveloper连接oracle11g

    本文将基于标题“Plsqldeveloper连接oracle11g”及其相关内容,深入解析如何通过Plsqldeveloper成功连接至Oracle 11g数据库,包括必要的软件准备、配置步骤及环境变量设置。 ### 一、软件准备 #### 1. 下载Oracle ...

    OCP Oracle9i 性能调整学习指南.pdf

    - **性能调整的重要性**: 在 Oracle9i 中进行性能调整是确保系统高效运行的关键。通过优化数据库配置、SQL 查询、索引设计等方面,可以显著提高系统的响应速度和整体性能。 #### 二、性能调整基础 - **理解 Oracle...

    kettle连接oracle12C--报错ORA-28040 没有匹配的验证协议.rar

    总之,"ORA-28040:没有匹配的验证协议"是一个常见的Oracle连接问题,需要结合Kettle的配置和Oracle数据库的设置来解决。通过以上分析和解决方案,你应该能够找到解决问题的方法,顺利连接到Oracle 12c数据库。

    django连接oracle的方法 Windows环境

    本文将详细介绍在 Windows 环境下使用 Django 连接 Oracle 数据库时可能遇到的问题及其解决方案。 #### 二、问题 1:oci.dllisnotthecorrectarchitecture 错误 在尝试连接 Oracle 数据库时,可能会遇到“oci....

    Oracle EBS 表结构

    在OracleERP表结构.xls文件中,很可能是列出了Oracle EBS中的关键表及其描述,这对于学习和理解EBS的数据架构非常有帮助。 首先,表结构中的"表"是Oracle数据库中的基本数据容器,每个表都包含一系列列,列定义了...

    Oracle查询死锁表

    本文将围绕“Oracle查询死锁表”的主题展开详细讨论,旨在帮助数据库管理员更好地理解和掌握如何检测及解除Oracle中的死锁现象。 ### 一、理解Oracle死锁 #### 1.1 定义 死锁通常发生在两个或更多的事务试图同时...

    Oracle 常用系统表

    ### Oracle常用系统表与数据字典视图详解 #### 一、系统视图概述 Oracle 数据库中的系统视图提供了一种方式来访问数据库内部结构和管理信息。这些视图通常以 `v$` 开头,并且存储在 `$ORACLE_HOME/RDBMS/ADMIN/...

    kettler连接oracle 12c数据库(目前只能用JNDI)

    然而,随着 Oracle 12c 的发布及其对安全性和性能的改进,原有的连接方式可能不再适用或遇到兼容性问题。在这种情况下,使用 JNDI 连接方式成为了一种可行且推荐的选择。 #### 二、准备工作 1. **安装 Kettler ...

    Oracle中hint的理解篇

    - **调整表的连接顺序**:改变表的连接顺序可能会对执行计划产生重大影响。 - **设置语句的并行度**:可以控制SQL语句执行时的并行程度。 #### 三、Hint的使用方法 1. **语法格式**:`{DELETE|INSERT|SELECT|...

    Oracle sql 性能优化调整

    以下是对标题“Oracle SQL性能优化调整”及其描述中提到的知识点进行的深入解析。 #### 1. 优化器模式选择 Oracle数据库提供了三种主要的优化器模式:`RULE`、`COST`和`CHOOSE`,以及`ALL_ROWS`和`FIRST_ROWS`。...

    oracle 043中文

    4. 表空间与数据文件:在Oracle中,数据存储在表空间中,而表空间由一个或多个数据文件组成。了解如何创建、管理表空间及数据文件对于数据库的维护是必要的。 5. 表与索引:表是数据的主要存储结构,索引则能提高...

Global site tag (gtag.js) - Google Analytics