`
Wen_JN_86
  • 浏览: 33736 次
  • 性别: Icon_minigender_1
社区版块
存档分类
最新评论

Oracle-表连接简介

阅读更多

 

Oracle-表连接简介

表的连接是指在一个SQL语句中通过表与表之间的关联,从一个或多个表检索出相关的数据。连接是通过SQL语句中FROM从句的多个表名及WHERE从句里定义的表之间的连接条件来实现的。如果一个SQL语句的关联表超过两个,那么连接的顺序如何呢?ORACLE首先连接其中的两个表,产生一个结果集;然后将产生的结果集与下一个表进行关联....继续这个过程,直到所有的表都连接完成;最后产生所需的数据。

嵌套循环连接(NESTED LOOP JOIN)

排序合并连接(SORT MERGE JOIN)

哈希连接(HASH JOIN)

1. 嵌套循环连接(NESTED LOOP JOIN)

嵌套循环连接的内部处理流程:

1) Oracle 优化器根据基于规则RBO或基于成本CBO的原则,选择两个表中的一个作为驱动表,并指定其为外部表。

2) Oracle 优化器再将另外一个表指定为内部表。

3) 从外部表中读第一行,然后和内部表中的数据逐一进行对比,所有匹配的记录放在结果集中。

4) 读取外部表中的第二行,再和内部表中的数据逐一进行对比,所有匹配的记录添加到结果集中。

5) 重复上述步骤,直到外部表中的所有纪录全部处理完。

6) 最后产生满足要求的结果集。

在驱动表(外部表)较小、或者内部表已连接的列有惟一的索引或高度可选的非惟一索引时, 嵌套循环连接效果是比较理想的。嵌套循环连接比其他连接方法有优势,它可以快速地从结果集中提取第一批记录,而不用等待整个结果集完全确定下来。这样,在理想情况下,终端用户就可以通过查询屏幕查看第一批记录,而在同时读取其他记录。不管如何定义连接的条件或者模式,都可以使用嵌套循环连接,所以嵌套循环连接是非常灵活的。

然而,如果内部行源表(读取的第二张表)已连接的列上不包含索引或者索引不是高度可选时, 嵌套循环连接效率是很低的。如果驱动表的记录非常庞大时,其他的连接方法可能更加有效。

可以在SQL语句中添加HINTS(/*+ use_nl(a b) */),强制ORACLE优化器产生嵌套循环连接的执行计划。通过查询SQL语句的执行计划可以看出哪个表是外部表,哪个为内部表。

2. 哈希连接(HASH JOIN)

当内存能够提供足够的空间时,哈希连接是Oracle优化器通常的选择。哈希连接中,优化器根据统计信息,首先选择两个表中的小表,在内存中建立这张表的基于连接键的哈希表;优化器再扫描表连接中的大表,将大表中的数据与哈希表进行比较,如果有相关联的数据,则将数据添加到结果集中。

当哈希表构建完成后,进行下面的处理:

1) 第二个大表进行扫描

2) 如果大表不能完全cache到可用内存的时候,大表会分成很多分区

3) 大表的第一个分区cache到内存

4) 对大表第一个分区的数据进行扫描,并与哈希表进行比较,如果有匹配的纪录,添加到结果集里

5) 与第一个分区一样,其它的分区也类似处理。

6) 所有的分区处理完后,ORACLE对产生的结果集进行归并,汇总,产生最终的结果。

当连接的两个表是用等值连接并且表的数据量比较大时,优化器才可能采用哈希连接。只有在数据库初始化参数HASH_JOIN_ENABLED设为True,并且为参数PGA_AGGREGATE_TARGET设置了一个足够大的值的时候,Oracle才会使用哈希连接。HASH_AREA_SIZE是向下兼容的参数,但在Oracle9i之前的版本中应当使用HASH_AREA_SIZE。当表连接中的小表能够完全cache到可用内存的时候,哈希连接的效果最佳。哈希连接的成本只是两个表从硬盘读入到内存的成本。

但是,当哈希表过大或可用内存有限,哈希表不能完全CACHE到内存。随着满足连接条件的结果集的增加,可用内存会随之下降,这时已经CACHE到内存的数据可能会重新写回到硬盘去。如果出现这种情况,系统的性能就会下降。

可以在SQL语句中添加HINTS(/*+ use_hash(a b)*/),强制ORACLE优化器产生哈希连接的执行计划。

3. 排序合并连接(SORT MERGE JOIN)

排序合并连接内部处理的流程:

1) 优化器判断第一个源表是否已经排序,如果已经排序,则到第3步,否则到第2步。

2) 第一个源表排序

3) 优化器判断第二个源表是否已经排序,如果已经排序,则到第5步,否则到第4步。

4) 第二个源表排序

5) 已经排过序的两个源表进行合并操作,并生成最终的结果集。

在缺乏数据的选择性或者可用的索引时,或者两个源表都过于庞大(所选的数据超过表记录数的5%)时,排序合并连接将比嵌套循环连更加高效。

排列合并连接需要比较大的临时内存块用于排序,这将导致在临时表空间占用更多的内存和磁盘I/O。可以在SQL语句中添加HINTS(/*+use_merge(a b) */),强制ORACLE优化器产生排序合并连接。

4. 比较

表连接的比较:

 

Oracle学习-表连接

 

 

结束语

深入地理解和掌握oracle的表连接对于优化数据库的性能至关重要。由于优化器选择方式的不同,以及统计信息的缺失或统计信息的不准确,ORACLE自动选择的表连接方式不一定是最优的。当出现多表连接时,需要仔细分析是否有更佳的连接条件。根据系统的特点,必要时可以在SQL中添加HINTS,从而改变SQL的执行计划,从而达到性能优化的目的

Oracle的优化器的RBO和CBO方式:

1、基于规则的优化方式(Rule-Based Optimization,简称为RBO)

优化器在分析SQL语句时,所遵循的是Oracle内部预定的一些规则,对数据是不敏感的。它只借助少量的信息来决定一个sql语句的执行计划,包括:

1)sql语句本身

2)sql中涉及到的table、view、index等的基本信息

3)本地数据库中数据字典中的信息(远程数据库数据字典信息对RBO是无效的)

例如:我们常见的,当一个where子句中的一列有索引时去走索引。但是需要注意,走索引不一定就是优的,比如一个表只有两行数据,一次IO就可以完成全表的检索,而此时走索引时则需要两次IO,这时全表扫描(full table scan)的效率更优。

2、基于代价的优化方式(Cost-Based Optimization,简称为CBO)

它是看语句的代价(Cost),通过代价引擎来估计每个执行计划所需的代价,该代价将每个执行计划所耗费的资源进行量化,CBO根据这个代价选择出最优的执行计划。一个查询所耗费的资源可分为三部分:I/O代价、CPU代价、NETWORK代价。I/O是指把数据从磁盘读入内存时所需代价(该代价是查询所需最主要的,所以在优化时一个基本原则就是降低I/O总次数);CPU代价是指处理内存中数据所需的代价,数据一旦读入内存,当我们识别出我们所要的数据后,会在这些数据上执行排序(sort)或连接(join)操作,这需要消耗CPU资源;对于访问远程节点来说,network代价的花费也是很大的。

优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息。统计信息给出表的大小、有多少行、每行的长度等信息。这些统计信息起初在库内是没有的,是做analyze后才出现的,很多的时侯过期统计信息会令优化器做出一个错误的执行计划,因些应及时更新这些信息(dbms_stat.analyze)。

如星型连接排列查询,哈希连接查询,函数索引,和并行查询等一些技术都是基于CBD的。

分享到:
评论

相关推荐

    oracle-instantclient19.10-sqlplus-base-devel(x86 arm架构)

    linux连接oracle工具,适用于x86及arm架构 oracle-instantclient-basic-21.6.0.0.0-1.x86_64.rpm oracle-instantclient-basic-21.6.0.0.0-1.x86_64.zip oracle-instantclient-devel-21.6.0.0.0-1.x86_64.rpm oracle-...

    cx_Oracle-5.1.3.tar.gz,oracle-instantclient11.2

    总的来说,cx_Oracle库是Python与Oracle数据库交互的关键工具,而oracle-instantclient11.2则为连接Oracle服务器提供了基础支持。在Python项目中,正确配置和使用这两个组件,可以实现高效的数据存取和管理。对于...

    oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm

    oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm --基础包,为了运行OCI、OCCI、JDBC-OCI 这几个应用程序; oracle-instantclient11.2-sqlplus-11.2.0.4.0-1.x86_64.rpm --补充包/文件,是为了运行sql*plus...

    oracle-instantclient19.6-basic-19.6.0.0.0-1.x86_64 rpm包合集

    oracle-instantclient19.6-basic-19.6.0.0.0-1.x86_64.rpm oracle-instantclient19.6-devel-19.6.0.0.0-1.x86_64.rpm

    oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm

    Oracle Instant Client是Oracle公司提供的一款轻量级的数据库连接工具,用于在Linux系统上与Oracle数据库进行通信。这个特定的版本,“oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm”,是针对64位Linux...

    oracle-instantclient.11.2.zip

    Oracle Instant Client是Oracle公司提供的一款轻量级的数据库连接组件,用于在客户端计算机上实现对Oracle数据库的连接和数据操作。这个"oracle-instantclient.11.2.zip"压缩包包含了版本11.2的Instant Client,适用...

    oracle-instantclient11.2-devel-11.2.0.4.0-1.x86_64.rpm

    oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm --基础包,为了运行OCI、OCCI、JDBC-OCI 这几个应用程序; oracle-instantclient11.2-sqlplus-11.2.0.4.0-1.x86_64.rpm --补充包/文件,是为了运行sql*plus...

    oracle-instantclient-12.2.0.1.0-1.x86_64的rpm格式

    Oracle Instant Client是Oracle公司提供的一款轻量级的数据库连接工具,它允许应用程序无需完整安装Oracle Database Server即可与Oracle数据库进行通信。在这个特定的版本“oracle-instantclient-12.2.0.1.0-1.x86_...

    oracle-database-server-12cR2-preinstall

    Oracle Database Server 12cR2 Preinstall 是一个专为在x86_64架构的Linux系统上安装Oracle数据库12c Release 2 (12.2)而设计的预配置软件包。这个预安装包的主要目的是简化Oracle数据库服务器的安装流程,预先配置...

    DBD-Oracle-1.74.tar.gz

    5. **测试连接**:安装完成后,可以通过Perl脚本测试DBD::Oracle是否工作正常,例如创建一个简单的Perl脚本连接到Oracle数据库并执行SQL查询。 在使用DBD::Oracle时,开发者需要注意以下几点: 1. **连接参数**:...

    cx_Oracle-7.3.0.tar.gz

    `cx_Oracle` 是一个 Python 的数据库连接模块,它提供了与 Oracle 数据库交互的功能。`cx_Oracle-7.3.0.tar.gz` 是这个模块的最新版本,针对 Linux 操作系统。相比于常见的 `cx_Oracle-5.1.2` 版本,7.3.0 版本可能...

    cx_Oracle-5.1-11g.win32-py2.5.msi

    cx_Oracle-5.1-11g.win32-py2.5.msi,用于连接oracle数据库

    oracle-instantclient11.2-devel-11.2.0.3.0-1.x86_64.zip

    Oracle Instant Client是Oracle公司提供的一款轻量级的数据库连接工具,它允许开发者在不安装完整Oracle数据库的情况下,进行Oracle数据库的开发和连接。这个压缩包"oracle-instantclient11.2-devel-11.2.0.3.0-1....

    cx_Oracle-5.1.2-11g.win32-py2.7.msi

    cx_Oracle是Python连接Oracle数据库的官方驱动程序,它提供了全面的功能,包括数据查询、事务处理、游标操作等。本文将详细介绍cx_Oracle模块的使用方法以及如何安装"cx_Oracle-5.1.2-11g.win32-py2.7.msi"这个特定...

    oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64/sqlplus/devel

    Oracle Instant Client是Oracle公司提供的一款轻量级的数据库连接工具,它允许用户在不安装完整Oracle数据库的情况下,进行数据查询、开发和管理。这个特定的版本,“oracle-instantclient11.2-basic-11.2.0.4.0-1....

    ORACLE数据库SQL优化---表连接类型.docx

    Oracle数据库的SQL优化是数据库管理员和开发人员关注的重要领域,特别是在处理复杂的表连接时。本文主要探讨了Oracle数据库中表连接类型的优化,包括连接顺序、连接方法和访问单表的方法。 1. 表连接顺序: Oracle...

Global site tag (gtag.js) - Google Analytics