NESTED LOOP:

对于被连接的数据子集较小的情况，嵌套循环连接是个较好的选择。在嵌套循环中，内
表被外表驱动，外表返回的每一行都要在内表中检索找到与它匹配的行，因此整个查询返回
的结果集不能太大（大于1 万不适合），要把返回子集较小表的作为外表（CBO 默认外表是
驱动表），而且在内表的连接字段上一定要有索引。当然也可以用ORDERED 提示来改变CBO
默认的驱动表，使用USE_NL(table_name1 table_name2)可是强制CBO 执行嵌套循环连接。

HASH JOIN :

散列连接是CBO 做大数据集连接时的常用方式，优化器使用两个表中较小的表（或数据
源）利用连接键在内存中建立散列表，然后扫描较大的表并探测散列表，找出与散列表匹配
的行。
这种方式适用于较小的表完全可以放于内存中的情况，这样总成本就是访问两个表的成
本之和。但是在表很大的情况下并不能完全放入内存，这时优化器会将它分割成若干不同的
分区，不能放入内存的部分就把该分区写入磁盘的临时段，此时要有较大的临时段从而尽量
提高I/O 的性能。
也可以用USE_HASH(table_name1 table_name2)提示来强制使用散列连接。如果使用散
列连接HASH_AREA_SIZE 初始化参数必须足够的大，如果是9i，Oracle建议使用SQL工作区
自动管理，设置WORKAREA_SIZE_POLICY 为AUTO，然后调整PGA_AGGREGATE_TARGET 即可。

排序合并连接

通常情况下散列连接的效果都比排序合并连接要好，然而如果行源已经被排过序，在执
行排序合并连接时不需要再排序了，这时排序合并连接的性能会优于散列连接。可以使用
USE_MERGE(table_name1 table_name2)来强制使用排序合并连接

 

---

---

 

 

---

---

 

转载biti的一段话:  
   
  举例，表连接返回一条记录  
  存在两个表，一个   10条记录   ，一个1000万条记录  
   
  若2表都存在连接字段索引，若以小表为驱动表，则  
  代价：    
  10*   (通过索引在大表查询一条记录的代价)    
   
  若以大表为驱动表：  
   
  1000万   *   (通过索引在小表中查询一条记录的代价)  
   
  通过索引获取一条记录，10rows的表，代价通常在   3   blocks    
  索引2块，表一块  
   
  而如果是1000万的表，索引可能达到4块表一块  
  这样一来参考上面的计算，你说哪个更好？很显然！  
   
  小表查询参考  
   
  SQL>   create   table   test   as   select   *   from   all_objects   where   rownum   <   11;  
   
  Table   created.  
   
  SQL>   create   index   test_index   on   test(object_id);  
   
  Index   created.  
   
  SQL>   select   object_id   from   test;  
   
  OBJECT_ID  
  ----------  
  18159  
  7781  
  4841  
  19891  
  22549  
  17099  
  17712  
  4287  
  10107  
  19135  
   
  10   rows   selected.  
   
   
  Execution   Plan  
  ----------------------------------------------------------  
  0   SELECT   STATEMENT   Optimizer=CHOOSE  
  1   0   TABLE   ACCESS   (FULL)   OF   'TEST'  
   
   
   
   
  Statistics  
  ----------------------------------------------------------  
  0   recursive   calls  
  12   db   block   gets  
  6   consistent   gets  
  0   physical   reads  
  0   redo   size  
  736   bytes   sent   via   SQL*Net   to   client  
  425   bytes   received   via   SQL*Net   from   client  
  2   SQL*Net   roundtrips   to/from   client  
  0   sorts   (memory)  
  0   sorts   (disk)  
  10   rows   processed  
   
  SQL>   select   *   from   test   where   object_id   =   4287;  
   
  OWNER   OBJECT_NAME  
  ------------------------------   ------------------------------  
  SUBOBJECT_NAME   OBJECT_ID   DATA_OBJECT_ID   OBJECT_TYPE  
  ------------------------------   ----------   --------------   ------------------  
  CREATED   LAST_DDL_   TIMESTAMP   STATUS   T   G   S  
  ---------   ---------   -------------------   -------   -   -   -  
  SYS   /1033c8a_SqlTypeWithMethods  
  4287   JAVA   CLASS  
  14-NOV-00   03-JUL-03   2003-07-03:11:18:19   INVALID   N   N   N  
   
   
   
  Execution   Plan  
  ----------------------------------------------------------  
  0   SELECT   STATEMENT   Optimizer=CHOOSE  
  1   0   TABLE   ACCESS   (BY   INDEX   ROWID)   OF   'TEST'  
  2   1   INDEX   (RANGE   SCAN)   OF   'TEST_INDEX'   (NON-UNIQUE)  
   
   
   
   
  Statistics  
  ----------------------------------------------------------  
  0   recursive   calls  
  0   db   block   gets  
  3   consistent   gets  
  0   physical   reads  
  0   redo   size  
  1157   bytes   sent   via   SQL*Net   to   client  
  425   bytes   received   via   SQL*Net   from   client  
  2   SQL*Net   roundtrips   to/from   client  
  0   sorts   (memory)  
  0   sorts   (disk)  
  1   rows   processed