一、        嵌套循环连接（Nested Loop）：

嵌套循环连接的工作方式是这样的：
1、        Oracle首先选择一张表作为连接的驱动表，这张表也称为外部表（Outer Table）。由驱动表进行驱动连接的表或数据源称为内部表（Inner Table）。
2、        提取驱动表中符合条件的记录，与被驱动表的连接列进行关联查询符合条件的记录。在这个过程中，Oracle首先提取驱动表中符合条件的第一条记录，再与内部表的连接列进行关联查询相应的记录行。在关联查询的过程中，Oracle会持续提取驱动表中其他符合条件的记录与内部表关联查询。这两个过程是并行进行的，因此嵌套循环连接返回前几条记录的速度是非常快的。在这里需要说明的是，由于Oracle最小的IO单位为单个数据块，因此在这个过程中Oracle会首先提取驱动表中符合条件的单个数据块中的所有行，再与内部表进行关联连接查询的，然后提取下一个数据块中的记录持续地循环连接下去。当然，如果单行记录跨越多个数据块的话，就是一次单条记录进行关联查询的。
3、        嵌套循环连接的过程如下所示：
  

NESTED LOOP
 <Outer Loop>
 <Inner Loop>


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

嵌套循环连接适用于查询的选择性强、约束性高并且仅返回小部分记录的结果集。通常要求驱动表的记录（符合条件的记录，通常通过高效的索引访问）较少，且被驱动表连接列有唯一索引或者选择性强的非唯一索引时，嵌套循环连接的效率是比较高的。比如下面这个查询是选用嵌套循环连接的典型例子：

[php]

 SQL>  select e.empno,e.ename,e.job,d.dname
   2   from   emp e,dept d
   3   where  e.deptno=d.deptno
   4   and    e.empno=7900;

      EMPNO ENAME      JOB       DNAME
 ---------- ---------- --------- --------------
       7900 JAMES      CLERK     SALES

 Execution Plan
 ----------------------------------------------------------
    0      SELECT STATEMENT Optimizer=CHOOSE
    1    0   NESTED LOOPS
    2    1     TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
    3    2       INDEX (UNIQUE SCAN) OF 'PK_EMP' (UNIQUE)
    4    1     TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
    5    4       INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)

 ..
 [/php]


在这个查询中，优化器选择emp作为驱动表，根据唯一性索引PK_EMP快速返回符合条件empno为7900的记录，然后再与被驱动表dept的deptno关联查询相应的dname并最终返回结果集。由于dept表上面的deptno有唯一索引PK_DEPT，故查询能够快速地定位deptno对应dname为SALES的记录并返回。
嵌套循环连接驱动表的选择也是连接中需要着重注意的一点，有一个常见的误区是驱动表要选择小表，其实这是不对的。假如有两张表A、B关联查询，A表有1000000条记录，B表有10000条记录，但是A表过滤出来的记录只有10条，这时候显然用A表当做驱动表是比较合适的。因此驱动表是由过滤条件限制返回记录最少的那张表，而不是根据表的大小来选择的。
在外连接查询中，如果走嵌套循环连接的话，那么驱动表必然是没有符合条件关联的那张表，也就是后面不加(+)的那张表。这是由于外连接需要提取可能另一张表没符合条件的记录，因此驱动表需要是那张我们要返回所有符合条件记录的表。比如下面这个查询，就是选择了emp表做为驱动表进行连接：
[php]

Roby@XUE> select emp.ename,dept.dname
   2  from emp,dept
   3  where emp.deptno=dept.deptno(+);

 ENAME      DNAME
 ---------- --------------
 SMITH
 ALLEN
 WARD       SALES
 JONES      RESEARCH
 MARTIN     SALES
 BLAKE      SALES
 CLARK      ACCOUNTING
 SCOTT      RESEARCH
 KING       ACCOUNTING
 TURNER     SALES
 ADAMS      RESEARCH
 JAMES      SALES
 FORD       RESEARCH
 MILLER     ACCOUNTING

 14 rows selected.

 Execution Plan
 ----------------------------------------------------------

 |   0 | SELECT STATEMENT             |         |    14 |   308 |    15   
 |   1 |  NESTED LOOPS OUTER          |         |    14 |   308 |    15   
 |   2 |   TABLE ACCESS FULL          | EMP     |    14 |   126 |     3   
 |   3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    13 |     1   
 |*  4 |    INDEX UNIQUE SCAN         | DEPT_PK |     1 |       |     0   
 ..
 [/php]

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

 二、        排序合并连接(Sort Merge)：

 排序合并连接的方法非常简单。在排序合并连接中是没有驱动表的概念的，两个互相连
 接的表按连接列的值先排序，排序完后形成的结果集再互相进行合并连接提取符合条件的记录。相比嵌套循环连接，排序合并连接比较适用于返回大数据量的结果。以下为排序合并连接的例子：

 [php]

 Roby@XUE> select emp.ename,dept.dname
   2  from emp,dept
   3  where emp.deptno=dept.deptno
   4  /

 ENAME      DNAME
 ---------- --------------
 CLARK      ACCOUNTING
 KING       ACCOUNTING
 MILLER     ACCOUNTING
 JONES      RESEARCH
 SCOTT      RESEARCH
 FORD       RESEARCH
 ADAMS      RESEARCH
 TURNER     SALES
 JAMES      SALES
 WARD       SALES
 MARTIN     SALES
 BLAKE      SALES

 12 rows selected.

 Execution Plan

 ----------------------------------------------------------------------------
 | Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
 ----------------------------------------------------------------------------
 |   0 | SELECT STATEMENT    |      |    12 |   264 |     8  (25)| 00:00:01 |
 |   1 |  MERGE JOIN         |      |    12 |   264 |     8  (25)| 00:00:01 |
 |   2 |   SORT JOIN         |      |     4 |    52 |     4  (25)| 00:00:01 |
 |   3 |    TABLE ACCESS FULL| DEPT |     4 |    52 |     3   (0)| 00:00:01 |
 |*  4 |   SORT JOIN         |      |    12 |   108 |     4  (25)| 00:00:01 |
 |*  5 |    TABLE ACCESS FULL| EMP  |    12 |   108 |     3   (0)| 00:00:01 |

 ..
 [/php]


可以看得出来上述查询首先按dept、emp两张表的deptno先排序，然后排序好的结果集再进行合并连接返回最终的记录。

排序合并连接在数据表预先排序好的情况下效率是非常高的，也比较适用于非等值连接的情况，比如>、>=、<=等情况下的连接（哈希连接只适用于等值连接）。由于Oracle中排序操作的开销是非常消耗资源的，当结果集很大时排序合并连接的性能很差 [/B]


----------------------------------------------------------------------------------------------------------------

ORACLE 连接方式 NESTED LOOP、HASH JOIN

ORACLE 连接方式

NESTED LOOP 

    对于被连接的数据子集较小的情况，nested loop连接是个较好的选择。nested loop就是扫描一个表，每读到一条记录，就根据索引去另一个表里面查找，没有索引一般就不会是 nested loops。 
一般在nested loop中， 驱动表满足条件结果集不大，被驱动表的连接字段要有索引，这样就走nstedloop。如果驱动表返回记录太多，就不适合nested loops了。如果连接字段没有索引，则适合走hash join，因为不需要索引。 
可用ordered提示来改变CBO默认的驱动表，可用USE_NL(table_name1 table_name2)提示来强制使用nested loop。

HASH JOIN 

   hash join是CBO 做大数据集连接时的常用方式。优化器扫描小表（或数据源），利用连接键（也就是根据连接字段计算hash 值）在内存中建立hash表，然后扫描大表，每读到一条记录就来探测hash表一次，找出与hash表匹配的行。
当 小表可以全部放入内存中，其成本接近全表扫描两个表的成本之和。如果表很大不能完全放入内存，这时优化器会将它分割成若干不同的分区，不能放入内存的部分 就把该分区写入磁盘的临时段，此时要有较大的临时段从而尽量提高I/O 的性能。临时段中的分区都需要换进内存做hash join。这时候成本接近于全表扫描小表+分区数*全表扫描大表的代价和。 
    至于两个表都进行分区，其好处是可以使用parallel query，就是多个进程同时对不同的分区进行join，然后再合并。但是复杂。
使用hash join时，HASH_AREA_SIZE初始化参数必须足够的大，如果是9i，Oracle建议使用SQL工作区自动管理，设置WORKAREA_SIZE_POLICY 为AUTO，然后调整PGA_AGGREGATE_TARGET即可。
以下条件下hash join可能有优势：
两个巨大的表之间的连接。
在一个巨大的表和一个小表之间的连接。 
可用ordered提示来改变CBO默认的驱动表，可用USE_HASH(table_name1 table_name2)提示来强制使用hash join。

SORT MERGE JOIN 

    sort merge join的操作通常分三步：对连接的每个表做table access full；对table access full的结果进行排序；进行merge join对排序结果进行合并。sort merge join性能开销几乎都在前两步。一般是在没有索引的情况下，9i开始已经很少出现了，因为其排序成本高，大多为hash join替代了。 
通常情况下hash join的效果都比sort merge join要好，然而如果行源已经被排过序，在执行sort merge join时不需要再排序了，这时sort merge join的性能会优于hash join。
在全表扫描比索引范围扫描再通过rowid进行表访问更可取的情况下，sort merge join会比nested loops性能更佳。
可用USE_MERGE(table_name1 table_name2)提示强制使用sort merge join。