oracle连接方式(nested loop、 hash join、merge join)

oracle连接方式(nested loop、 hash join、merge join)
在日常基于数据库应用的开发过程中，我们经常需要对多个表或者数据源进行关联查询而得出我们需要的结果集。那么Oracle到底存在着哪几种连接方式？优化器内部又是怎样处理这些连接的？哪种连接方式又是适合哪种查询需求的？只有对这些问题有了清晰的理解后，我们才能针对特定的查询需求选择合适的连接方式，开发出健壮的数据库应用程序。选择合适的表连接方法对SQL语句运行的性能有着至关重要的影响。下面我们就Oracle常用的一些连接方法及适用情景做一个简单的介绍。
3.1 嵌套循环连接(nested loop)
嵌套循环连接的工作方式是这样的：
1、 Oracle首先选择一张表作为连接的驱动表，这张表也称为外部表（Outer Table）。由驱动表进行驱动连接的表或数据源称为内部表（Inner Table）。
2、 提取驱动表中符合条件的记录，与被驱动表的连接列进行关联查询符合条件的记录。在这个过程中，Oracle首先提取驱动表中符合条件的第一条记录，再与内部表的连接列进行关联查询相应的记录行。在关联查询的过程中，Oracle会持续提取驱动表中其他符合条件的记录与内部表关联查询。这两个过程是并行进行的，因此嵌套循环连接返回前几条记录的速度是非常快的。在这里需要说明的是，由于Oracle最小的IO单位为单个数据块，因此在这个过程中Oracle会首先提取驱动表中符合条件的单个数据块中的所有行，再与内部表进行关联连接查询的，然后提取下一个数据块中的记录持续地循环连接下去。当然，如果单行记录跨越多个数据块的话，就是一次单条记录进行关联查询的。
3、 嵌套循环连接的过程如下所示：
Nested loop
  Outer loop
    Inner loop
    我们可以看出这里面存在着两个循环，一个是外部循环，提取驱动表中符合条件的每条记录。另外一个是内部循环，根据外循环中提取的每条记录对内部表进行连接查询相应的记录。由于这两个循环是嵌套进行的，故此种连接方法称为嵌套循环连接。
嵌套循环连接适用于查询的选择性强、约束性高并且仅返回小部分记录的结果集。通常要求驱动表的记录（符合条件的记录，通常通过高效的索引访问）较少，且被驱动表连接列有唯一索引或者选择性强的非唯一索引时，嵌套循环连接的效率是比较高的。
嵌套循环连接驱动表的选择也是连接中需要着重注意的一点，有一个常见的误区是驱动表要选择小表，其实这是不对的。假如有两张表A、B关联查询，A表有1000000条记录，B表有10000条记录，但是A表过滤出来的记录只有10条，这时候显然用A表当做驱动表是比较合适的。因此驱动表是由过滤条件限制返回记录最少的那张表，而不是根据表的大小来选择的。
    在外连接查询中，如果走嵌套循环连接的话，那么驱动表必然是没有符合条件关联的那张表，也就是后面不加(+)的那张表。这是由于外连接需要提取可能另一张表没符合条件的记录，因此驱动表需要是那张我们要返回所有符合条件记录的表。比如下面这个查询，
嵌套循环连接返回前几行的记录是非常快的，这是因为使用了嵌套循环后，不需要等到全部循环结束再返回结果集，而是不断地将查询出来的结果集返回。在这种情况下，终端用户将会快速地得到返回的首批记录，且同时等待Oracle内部处理其他记录并返回。如果查询的驱动表的记录数非常多，或者被驱动表的连接列上无索引或索引不是高度可选的情况，嵌套循环连接的效率是非常低的
-- 删除原表
drop table t1;

-- 建立测试表
create table t1(
f1 varchar2(10),
f2 varchar2(1000)
)
tablespace CTL
  pctfree 98;

-- 填充测试内容
insert into t1(f1,f2)
select rownum, lpad(rownum,700,'0')
from dba_tables a, dba_tab_cols b
where a.owner = b.owner
and rownum < 10000;
commit;

-- 检查测试内容格式
select sys.dbms_rowid.rowid_block_number(rowid), f1, f2 from t1;
-- 每条记录都存储在单独的数据块中
select count( distinct sys.dbms_rowid.rowid_block_number(rowid)) from t1;

/*
用同样的方式建立表t2
*/

-- 删除原表
drop table t2;

-- 建立测试表
create table t2(
f1 varchar2(10),
f2 varchar2(1000)
)
tablespace CTL
  pctfree 98;

-- 填充测试内容
insert into t2(f1,f2)
select rownum * 10, lpad(rownum * 10,700,'0')
from dba_tables a, dba_tab_cols b
where a.owner = b.owner
and rownum < 1000;
commit;

-- 检查测试内容格式
select sys.dbms_rowid.rowid_block_number(rowid), f1, f2 from t2;
-- 每条记录都存储在单独的数据块中
select count( distinct sys.dbms_rowid.rowid_block_number(rowid)) from t2;

create index ind_t1_f1 on t1(f1);
create index ind_t2_f1 on t2(f1);

--首先我们来看使用nested loop关联方式, 不同表作为驱动时的情况.
1, 表t2作为驱动表
select /*+ ordered use_nl(t1 , t2)*/
t1.f1, t2.f1
from ctl.t2 t2,ctl.t1 t1
where t1.f1 = t2.f1
  and t1.f1 < 1000;

Execution Plan
----------------------------------------------------------
   0   SELECT STATEMENT Optimizer=CHOOSE (Cost=84 Card=4 Bytes=56)
   1 0   NESTED LOOPS (Cost=84 Card=4 Bytes=56)
   2 1     TABLE ACCESS (FULL) OF 'T2' (Cost=2 Card=82 Bytes=574)
   3 1     INDEX (RANGE SCAN) OF 'IND_T1_F1' (NON-UNIQUE) (Cost=1 C
   ard=1 Bytes=7)
Cost = outer access cost + (inner access cost * outer cardinality)
    Cost = 2 + 1 * 82 = 84;
Statistics
----------------------------------------------------------
   0  recursive calls
   0  db block gets
       2020  consistent gets
  23  physical reads
   0  redo size
       2650  bytes sent via SQL*Net to client
721  bytes received via SQL*Net from client
   8  SQL*Net roundtrips to/from client
   0  sorts (memory)
   0  sorts (disk)
  99  rows processed

2, t1作为驱动表
Execution Plan
----------------------------------------------------------
   0   SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=4 Bytes=56)
   1 0   NESTED LOOPS (Cost=6 Card=4 Bytes=56)
   2 1     TABLE ACCESS (FULL) OF 'T1' (Cost=2 Card=4 Bytes=28)
   3 1     INDEX (RANGE SCAN) OF 'IND_T2_F1' (NON-UNIQUE) (Cost=1 C
   ard=1 Bytes=7)
Cost = outer access cost + (inner access cost * outer cardinality)
    Cost = 2 + 1 * 4 = 84;
Statistics
----------------------------------------------------------
   0  recursive calls
   0  db block gets
      11123  consistent gets
   3  physical reads
   0  redo size
       2650  bytes sent via SQL*Net to client
721  bytes received via SQL*Net from client
   8  SQL*Net roundtrips to/from client
   0  sorts (memory)
   0  sorts (disk)
  99  rows processed
3.2, 哈希连接(hash join)
哈希连接分为两个阶段，如下。
1、 构建阶段：优化器首先选择一张小表做为驱动表，运用哈希函数对连接列进行计算产生一张哈希表。通常这个步骤是在内存（hash_area_size）里面进行的，因此运算很快。
2、 探测阶段：优化器对被驱动表的连接列运用同样的哈希函数计算得到的结果与前面形成的哈希表进行探测返回符合条件的记录。这个阶段中如果被驱动表的连接列的值没有与驱动表连接列的值相等的话，那么这些记录将会被丢弃而不进行探测
哈希连接比较适用于返回大数据量结果集的连接。
使用哈希连接必须是在CBO模式下，参数hash_join_enabled设置为true，
哈希连接只适用于等值连接。从Oracle9i开始，哈希连接由于其良好的性能渐渐取代了原来的排序合并连接。

SQL> select /*+ ordered use_hash(t1 , t2)  */
t1.f1, t2.f1
from ctl.t1 t1,ctl.t2 t2
where t1.f1 = t2.f1  2    3    4  ;

999 rows selected.

Execution Plan
----------------------------------------------------------
   0   SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=82 Bytes=1148
   )
   1 0   HASH JOIN (Cost=5 Card=82 Bytes=1148)
   2 1     TABLE ACCESS (FULL) OF 'T1' (Cost=2 Card=82 Bytes=574)
   3 1     TABLE ACCESS (FULL) OF 'T2' (Cost=2 Card=82 Bytes=574)

Statistics
----------------------------------------------------------
   0  recursive calls
   0  db block gets
      11113  consistent gets
   0  physical reads
   0  redo size
      23590  bytes sent via SQL*Net to client
       1381  bytes received via SQL*Net from client
  68  SQL*Net roundtrips to/from client
   0  sorts (memory)
   0  sorts (disk)
999  rows processed
3.3, 排序合并连接(merge join)
排序合并连接的方法非常简单。在排序合并连接中是没有驱动表的概念的，两个互相连接的表按连接列的值先排序，排序完后形成的结果集再互相进行合并连接提取符合条件的记录。相比嵌套循环连接，排序合并连接比较适用于返回大数据量的结果。
排序合并连接在数据表预先排序好的情况下效率是非常高的，也比较适用于非等值连接的情况，比如>、>=、<=等情况下的连接（哈希连接只适用于等值连接）。由于Oracle中排序操作的开销是非常消耗资源的，当结果集很大时排序合并连接的性能很差，于是Oracle在7.3之后推出了新的连接方式——哈希连接。
1, rbo模式;
2, 不等于关联(> < >= <= <>)
3, hash_join_enabled = false;
4, 数据源已排序