ORACLE多个表连接方式详解

排序合并连接内部处理的流程：
1) 优化器判断第一个源表是否已经排序，如果已经排序，则到第3步，否则
到第2步。
2) 第一个源表排序
3) 优化器判断第二个源表是否已经排序，如果已经排序，则到第5步，否则
到第4步。
4) 第二个源表排序
5) 已经排过序的两个源表进行合并操作，并生成最终的结果集。
1） 第二个大表进行扫描
2） 如果大表不能完全cache到可用内存的时候，大表同样会分成很多分区
3） 大表的第一个分区cache到内存
4） 对大表第一个分区的数据进行扫描，并与哈希表进行比较，如果有匹配的纪录，添加到结果集里面
5） 与第一个分区一样，其它的分区也类似处理。
6） 最后产生满足要求的结果集。
通过查询SQL语句的执行计划可以看出哪个表是外部表，哪个为内部表。
如 select er_name,b.dev_no
from user_info a, dev_info b
where er_id = er_id;
笛卡尔连接是指在sql语句中没有写出表连接的条件，优化器把第一个表的每一条记录和第二个表的所有纪录相连接。如果第
一个表的纪录数为m, 第二个表的纪录数为m,则会产生m*n条纪录数。
下面的查询，未指名连接条件，就会产生笛卡尔连接。
select er_name,b.dev_no
使用嵌套循环连接是一种从结果集中提取第一批记录最快速的方法。在驱动行源表(就是正在查找的记录)较小、或者内部行
源表已连接的列有惟一的索引或高度可选的非惟一索引时, 嵌套循环连接效果是比较理想的。嵌套循环连接比其他连接方法
有优势,它可以快速地从结果集中提取第一批记录,而不用等待整个结果集完全确定下来。这样,在理想情况下,终端用户就可
2 群集连接（CLUSTER JOIN）
群集连接实际上是嵌套循环连接的一种特例。如果所连接的两张源表是群集中的表，即两张表属于同一个段（SEGMENT）,，
那么ORACLE能够使用群集连接。处理的过程是：ORACLE从第一张行源表中读取第一行，然后在第二张行源表中使用CLUSTER索
引查找能够匹配到的纪录；继续上面的步骤处理行源表中的第二行，直到所有的记录全部处理完。
群集连接的效率极高，因为两个参加连接的行源表实际上处于同一个物理块上。但是，群集连接也有其限制，没有群集的两
个表不可能用群集连接。所以，群集连接实际上很少使用。
3 排序合并连接（SORT MERGE JOIN）
2） Oracle 优化器再将另外一个表指定为内部表。
3） Oracle从外部表中读取第一行,然后和内部表中的数据逐一进行对比，所有匹配的记录放在结果集中。
4） Oracle读取外部表中的第二行，再和内部表中的数据逐一进行对比，所有匹配的记录添加到结果集中。
5） 重复上述步骤，直到外部表中的所有纪录全部处理完。
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)
处理的数据量十分庞大，处理的时间长。尤其是对于大表之间的关联操作，有的大表的记录数达到数亿条，处理时间更是漫
长，这成为影响数据库运行效率的主要因素。因此，对于数据库的性能优化相当重要。性能优化是个很大的课题，需要综合
考虑，从服务器、磁盘、网络、ORACLE实例、ORACLE SQL等多方面着手。本文着重分析ORACLE SQL优化中对于系统性能影响
过便捷有效的数据访问手段，可以支持企业内部不同部门，不同需求，不同层次的用户随时获得自己所需的信息。数据仓库
系统需要能够及时地追踪和分析大量的历史数据，并能够及时做出分析和预测，因此实时性是一个非常重要的指标。ORACLE
由于可靠性、高性能等方面的特点，在电信行业大部分的数据仓库系统中担当了后台数据库的角色。由于电信行业的特点，
? 群集连接 (CLUSTER JOIN)
? 排序合并连接（SORT MERGE JOIN）
? 笛卡尔连接 (CARTESIAN JOIN)
ORACLE 7.3中，新增加了
? 哈希连接(HASH JOIN)。
在ORACLE 8中，新增加了
? 索引连接(INDEX JOIN)。
这六种连接方式都有其独特的技术特点，在一定的条件下，可以充分发挥高效的性能。
但是也都有其局限性，如果使用不当，不仅不能提高效率，反而会严重影响系统的性能。因此，深入地探讨连接方式的内部
运行机制对于性能优化是必要的。
1 嵌套循环连接
嵌套循环连接的内部处理的流程：
1） Oracle 优化器根据基于规则RBO或基于成本CBO的原则，选择两个表中的一个作为驱动表，并指定其为外部表。
时,FROM子句中的第一张表将用于建立哈希表。
select er_name,b.dev_no
from user_info a, dev_info b where a.ser_id = er_id;
Plan
----------------------------------------------------------
当表的分区超过了可用内存时，分区的部分数据就会临时地写到磁盘上的临时表空间上。因此，分区的数据写磁盘时，比较
大的区间（EXTENT）会提高I/O性能。ORACLE推荐的临时表空间的区间是1MB。临时表空间的区间大小由UNIFORM SIZE指定。
当哈希表构建完成后，进行下面的处理：
比较，如果有相关联的数据，则将数据添加到结果集中。
当表连接中的小表能够完全cache到可用内存的时候，哈希连接的效果最佳。哈希连接的成本只是两个表从硬盘读入到内存的
成本。
但是，如果哈希表过大而不能全部cache到可用内存时，优化器将会把哈希表分成多个分区，再将分区逐一cache到内存中。
以通过查询屏幕查看第一批记录,而在同时读取其他记录。不管如何定义连接的条件或者模式,任何两行记录源可以使用嵌套
循环连接,所以嵌套循环连接是非常灵活的。
然而,如果内部行源表(读取的第二张表)已连接的列上不包含索引,或者索引不是高度可选时, 嵌套循环连接效率是很低的。
如果驱动表的记录非常庞大时,其他的连接方法可能更加有效。
select /*+ use_merge(a b) */ er_name,b.dev_no
from user_info a, dev_info b
where er_id > er_id;
排序合并连接是基于RBO的。
4 笛卡尔连接 (CARTESIAN JOIN)
极大的表连接方式、特点、适用范围，并对如何使用和优化做了详细的探讨。
二 表的连接
表的连接是指在一个SQL语句中通过表与表之间的关联，从一个或多个表检索出相关的数据。连接是通过SQL语句中FROM从句
的多个表名，以及WHERE从句里定义的表之间的连接条件来实现的。如果一个SQL语句的关联表超过两个，那么连接的顺序如
create table dev_info(dev_no char(10),user_id char(10),dev_type char(10));
说明和分析表的各种连接方式。
ORACLE 从6的版本开始，优化器使用4种不同的表的连接方式：
? 嵌套循环连接（NESTED LOOP JOIN）
ORACLE表连接方式分析及常见用法
摘要: 针对在数据仓库环境下,由于超大数据量的处理而产生的效率问题，本文深入分析了ORACLE表的几种连接方式、特点、
适用范围,以及对于如何使用和优化做了详细的探讨。
关键字： 数据仓库 ORACLE 表连接
一 引言
数据仓库技术是目前已知的比较成熟和被广泛采用的解决方案，用于整和电信运营企业内部所有分散的原始业务数据，并通
何呢？ORACLE首先连接其中的两个表，产生一个结果集；然后将产生的结果集与下一个表再进行关联；继续这个过程，直到
所有的表都连接完成；最后产生所需的数据。下面都以两个表的连接为例
create table user_info(user_name char(10),user_id char(10));
from user_info a ,dev_info b;
由于笛卡尔连接会导致性能很差的SQL,因此一般也很少用到。
5哈希连接
当内存能够提供足够的空间时,哈希(HASH)连接是Oracle优化器通常的选择。哈希连接中，优化器根据统计信息，首先选择两
个表中的小表，在内存中建立这张表的基于连接键的哈希表；优化器再扫描表连接中的大表，将大表中的数据与哈希表进行
当连接的两个表是用等值连接并且表的数据量比较大时，优化器才可能采用哈希连接。哈希连接是基于CBO的。只有在数据库
初始化参数HASH_JOIN_ENABLED设为True,并且为参数PGA_AGGREGATE_TARGET设置了一个足够大的值的时候,Oracle才会使用哈
希边连接。HASH_AREA_SIZE是向下兼容的参数,但在Oracle9i之前的版本中应当使用HASH_AREA_SIZE。当使用ORDERED提示
上面的表是外部表，即驱动表
下面的表是内部表
的执行计划:
SELECT STATEMENT Optimizer=CHOOSE
NESTED LOOPS
TABLE ACCESS (FULL) OF 'USER_INFO'