Oracle优化之连接方法

nested loop（嵌套循环）:

存在着两个循环，一个是外部循环，提取驱动表中符合条件的每条记录。另外一个是内部循环，根据外循环中提取的每条记录对内部表进行连接查询相应的记录。由于这两个循环是嵌套进行的，故此种连接方法称为嵌套循环连接。

特点：

1.一个大表和一个小表(驱动表)连接，连接方式可以是等值或者是不等值

2.驱动表数据较小或者内部表已连接的列有唯一性索引或者高度可选的非唯一性索引，效率很高

3.能快速读取结果集中第一批记录而不必等待整个结果集完全确定下来

嵌套循环连接返回前几行的记录是非常快的，这是因为使用了嵌套循环后，不需要等到全部循环结束再返回结果集，而是不断地将查询出来的结果集返回。在这种情况下，终端用户将会快速地得到返回的首批记录，且同时等待Oracle内部处理其他记录并返回。如果查询的驱动表的记录数非常多，或者被驱动表的连接列上无索引或索引不是高度可选的情况，嵌套循环连接的效率是非常低的。

hash join（散列连接）:

哈希连接分为两个阶段，如下。

1、 构建阶段：优化器首先选择一张相对较小的表做为驱动表，运用哈希函数对连接列进行计算产生一张哈希表。通常这个步骤是在内存（hash_area_size）里面进行的，因此运算很快。

2、 探测阶段：优化器对被驱动表的连接列运用同样的哈希函数计算得到的结果与前面形成的哈希表进行探测返回符合条件的记录。这个阶段中如果被驱动表的连接列的值没有与驱动表连接列的值相等的话，那么这些记录将会被丢弃而不进行探测

特点：

1.一般两张相同大小的表连接,初始参数hash_join_enable=true

2.只能是等价连接,只能是CBO模式

3.只有一张源表需要排序,可能比merge join更快,因为只需要对一张源表排序;

也可能比nested loop更快,因为处理内存中的hash表比处理b-tree索引更快

4.可能会使用到临时表空间,所以最好pag_aggregate_target设置的比较大

哈希连接比较适用于返回大数据量结果集的连接。

merge join（排序-合并）: 两个互相连接的表按连接列的值先排序，排序完后形成的结果集再互相进行合并连接提取符合条件的记录。

特点：

1.首先对2张表的连接列进行排序后再连接

2.当缺乏数据选择性或者有效索引时,或者2个表都比较庞大,可能比nested loop更有效

3.可能使用temp表空间

排序合并连接比较适用于返回大数据量的结果。 

排序合并连接在数据表预先排序好的情况下效率是非常高的，也比较适用于非等值连接的情况，比如>、>=、<=等情况下的连接（哈希连接只适用于等值连接）

笛卡尔积

笛卡尔积连接发生在当一张表的所有行与另一张表的所有行连接的时候，因此这种连接的结果集等于两个表的数据行数相乘。在实际应用中不使用或者避免出现这种连接。