ITPub博客

首页 > Linux操作系统 > Linux操作系统 > HASH JOIN ,MERGE JOIN ,NESTED LOOP

HASH JOIN ,MERGE JOIN ,NESTED LOOP

原创 Linux操作系统 作者:shely 时间:2009-03-13 11:17:07 0 删除 编辑
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)来强制使用排序合并连接
 


 
Nested loop join:

步骤:确定一个驱动表(outer table),另一个表为inner table,驱动表中的每一行与inner表中的相应记录JOIN。类似一个嵌套的循环。适用于驱动表的记录集比较小(<10000)而且inner表需要有有效的访问方法(Index)。需要注意的是:JOIN的顺序很重要,驱动表的记录集一定要小,返回结果集的响应时间是最快的。

cost = outer access cost + (inner access cost * outer cardinality)


| 2 | NESTED LOOPS | | 3 | 141 | 7 (15)|
| 3 | TABLE ACCESS FULL | EMPLOYEES | 3 | 60 | 4 (25)|
| 4 | TABLE ACCESS BY INDEX ROWID| JOBS | 19 | 513 | 2 (50)|
| 5 | INDEX UNIQUE SCAN | JOB_ID_PK | 1 | | |


EMPLOYEES为outer table, JOBS为inner table.

Hash join

步骤:将两个表中较小的一个在内存中构造一个HASH表(对JOIN KEY),扫描另一个表,同样对JOIN KEY进行HASH后探测是否可以JOIN。适用于记录集比较大的情况。需要注意的是:如果HASH表太大,无法一次构造在内存中,则分成若干个partition,写入磁盘的temporary segment,则会多一个写的代价,会降低效率。

cost = (outer access cost * # of hash partitions) + inner access cost
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 665 | 13300 | 8 (25)|
| 1 | HASH JOIN | | 665 | 13300 | 8 (25)|
| 2 | TABLE ACCESS FULL | ORDERS | 105 | 840 | 4 (25)|
| 3 | TABLE ACCESS FULL | ORDER_ITEMS | 665 | 7980 | 4 (25)|
--------------------------------------------------------------------------


ORDERS为HASH TABLE,ORDER_ITEMS扫描

Sort merge join

步骤:将两个表排序,然后将两个表合并。通常情况下,只有在以下情况发生时,才会使用此种JOIN方式:

1.RBO模式

2.不等价关联(>,<,>=,<=,<>)

3.HASH_JOIN_ENABLED=false

4.数据源已排序

cost = (outer access cost * # of hash partitions) + inner access cost
 


 
转载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   ptimizer=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   ptimizer=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  
 
 
表连接方式及使用场合

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。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7299296/viewspace-567149/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-02-19

  • 博文量
    15
  • 访问量
    19889