表连接方式的分析与优化

本文详细介绍了Oracle数据库中六种不同的表连接方式,包括嵌套循环连接、群集连接、排序合并连接、笛卡尔连接、哈希连接和索引连接。每种连接方式的技术特点、适用场景及优缺点均有涉及。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

连接是通过SQL语句中FROM从句的多个表名,以及WHERE从句里定义的表之间的连接条件来实现的。如果一个SQL语句的关联表超过两个:ORACLE首先连接其中的两个表,产生一个结果集;然后将产生的结果集与下一个表再进行关联;继续这个过程,直到所有的表都连接完成;最后产生所需的数据。

下面都以两个表的连接为例

create table user_info(user_name char(10),user_id char(10));

create table dev_info(dev_no char(10),user_id char(10),dev_type char(10));

说明和分析表的各种连接方式。

ORACLE 6的版本开始,优化器使用4种不同的表的连接方式:

Ø  嵌套循环连接(NESTED LOOP JOIN

Ø  群集连接     (CLUSTER JOIN)

Ø  排序合并连接(SORT MERGE JOIN

Ø  笛卡尔连接   (CARTESIAN JOIN)

ORACLE 7.3中,新增加了

Ø  哈希连接(HASH JOIN)

ORACLE 8中,新增加了

Ø  索引连接(INDEX JOIN)

这六种连接方式都有其独特的技术特点,在一定的条件下,可以充分发挥高效的性能;但是也都有其局限性,如果使用不当,不仅不能提高效率,反而会严重影响系统的性能。

1 嵌套循环连接

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

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

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

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

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

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

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

通过查询SQL语句的执行计划可以看出哪个表是外部表,哪个为内部表。

:

SELECT A.USER_NAME, B.DEV_NO

  FROM USER_INFO A, DEV_INFO B

 WHERE A.USER_ID = B.USER_ID;

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

可以通过在SQL语句中添加HINTS,强制ORACLE优化器产生嵌套循环连接的执行计划。

SELECT /*+ use_nl(a b) */

 A.USER_NAME, B.DEV_NO

  FROM USER_INFO A, DEV_INFO B

 WHERE A.USER_ID = B.USER_ID;

2 群集连接(CLUSTER JOIN

  群集连接实际上是嵌套循环连接的一种特例。如果所连接的两张源表是群集中的表,即两张表属于同一个段(SEGMENT,,那么ORACLE能够使用群集连接。处理的过程是同嵌套循环连接群集连接的效率极高,因为两个参加连接的行源表实际上处于同一个物理块上。但是,群集连接也有其限制,没有群集的两个表不可能用群集连接。所以,群集连接实际上很少使用。

3 排序合并连接(SORT MERGE JOIN

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

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

到第2步。

2)  第一个源表排序

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

到第4步。

4)  第二个源表排序

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

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

排序合并连接需要比较大的临时内存块,以用于排序,这将导致在临时表空间占用更多的内存和磁盘I/O

SELECT A.USER_NAME, B.DEV_NO

  FROM USER_INFO A, DEV_INFO B

 WHERE A.USER_ID > B.USER_ID;

可以通过在SQL语句中添加HINTS,强制ORACLE优化器产生排序合并连接的执行计划。

SELECT /*+ use_merge(a b) */

 A.USER_NAME, B.DEV_NO

  FROM USER_INFO A, DEV_INFO B

 WHERE A.USER_ID > B.USER_ID;

   排序合并连接是基于RBO的。

4 笛卡尔连接   (CARTESIAN JOIN)

   笛卡尔连接是指在sql语句中没有写出表连接的条件,优化器把第一个表的每一条记录和第二个表的所有纪录相连接。如果第一个表的纪录数为m, 第二个表的纪录数为m,则会产生m*n条纪录数。

   下面的查询,未指名连接条件,就会产生笛卡尔连接。

   SELECT A.USER_NAME, B.DEV_NO FROM USER_INFO A, DEV_INFO B;

   由于笛卡尔连接会导致性能很差的SQL,因此一般也很少用到。

5 哈希连接

当内存能够提供足够的空间时,哈希(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子句中的第一张表将用于建立哈希表。

   SELECT A.USER_NAME, B.DEV_NO

  FROM USER_INFO A, DEV_INFO B

 WHERE A.USER_ID = B.USER_ID;

可以通过在SQL语句中添加HINTS,强制ORACLE优化器产生哈希连接的执行计划。

SELECT /*+ use_hash(a b)*/

 A.USER_NAME, B.DEV_NO

  FROM USER_INFO A, DEV_INFO B

 WHERE A.USER_ID = B.USER_ID;

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

6 索引连接

如果一组已存在的索引包含了查询所需要的所有信息,那么优化器将在索引中有选择地生成一组哈希表。可通过范围或者快速全局扫描访问到每一个索引,而选择何种扫描方式取决于WHERE子句中的可有条件。在一张表有大量的列,而您只想访问有限的列时,这种方法非常有效。WHERE子句约束条件越多,执行速度越快。因为优化器在评估执行查询的优化路径时,将把约束条件作为选项看待。您必须在合适的列(那些满足整个查询的列)上建立索引,这样可以确保优化器将索引连接作为可选项之一。这个任务通常牵涉到在没有索引,或者以前没有建立联合索引的列上增加索引。相对于快速全局扫描,连接索引的优势在于:快速全局扫描只有一个单一索引满足整个查询;索引连接可以有多个索引满足整个查询。

假设表dev_info上有两个索(一个在dev_no,一个在dev_type 上)。

作如下的查询

SELECT DEV_NO, DEV_TYPE

  FROM DEV_INFO

 WHERE USER_ID = 'U101010'

   AND DEV_TYPE = '1010';

几种主要表连接的比较

类别

嵌套循环连接

排序合并连接

哈希连接

提示

USE_NL

USE_MERGE

USE_HASH

使用的条件

任何连接

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

但是不包括 <>

仅用于等价连接

相关资源

CPU、磁盘I/O

内存、临时空间

内存、临时空间

特点

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

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

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

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

缺点

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

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

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

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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值