当前位置: 首页 > news >正文

表之间的连接

   表之间的连接

表的连接是指在一个SQL语句中通过表与表之间的关联,从一个或多个表检索出相关的数据。连接是通过SQL语句中FROM从句的多个表名,以及WHERE从句里定义的表之间的连接条件来实现的。如果一个SQL语句的关联表超过两个,那么连接的顺序如何呢?ORACLE首先连接其中的两个表,产生一个结果集;然后将产生的结果集与下一个表再进行关联;继续这个过程,直到所有的表都连接完成;最后产生所需的数据。主要包括:a)排序合并连接;b)嵌套循环连接;c)哈希连接;d)笛卡尔积连接。

a) 排序合并连接

1) 判断第一个源表是否已经排序,如果已经排序,则转3),否则转2);

2) 对第一个源表按照连接关联列进行排序;

3) 判断第二个源表是否已经排序,如果已经排序,则转5),否则转4);

4) 对第二个源表按照连接关联列进行排序;

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

        在缺乏数据的选择性或者可用的索引时,或者两个源表都过于庞大(所选的数据超过表记录数的5%)时,排序合并连接将比嵌套循环连接更加高效。但是排序合并连接需要较大的临时内存块,以用于排序,这将导致临时表空间占用更多的内存和磁盘I/O。    

  

b) 嵌套循环连接

1) 首先根据基于规则RBO或者基于代价CBO的原则,选择两个表中的一个作为驱动表(外部表)。

2) 指定另外一个表为内部表。

3) 从外部表中读取第一行,然后与内部表的数据逐一进行对比,将匹配的记录放到结果集中。

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

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

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

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

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

c) 哈希连接

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

       当表连接中的小表能够完全cache到可用内存的时候,哈希连接的效果最佳。哈希连接的成本只是两个表从硬盘读入到内存的成本。但是,如果哈希表过大而不能全部cache到可用内存时,优化器将会把哈希表分成多个分区,再将分区逐一cache到内存中。当表的分区超过了可用内存时,分区的部分数据就会临时地写到磁盘上的临时表空间上。因此,分区的数据写磁盘时,比较大的区间(EXTENT)会提高I/O性能。ORACLE推荐的临时表空间的区间是1MB。临时表空间的区间大小由UNIFORM. SIZE指定。

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

1) 第二个大表进行扫描

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

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

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

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

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

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

当连接的两个表是用等值连接并且表的数据量比较大时,优化器才可能采用哈希连接。哈希连接是基于CBO的。只有在数据库初始化参数HASH_JOIN_ENABLED设为True,并且为参数PGA_AGGREGATE_TARGET设置了一个足够大的值的时候,Oracle才会使用哈希边连接。HASH_AREA_SIZE是向下兼容的参数,但在Oracle9i之前的版本中应当使用HASH_AREA_SIZE。当使用ORDERED提示时,FROM子句中的第一张表将用于建立哈希表。

当缺少有用的索引时,哈希连接比嵌套循环连接更加有效。哈希连接也可能比嵌套循环连接更快,因为处理内存中的哈希表比检索B_树索引更加迅速。

d) 笛卡尔积连接

       当两张表进行连接,它们之间没有任何关联条件时,这两张表就会进行笛卡尔乘积连接。笛卡尔乘积连接是使用一个表的每一行依次与另一个表中的所有行匹配。例如:表A和表B做笛卡尔乘积连接,表Am行记录,表Bn 行记录,则笛卡尔乘积的结果为 m*n条记录。

三种连接方式的比较:

 

 

 

 

 

类别

嵌套循环连接

排序合并连接

哈希连接

优化器提示

USE_NL

USE_MERGE

USE_HASH

使用的条件

任何连接

主要用于不等价连接,如<<=>>=;

但是不包括 <>

仅用于等价连接

相关资源

CPU、磁盘I/O

内存、临时空间

内存、临时空间

特点

当有高选择性索引或进行限制性搜索时效率比较高,能够快速返回第一次的搜索结果。

当缺乏索引或者索引条件模糊时,排序合并连接比嵌套循环有效。

当缺乏索引或者索引条件模糊时,哈希连接连接比嵌套循环有效。通常比排序合并连接快。

在数据仓库环境下,如果表的纪录数多,效率高。

缺点

当索引丢失或者查询条件限制不够时,效率很低;

当表的纪录数多时,效率低。

所有的表都需要排序。它为最优化的吞吐量而设计,并且在结果没有全部找到前不返回数据。

为建立哈希表,需要大量内存。第一次的结果返回较慢。

相关文章:

  • 【剑指Offer面试题】九度OJ1384:二维数组中的查找
  • 查看执行计划
  • oracle11g的内存分配不当,导致的错误ORA-01034,ORA-00838,ORA-27101
  • 如何改变oracle的执行计划(HINT)
  • 【Java线程】SwingWorker的用法
  • 如何分析执行计划
  • ipconfig提示不是内部或外部命令
  • ETL模型设计
  • python-函数用法
  • 数据集市
  • 纳税服务系统【自动受理,Quartz任务调度】
  • 小笑话集锦
  • mac下git配置和jenkins打包
  • 三国中最精辟的十句话
  • BFS模版程序
  • python3.6+scrapy+mysql 爬虫实战
  • Java新版本的开发已正式进入轨道,版本号18.3
  • react-native 安卓真机环境搭建
  • redis学习笔记(三):列表、集合、有序集合
  • Redis字符串类型内部编码剖析
  • 飞驰在Mesos的涡轮引擎上
  • 基于Android乐音识别(2)
  • 如何使用Mybatis第三方插件--PageHelper实现分页操作
  • 深度学习在携程攻略社区的应用
  • 深入浅出webpack学习(1)--核心概念
  • 用element的upload组件实现多图片上传和压缩
  • 京东物流联手山西图灵打造智能供应链,让阅读更有趣 ...
  • ‌U盘闪一下就没了?‌如何有效恢复数据
  • ### Error querying database. Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException
  • $.ajax()方法详解
  • (31)对象的克隆
  • (70min)字节暑假实习二面(已挂)
  • (Java企业 / 公司项目)点赞业务系统设计-批量查询点赞状态(二)
  • (八)光盘的挂载与解挂、挂载CentOS镜像、rpm安装软件详细学习笔记
  • (超简单)构建高可用网络应用:使用Nginx进行负载均衡与健康检查
  • (超详细)语音信号处理之特征提取
  • (二)十分简易快速 自己训练样本 opencv级联lbp分类器 车牌识别
  • (限时免费)震惊!流落人间的haproxy宝典被找到了!一切玄妙尽在此处!
  • (心得)获取一个数二进制序列中所有的偶数位和奇数位, 分别输出二进制序列。
  • (一)项目实践-利用Appdesigner制作目标跟踪仿真软件
  • (转)从零实现3D图像引擎:(8)参数化直线与3D平面函数库
  • (转)自己动手搭建Nginx+memcache+xdebug+php运行环境绿色版 For windows版
  • .NET core 自定义过滤器 Filter 实现webapi RestFul 统一接口数据返回格式
  • .NET应用UI框架DevExpress XAF v24.1 - 可用性进一步增强
  • .NET中 MVC 工厂模式浅析
  • @RequestMapping 和 @GetMapping等子注解的区别及其用法
  • []C/C++读取串口接收到的数据程序
  • [AI]ChatGPT4 与 ChatGPT3.5 区别有多大
  • [AIR] NativeExtension在IOS下的开发实例 --- IOS项目的创建 (一)
  • [AutoSar NVM] 存储架构
  • [C#]科学计数法(scientific notation)显示为正常数字
  • [C#]使用OpenCvSharp图像滤波中值滤波均值滤波高通滤波双边滤波锐化滤波自定义滤波
  • [C++]:for循环for(int num : nums)
  • [c++进阶(九)] STL之deque深度剖析
  • [FreeRTOS 基础知识] 保存现场与恢复现场