ORACLE表连接方式分析及常见用法

摘要 : 针对在数据仓库环境下 , 由于超大数据量的处理而产生的效率问题，本文深入分析了 ORACLE 表的几种连接方式、特点、适用范围 , 以及对于如何使用和优化做了详细的探讨。

关键字： 数据仓库 ORACLE 表连接

 

一 引言

数 据仓库技术是目前已知的比较成熟和被广泛采用的解决方案，用于整和电信运营企业内部所有分散的原始业务数据，并通过便捷有效的数据访问手段，可以支持企业 内部不同部门，不同需求，不同层次的用户随时获得自己所需的信息。数据仓库系统需要能够及时地追踪和分析大量的历史数据，并能够及时做出分析和预测，因此 实时性是一个非常重要的指标。 ORACLE 由于可靠性、高性能等方面的特点，在电信行业大部分的数据仓库系统中担当了后台数据库的角色。由于电信行业的特点，处理的数据量十分庞大，处理的时间长。 尤其是对于大表之间的关联操作， 有的大表的记录数达到数亿条， 处理时间更是漫长，这成为影响数据库运行效率的主要因素。因此，对于数据库的性能优化相当重要。性能优化是个很大的课题，需要综合考虑，从服务器、磁盘、网络、 ORACLE 实例、 ORACLE SQL 等多方面着手。本文着重分析 ORACLE SQL 优化中对于系统性能影响极大的表连接方式、特点、适用范围，并对如何使用和优化做了详细的探讨。

二 表的连接

表的连接是 指在一个 SQL 语句中通过表与表之间的关联，从一个或多个表检索出相关的数据。连接是通过 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;

上面的表是外部表，即驱动表 下面的表是内部表

的执行计划 :

SELECT STATEMENT Optimizer=CHOOSE

NESTED LOOPS

TABLE ACCESS (FULL) OF 'USER_INFO'

TABLE ACCESS (FULL) OF 'DEV_INFO'

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

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

可以通过在 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 能够使用群集连接。处理的过程是： ORACLE 从第一张行源表中读取第一行，然后在第二张行源表中使用 CLUSTER 索引查找能够匹配到的纪录；继续上面的步骤处理行源表中的第二行， 直到 所有的记录全部处理完。

群集连接的效率极高，因为两个参加连接的行源表实际上处于同一个物理块上。但是，群集连接也有其限制，没有群集的两个表不可能用群集连接。所以，群集连接实际上很少使用 。

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;

Plan

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

SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=336 Bytes=16128)

MERGE JOIN (Cost=7 Card=336 Bytes=16128)

SORT (JOIN) (Cost=4 Card=82 Bytes=1968)

TABLE ACCESS (FULL) OF 'USER_INFO' (Cost=2 Card=82 Bytes=1968)

SORT (JOIN) (Cost=4 Card=82 Bytes=1968)

TABLE ACCESS (FULL) OF 'DEV_INFO' (Cost=2 Card=82 Bytes=1968)

可以通过在 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;

Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=82 Bytes=3936

)

1 0 HASH JOIN (Cost=5 Card=82 Bytes=3936)

2 1 TABLE ACCESS (FULL) OF 'USER_INFO' (Cost=2 Card=82 Bytes

=1968)

3 1 TABLE ACCESS (FULL) OF 'DEV_INFO' (Cost=2 Card=82 Bytes=

1968)

可以通过在 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 user_info

where user_id = ‘U101010’

and dev_type = ‘1010’;

三 几种主要表连接的比较

类别	嵌套循环连接	排序合并连接	哈希连接
优化器提示	USE_NL	USE_MERGE	USE_HASH
使用的条件	任何连接	主要用于不等价连接，如 < 、 <= 、 > 、 >=; 但是不包括 <>	仅用于等价连接
相关资源	CPU 、磁盘 I/O	内存、临时空间	内存、临时空间
特点	当有高选择性索引或进行限制性搜索时效率比较高，能够快速返回第一次的搜索结果。	当缺乏索引或者索引条件模糊时，排序合并连接比嵌套循环有效。	当缺乏索引或者索引条件模糊时，哈希连接连接比嵌套循环有效。通常比排序合并连接快。 在数据仓库环境下，如果表的纪录数多，效率高。
缺点	当索引丢失或者查询条件限制不够时，效率很低； 当表的纪录数多时，效率低。	所有的表都需要排序。它为最优化的吞吐量而设计，并且在结果没有全部找到前不返回数据。	为建立哈希表，需要大量内存。第一次的结果返回较慢。

四 结束语

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