ORACLE3常用联接方式

1、嵌套循环联接嵌套循环联接也称为嵌套迭代，它将一个联接输入用作外部输入表（显示为图形执行计划中的顶 端输入），将另一个联接输入用作内部（底端）输入表。外部循环逐行消耗外部输入表。内部循 环为每个外部行执行，在内部输入表中搜索匹配行。最简单的情况是，搜索时扫描整个表或索引； 这称为单纯嵌套循环联接。如果搜索时使用索引，则称为索引嵌套循环联接。如果将索引生成为 查询计划的一部分（并在查询完成后立即将索引破坏），则称为临时索引嵌套循环联接。查询优 化器考虑所有这些不同形式。如果外部输入很小而内部输入很大且预先创建了索引，则嵌套循环 联接尤其有效。在许多小事务中（如那些只影响较小的一组行的事务），索引嵌

2、套循环联接远比 合并联接和哈希联接优越。但在大查询中，嵌套循环联接通常不是最佳选择。合并联接 sort merge join合并联接要求两个输入都在合并列上排序，而合并列由联接谓词的等效（WHERE）子句定义。 查询优化器一般扫描索引（如果在适当的一组列上存在一个索引），或在合并联接的下面放一个 排序运算符。在很少的情况下，虽然可能有多个等效子句，但只用其中一些可用的等效子句获得 合并列。由于每个输入都已排序，Merge Join运算符将获取每个输入中的行并将其进行比较。例如，对 于内联接操作，如果行相等则返回。如果行不相等，则废弃值较小的行并从该输入中获得另一行。 这一过程将重复进行，直到处

3、理完所有的行为止。合并联接操作可以是常规操作，也可以是多对多操作。多对多合并联接使用临时表存储行。如果 每个输入中有重复值，则在处理其中一个输入中的每个重复项时，另一个输入必须重绕到重复项 的开始位置。如果存在驻留谓词，所有满足合并谓词的行都将对该驻留谓词取值，然后只返回那些满足该驻留 谓词的行。合并联接本身的速度很快，但如果需要排序操作，选择合并联接就会非常费时。然而，如果数据 量很大且能够从现有B树索引中获得预排序的所需数据，则合并联接通常是最快的可用联接算 法。哈希联接哈希联接有两种输入：生成输入和探测输入。查询优化器指派这些角色，使两个输入中较小的那 个作为生成输入。哈希联接可用于许多

4、类型的集合匹配操作：内联接，左向外联接、右向外联接和完整外联接，左 向半联接和右向半联接、交集、联合和差分。而且，哈希联接的变化形式能够进行重复项删除和 分组操作（如SUM（salary） GROUP BY department）。这些修改对生成和探测角色只使用一个 输入。与合并联接相似，只有当联接谓词中至少有一个等效（WHERE）子句时才能使用哈希联接。然 而，联接一般用于重组合由主键和外键之间的等效谓词表达的关系，因此大多数联接至少有一个 等效子句。用等效谓词表达的列集合称为哈希键，因为这些列有助于哈希函数。还可以有附加的 谓词，并且可以将这些谓词取值为驻留谓词以与哈希值比较分开。哈希键可

5、以是表达式，只要能 从单个行中的列对其进行排它计算。在分组操作中，按列表分组的列是哈希键。在交集等集合操 作中以及删除复制项时，哈希键由所有列组成。内存中的哈希联接哈希联接先扫描或计算整个生成输入，然后在内存中生成哈希表。根据为哈希键计算出的哈 希值，将每行插入哈希存储桶。如果整个生成输入比可用内存少，则可以将所有行都插入哈 希表中。生成阶段后接着是探测阶段。一次一行地对整个探测输入进行扫描或计算，并为每 个探测行计算哈希键的值，扫描相应的哈希存储桶并生成匹配项。Grace哈希联接如果生成输入不适合内存，哈希联接将分步进行。每一步都包括生成阶段和探测阶段。首先， 消耗整个生成和探测输入并（使用

6、哈希键上的哈希函数）将其分区为多个文件。这类文件的 数目称为分区输出端。通过使用哈希键上的哈希函数，可以保证任意两个联接记录必在相同 的文件对中。因此，联接两个大输入的任务简化为相同任务的多个较小的实例。然后将哈希 联接应用于每对分区文件。递归哈希联接如果生成输入非常大，以至于标准外部合并排序的输入需要多个合并级别，则需要多个分区 步骤和多个分区级别。如果只有某些分区较大，则只需对这些分区使用附加的分区步骤。为 使所有的分区步骤尽可能快，将使用大的异步I/O操作以便单个线程就能使多个磁盘驱动 器繁忙工作。什么时候使用NL必须有一个table拥有index；只有一个table有index时，选择

7、没有index的table作为驱动表；当两个table都有index时，选择结果集较小的table作为驱动表（CBO），RBO会选from的最后一个表做驱动表；适用于有index的连接，两个有大小差异的结果集，数据量较小；对于被连接的数据子集较小的情 况，nested loop连接是个较好的选择。nested loop就是扫描一个表，每读到一条记录，就 根据索引去另一个表里面查找，没有索引一般就不会是nested loops。一般在nested loop中，驱动表满足条件结果集不大，被驱动表的连接字段要有索引，这样 就走nstedloop。如果驱动表返回记录太多，就不适合nested loop

8、s 了。如果连接字段没有索 引， 则适合走 hash join ， 因为不需要索引Merge Join合并列要求排序;在不使用index的情况下使用MR join：在连接table的字段上不存在可用的index；查询将返回两个table中大部分的数据块；CBO 认为 table scan 比 index range scan 更少的 cost；适用于没有index的连接，或两个大小接近的超大结果集。sort merge join的操作通常分三步：对连接的每个表做table access full；对table access full 的结果进行排序；进行merge join对排序结果进行合并，

9、前两步站很大的成本，如果行源已 经被排过序那比hash join效率高hash join在oracle7.3中作为NL join的代替方式首次引入的。大小不同的结果集进行连接，小的结果集作为驱动表，创建基于内存的Hash table，大的结 果集计算hash value0 hash join是CBO做大数据集连接时的常用方式。优化器扫描小表（或 数据源），利用连接键（也就是根据连接字段计算hash值）在内存中建立hash表，然后扫 描大表，每读到一条记录就来探测hash表一次，找出与hash表匹配的行。当小表可以全部放入内存中，其成本接近全表扫描两个表的成本之和。如果表很大不能完全 放入内存，这时优化器会将它分割成若干不同的分区，不能放入内存的部分就把该分区写入 磁盘的临时段，此时要有较大的临时段从而尽量提高I/O的性能。临时段中的分区都需要换 进内存做hash joino这时候成本接近于全表扫描小表+分区数*全表扫描大表的代价和。至于两个表都进行分区，其好处是可以使用parallel query，就是多个进程同时对不同的分 区进行join，然后再合并。但是复杂以下条件下hash join可能有优势：两个巨大的表之间的连接。在一个巨大的表和一个小表之间的连接嵌套连接：驱动表小，内表大切在关联的字段上有索引；驱动表的字段逐一扫描内表；返