oracle 表连接方式

那天面试，面试官问我表的连接方式，我给他说join,left join ,right join,当时还以为挺对。

结果，what fuck!!!,今天看到这个，才知道自己错的离谱。

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

嵌套循环连接(NESTEDLOOPJOIN)

群集连接(CLUSTERJOIN)

排序合并连接(SORTMERGEJOIN)

笛卡尔连接(CARTESIANJOIN)

ORACLE7.3中，新增加了哈希连接(HASHJOIN)

在ORACLE8中，新增加了索引连接(INDEXJOIN)

这是什么鬼东西？不知道啊？没听说过啊。笛卡尔连接倒是听说过，可这玩意一般情况下可不兴用啊。

嵌套循环连接

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

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

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;

上面的表是外部表，即驱动表。下面的表是内部表的执行计划:

SELECTSTATEMENTOptimizer=CHOOSENESTEDLOOPSTABLEACCESS(FULL)OF'USER_INFO'TABLEACCESS(FULL)OF'DEV_INFO'（原作者就这么写的，我看不懂）

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

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

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

select /*+use_nl(ab)*/ a.user_name,b.dev_no from user_info a,dev_info b where a.user_id = b.user_id;

群集连接(CLUSTERJOIN)

群集连接实际上是嵌套循环连接的一种特例。如果所连接的两张源表是群集中的表，即两张表属于同一个段(SEGMENT)，那么ORACLE能够使用群集连接。处理的过程是：ORACLE从第一张行源表中读取第一行，然后在第二张行源表中使用CLUSTER索引查找能够匹配到的纪录；继续上面的步骤处理行源表中的第二行，直到所有的记录全部处理完。

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

排序合并连接(SORTMERGEJOIN)

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

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--------------------------------------------------SELECTSTATEMENTOptimizer=CHOOSE(Cost=7Card=336Bytes=16128)MERGEJOIN(Cost=7Card=336Bytes=16128)SORT(JOIN)(Cost=4Card=82Bytes=1968)TABLEACCESS(FULL)OF'USER_INFO'(Cost=2Card=82Bytes=1968)SORT(JOIN)(Cost=4Card=82Bytes=1968)TABLEACCESS(FULL)OF'DEV_INFO'(Cost=2Card=82Bytes=1968)

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

select /*+use_merge(ab)*/ a.user_name,b.dev_no from user_info a,dev_info b where a.user_id > b.user_id;

笛卡尔连接(CARTESIANJOIN)

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

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

select a.user_name,b.dev_no from user_info a,dev_info b;

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

哈希连接

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

当表连接中的小表能够完全cache到可用内存的时候，哈希连接的效果最佳。哈希连接的成本只是两个表从硬盘读入到内存的成本。

但是，如果哈希表过大而不能全部cache到可用内存时，优化器将会把哈希表分成多个分区，再将分区逐一cache到内存中。当表的分区超过了可用内存时，分区的部分数据就会临时地写到磁盘上的临时表空间上。因此，分区的数据写磁盘时，比较大的区间(EXTENT)会提高I/O性能。ORACLE推荐的临时表空间的区间是1MB。临时表空间的区间大小由UNIFORMSIZE指定。

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

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子句中的第一张表将用于建立哈希表。

selecta.user_name,b.dev_nofromuser_infoa,dev_infobwherea.user_id=b.user_id;Plan----------------------------------------------------------0SELECTSTATEMENTOptimizer=CHOOSE(Cost=5Card=82Bytes=3936)10HASHJOIN(Cost=5Card=82Bytes=3936)21TABLEACCESS(FULL)OF'USER_INFO'(Cost=2Card=82Bytes=1968)31TABLEACCESS(FULL)OF'DEV_INFO'(Cost=2Card=82Bytes=1968)

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

select /*+use_hash(ab)*/ a.user_name,b.dev_no from user_info a, dev_info b where a.user_id = b.user_id;

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

索引连接

如果一组已存在的索引包含了查询所需要的所有信息，那么优化器将在索引中有选择地生成一组哈希表。可通过范围或者快速全局扫描访问到每一个索引，而选择何种扫描方式取决于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_NLUSE_MERGEUSE_HASH

使用的条件任何连接主要用于不等价连接，如、>=;

但是不包括<>仅用于等价连接

相关资源CPU、磁盘I/O内存、临时空间内存、临时空间

特点当有高选择性索引或进行限制性搜索时效率比较高，能够快速返回第一次的搜索结果。当缺乏索引或者索引条件模糊时，排序合并连接比嵌套循环有效。当缺乏索引或者索引条件模糊时，哈希连接连接比嵌套循环有效。通常比排序合并连接快。

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

缺点当索引丢失或者查询条件限制不够时，效率很低；当表的纪录数多时，效率低。所有的表都需要排序。它为最优化的吞吐量而设计，并且在结果没有全部找到前不返回数据。为建立哈希表，需要大量内存。第一次的结果返回较慢。