oracle Hash Join及三种连接方式详解程序员

在Oracle中，确定连接操作类型是执行计划生成的重要方面。各种连接操作类型代表着不同的连接操作算法，不同的连接操作类型也适应于不同的数据量和数据分布情况。

 

无论是Nest Loop Join（嵌套循环），还是Merge Sort Join（合并排序连接），都是适应于不同特殊情况的古典连接方法。Nest Loop Join算法虽然可以借助连接列索引，但是带来的随机读成本过大。而Merge Sort Join虽然可以减少随机读的情况，但是带来的大规模Sort操作，对内存和Temp空间压力过大。两种算法在处理海量数据的时候，如果是海量随机读还是海量排序，都是不能被接受的连接算法。本篇中，我们介绍目前比较常用的一种连接方式Hash Join连接。

 

1、Hash Join（哈希连接）原理

 

从Oracle 7.3开始，Hash Join正式进入优化器执行计划生成，只有CBO才能使用Hash Join操作。本质上说，Hash Join连接是借助Hash算法，连带小规模的Nest Loop Join，同时利用内存空间进行高速数据缓存检索的一种算法。

 

下面我们分步骤介绍Hash Join算法步骤：

 

  i.       Hash Join连接对象依然是两个数据表，首先选择出其中一个“小表”。这里的小表，就是参与连接操作的数据集合数据量小。对连接列字段的所有数据值，进行Hash函数操作。Hash函数是计算机科学中经常使用到的一种处理函数，利用Hash值的快速搜索算法已经被认为是成熟的检索手段。Hash函数处理过的数据特征是“相同数据值的Hash函数值一定相同，不同数据值的Hash函数值可能相同”；

 ii.       经过Hash处理过的小表连接列，连同数据一起存放到Oracle PGA空间中。PGA中存在一块空间为hash_area，专门存放此类数据。并且，依据不同的Hash函数值，进行划分Bucket操作。每个Bucket中包括所有相同hash函数值的小表数据。同时建立Hash键值对应位图。

iii.       之后对进行Hash连接大表数据连接列依次读取，并且将每个Hash值进行Bucket匹配，定位到适当的Bucket上（应用Hash检索算法）；

iv.       在定位到的Bucket中，进行小规模的精确匹配。因为此时的范围已经缩小，进行匹配的成功率精确度高。同时，匹配操作是在内存中进行，速度较Merge Sort Join时要快很多；

 

下面是一个Hash Join的执行计划。

 

PLAN_TABLE_OUTPUT

——————————————————————————–

Plan hash value: 779051904

—————————————————————————-

| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time    |

—————————————————————————-

|  0 | SELECT STATEMENT  |      | 2617 |  572K|  142  (1)| 00:00:02 |

|* 1 | HASH JOIN        |      | 2617 |  572K|  142  (1)| 00:00:02 |

|  2 |  TABLE ACCESS FULL| SEGS | 2503 |  312K|   16  (0)| 00:00:01 |

|  3 |  TABLE ACCESS FULL| OBJTS | 31083 | 2914K|  126  (1)| 00:00:02 |

—————————————————————————-

Predicate Information (identified by operation id):

—————————————————

  1 – access(“SEGS”.”SEGMENT_NAME”=”OBJTS”.”OBJECT_NAME”)

 

从原理过程来看，Hash Join与Nest Loop Join/Merge Sort Join存在一定相似度。

 

首先，Hash Join同Nest Loop Join一样，进行一定的嵌套循环匹配操作，不过差异在于匹配进行随机读的范围是受限范围。不会像Nest Loop Join一样直接频繁进行全表规模的随机读。

 

其次，Hash Join同之前介绍过的Merge Sort Join有相似点，都是利用PGA的空间进行独立操作。Hash Join中的Bucket就是保存在内存的PGA中，有一块专门Hash_Area进行该项操作。选择小表作为驱动连接表，就是尽量争取PGA内存中可以完全装下小表数据，尽量不要使用Temp表空间。这样，进行Hash匹配和精确匹配的速度就是有保证的。

 

最后，Hash Join使用的场景是有限制的。其中最大的一个就是连接操作仅能使用“=”连接。因为Hash匹配的过程只能支持相等操作。还有就是连接列的数据分布要尽量做到数据分布均匀，这样产生的Bucket也会尽可能均匀。这样限制匹配的速度才有保证。如果数据列分布偏移严重，Hash Join算法效率会有退化趋势。

 

随着系统数据量的不断增加，出现Hash Join的场景就会越来越多。下面通过一系列实验来确定Hash Join的各种特性。

 

2、Hash Join连接实验

 

首先是准备实验环境。

 

SQL> create table segs as select * from dba_segments where wner=’SYS’;

Table created

 

SQL> create table objts as select * from dba_objects where wner=’SYS’;

Table created

 

SQL> select count(*) from segs;

 COUNT(*)

———-

     2503

 

SQL> select count(*) from objts;

 COUNT(*)

———-

    31083

 

SQL> create index idx_segs_name on segs(segment_name);

Index created

 

SQL> create index idx_objts_name on objts(object_name);

Index created

 

SQL> exec dbms_stats.gather_table_stats(user,’SEGS’,cascade => true);

PL/SQL procedure successfully completed

 

SQL> exec dbms_stats.gather_table_stats(user,’OBJTS’,cascade => true);

PL/SQL procedure successfully completed

 

此时，我们对比三种连接方式的成本因素。

 

SQL> set autotrace traceonly;

SQL> select * from segs, objts where segs.segment_name=objts.object_name;

已选择4870行。

 

执行计划

———————————————————-

Plan hash value: 779051904

—————————————————————————-

| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time    |

—————————————————————————-

|  0 | SELECT STATEMENT  |      | 2617 |  572K|  142  (1)| 00:00:02 |

|* 1 | HASH JOIN        |      | 2617 |  572K|  142  (1)| 00:00:02 |

|  2 |  TABLE ACCESS FULL| SEGS | 2503 |  312K|   16  (0)| 00:00:01 |

|  3 |  TABLE ACCESS FULL| OBJTS | 31083 | 2914K|  126  (1)| 00:00:02 |

—————————————————————————-

Predicate Information (identified by operation id):

—————————————————

  1 – access(“SEGS”.”SEGMENT_NAME”=”OBJTS”.”OBJECT_NAME”)

统计信息

———————————————————-

         1 recursive calls

         0 db block gets

       814 consistent gets

         0 physical reads

         0 redo size

    356347 bytes sent via SQL*Net to client

      3940 bytes received via SQL*Net from client

       326 SQL*Net roundtrips to/from client

         0 sorts (memory)

         0 sorts (disk)

      4870 rows processed

 

SQL> select/*+use_nl(segs,objts)*/*from segs, objts where segs.segment_name=objts.object_name;

 

已选择4870行。

 

执行计划

———————————————————-

Plan hash value: 2045044449

———————————————————————————————–

| Id | Operation                   | Name          | Rows | Bytes | Cost (%CPU)| Time    |

———————————————————————————————–

|  0 | SELECT STATEMENT            |               | 2617 |  572K| 5023  (1)| 00:01:01 |

|  1 | NESTED LOOPS               |               |      |      |           |         |

|  2 |  NESTED LOOPS              |               | 2617 |  572K| 5023  (1)| 00:01:01 |

|  3 |   TABLE ACCESS FULL        | SEGS          | 2503 |  312K|   16  (0)| 00:00:01 |

|* 4 |   INDEX RANGE SCAN         | IDX_OBJTS_NAME |    1 |      |    1  (0)| 00:00:01 |

|  5 |  TABLE ACCESS BY INDEX ROWID| OBJTS         |    1 |   96 |    2  (0)| 00:00:01 |

———————————————————————————————–

 

Predicate Information (identified by operation id):

—————————————————

  4 – access(“SEGS”.”SEGMENT_NAME”=”OBJTS”.”OBJECT_NAME”)

 

统计信息

———————————————————-

         1 recursive calls

         0 db block gets

      5799 consistent gets

         0 physical reads

         0 redo size

    406352 bytes sent via SQL*Net to client

      3940 bytes received via SQL*Net from client

       326 SQL*Net roundtrips to/from client

         0 sorts (memory)

         0 sorts (disk)

      4870 rows processed

 

SQL> select/*+use_merge(segs,objts)*/*from segs, objts where segs.segment_name=objts.object_name;

已选择4870行。

执行计划

———————————————————-

Plan hash value: 2272228973

————————————————————————————-

| Id | Operation          | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time    |

————————————————————————————-

|  0 | SELECT STATEMENT   |      | 2617 |  572K|      |  900  (1)| 00:00:11|

|  1 | MERGE JOIN        |      | 2617 |  572K|      |  900  (1)| 00:00:11 |

|  2 |  SORT JOIN        |      | 2503 |  312K|  920K|   90  (2)| 00:00:02 |

|  3 |   TABLE ACCESS FULL| SEGS | 2503 |  312K|      |   16  (0)| 00:00:01 |

|* 4 |  SORT JOIN        |      | 31083 | 2914K| 8168K|  809  (1)| 00:00:10 |

|  5 |   TABLE ACCESS FULL| OBJTS | 31083 | 2914K|      |  126  (1)| 00:00:02 |

————————————————————————————-

Predicate Information (identified by operation id):

—————————————————

  4 – access(“SEGS”.”SEGMENT_NAME”=”OBJTS”.”OBJECT_NAME”)

      filter(“SEGS”.”SEGMENT_NAME”=”OBJTS”.”OBJECT_NAME”)

 

统计信息

———————————————————-

         1 recursive calls

         0 db block gets

       494 consistent gets

         0 physical reads

         0 redo size

    427743 bytes sent via SQL*Net to client

      3940 bytes received via SQL*Net from client

       326 SQL*Net roundtrips to/from client

         2 sorts (memory)

         0 sorts (disk)

      4870 rows processed

 

详细对比见下图：

 

 

三种连接方式，SQL数据量、语句相同，最后获取不同的成本消耗。可以看出，当数据量达到万级之后，Nest Loop Join的随机读会急剧增加，带来的CPU成本和总执行时间成本也会大大增加。

 

而使用Merge Sort Join带来的块读是相对较少，但是付出的CPU成本和执行时间也是不可忽视的。将数据集合排序映射到内存中（可能要利用Temp Tablespace），需要消耗很大的CPU和内存资源（排序段）。

 

总体来说，Hash Join在这个SQL中还是能带来很好的综合性能的。只有块读稍大，其他指标都是可以接受的最好值。

 

下面我们介绍与Hash Join相关的一些系统参数，和Hash Join进行的三种操作模式。不同的系统参数，可能会给CBO成本运算带来影响。不同的操作模式，帮助我们理解PGA中的hash_area大小是如何影响到Hash Join操作的性能。

 

哈希连接（HASH JOIN）是一种两个表在做表连接时主要依靠哈希运算来得到连接结果集的表连接方法。

在Oracle 7.3之前，Oracle数据库中 的常用表连接方法就只有排序合并连接和嵌套循环连接这两种，但这两种表连接方法都有其明显缺陷。对于排序合并连接，如果两个表在施加了目标SQL中指定的 谓词条件（如果有的话）后得到的结果集很大且需要排序的话，则这种情况下的排序合并连接的执行效率一定是很差的；而对于嵌套循环连接，如果驱动表所对应的 驱动结果集的记录数很大，即便在被驱动表的连接列上存在索引，此时使用嵌套循环连接的执行效率也同样会很差。

为了解决排序合并连接和嵌套循环连接在上述情形下执行效率不高的问题，同时也为了给优化器提供一种新的选择，Oracle在Oracle 7.3中引入了哈希连接。从理论上来说，哈希连接的执行效率会比排序合并连接和嵌套循环连接的执行效率要高，当然，实际情况并不总是这样。

在Oracle 10g及其以后的Oracle数据库版本中，优化器（实际上是CBO，因为哈希连接仅适用于CBO）在解析目标SQL时是否考虑哈希连接是受限于隐含参数 _HASH_JOIN_ENABLED，而在Oracle 10g以前的Oracle数据库版本中，CBO在解析目标SQL时是否考虑哈希连接是受限于参数HASH_JOIN_ENABLED。

_HASH_JOIN_ENABLED的默认值是TRUE，表示允许CBO在解析目标SQL时考虑哈希连接。当然，即使你将该参数的值改成了 FALSE，我们使用USE_HASH Hint依然可以让CBO在解析目标SQL时考虑哈希连接，这说明USE_HASH Hint的优先级高于参数_HASH_JOIN_ENABLED。

   

如果两个表（这里将它们分别命名为表T1和表T2）在做表连接时使用的是哈希连接，则Oracle在做哈希连接时会依次顺序执行如下步骤：

1、  首先Oracle会根据参数HASH_AREA_SIZE、DB_BLOCK_SIZE和_HASH_MULTIBLOCK_IO_COUNT的值来决定Hash Partition的数量（Hash Partition是一个逻辑上的概念，所有Hash Partition的集合就被称之为Hash Table，即一个Hash Table是由多个Hash Partition所组成，而一个Hash Partition又是由多个Hash Bucket所组成）；

2、  表T1和T2在施加了目标SQL中指定的谓词条件（如果有的话）后得到的结果集中数据量较小的那个结果集会被Oracle选为哈希连接的驱动结果集，这里 我们假设T1所对应的结果集的数据量相对较小，我们记为S；T2所对应的结果集的数据量相对较大，我们记为B；显然这里S是驱动结果集，B是被驱动结果 集；

3、  接着Oracle会遍历S，读取S中的每一条记录，并对S中的每一条记录按照该记录在表T1中的连接列做哈希运算，这个哈希运算会使用两个内置哈希函数， 这两个哈希函数会同时对该连接列计算哈希值，我们把这两个内置哈希函数分别记为hash_func_1和hash_func_2，它们所计算出来的哈希值 分别记为hash_value_1和hash_value_2；

4、  然后Oracle会按照hash_value_1的值把相应的S中的对应记录存储在不同Hash Partition的不同Hash Bucket里，同时和该记录存储在一起的还有该记录用hash_func_2计算出来的hash_value_2的值。注意，存储在Hash Bucket里的记录并不是目标表的完整行记录，而是只需要存储位于目标SQL中的跟目标表相关的查询列和连接列就足够了；我们把S所对应的每一个Hash Partition记为Si；

5、  在构建Si的同时，Oracle会构建一个位图（BITMAP），这个位图用来标记Si所包含的每一个Hash Bucket是否有记录（即记录数是否大于0）；

6、  如果S的数据量很大，那么在构建S所对应的Hash Table时，就可能会出现PGA的工作区（WORK AREA）被填满的情况，这时候Oracle会把工作区中现有的Hash Partition中包含记录数最多的Hash Partition写到磁盘上（TEMP表空间）；接着Oracle会继续构建S所对应的Hash Table，在继续构建的过程中，如果工作区又满了，则Oracle会继续重复上述挑选包含记录数最多的Hash Partition并写回到磁盘上的动作；如果要构建的记录所对应的Hash Partition已经事先被Oracle写回到了磁盘上，则此时Oracle就会去磁盘上更新该Hash Partition，即会把该条记录和hash_value_2直接加到这个已经位于磁盘上的Hash Partition的相应Hash Bucket中；注意，极端情况下可能会出现只有某个Hash Partition的部分记录还在内存中，该Hash Partition的剩余部分和余下的所有Hash Partition都已经被写回到磁盘上；

7、  上述构建S所对应的Hash Table的过程会一直持续下去，直到遍历完S中的所有记录为止；

8、  接着，Oracle会对所有的Si按照它们所包含的记录数来排序，然后Oracle会把这些已经排好序的Hash Partition按顺序依次、并且尽可能的全部放到内存中（PGA的工作区），当然，如果实在放不下的话，放不下的那部分Hash Partition还是会位于磁盘上。我认为这个按照Si的记录数来排序的动作不是必须要做的，因为这个排序动作的根本目的就是为了尽可能多的把那些记录 数较小的Hash Partition保留在内存中，而将那些已经被写回到磁盘上、记录数较大且现有内存已经放不下的Hash Partition保留在磁盘上，显然，如果所有的Si本来就都在内存中，也没发生过将Si写回到磁盘的操作，那这里根本就不需要排序了。

9、     至此Oracle已经处理完S，现在可以来开始处理B了；

10、 Oracle会遍历B，读取B中的每一条记录，并对B中的每一条记录按照该记录在表T2中的连接列做哈希运算，这个哈希运算和步骤3中的哈希运算是一模一 样的，即这个哈希运算还是会用步骤3中的hash_func_1和hash_func_2，并且也会计算出两个哈希值hash_value_1和 hash_value_2；接着Oracle会按照该记录所对应的哈希值hash_value_1去Si里找匹配的Hash Bucket；如果能找到匹配的Hash Bucket，则Oracle还会遍历该Hash Bucket中的每一条记录，并会校验存储于该Hash Bucket中的每一条记录的连接列，看是否是真的匹配（即这里要校验S和B中的匹配记录所对应的连接列是否真的相等，因为对于Hash运算而言，不同的值经过哈希运算后的结果可能是一样的）， 如果是真的匹配，则上述hash_value_1所对应B中的记录的位于目标SQL中的查询列和该Hash Bucket中的匹配记录便会组合起来，一起作为满足目标SQL连接条件的记录返回；如果找不到匹配的Hash Bucket，则Oracle就会去访问步骤5中构建的位图，如果位图显示该Hash Bucket在Si中对应的记录数大于0，则说明该Hash Bucket虽然不在内存中，但它已经被写回到了磁盘上，则此时Oracle就会按照上述hash_value_1的值把相应B中的对应记录也以Hash Partition的方式写回到磁盘上，同时和该记录存储在一起的还有该记录用hash_func_2计算出来的hash_value_2的值；如果位图 显示该Hash Bucket在Si中对应的记录数等于0，则Oracle就不用把上述hash_value_1所对应B中的记录写回到磁盘上了，因为这条记录必然不满足 目标SQL的连接条件；这个根据位图来决定是否将上述hash_value_1所对应B中的记录写回到磁盘的动作就是所谓的“位图过滤”；我们把B所对应的每一个Hash Partition记为Bj；

11、 上述去Si中查找匹配Hash Bucket和构建Bj的过程会一直持续下去，直到遍历完B中的所有记录为止；

12、 至此Oracle已经处理完所有位于内存中的Si和对应的Bj，现在只剩下位于磁盘上的Si和Bj还未处理；

13、 因为在构建Si和Bj时用的是同样的哈希函数hash_func_1和hash_func_2，所以Oracle在处理位于磁盘上的Si和Bj的时候可以 放心的配对处理，即只有对应Hash Partition Number值相同的Si和Bj才可能会产生满足连接条件的记录；这里我们用Sn和Bn来表示位于磁盘上且对应Hash Partition Number值相同的Si和Bj；

14、 对于每一对儿Sn和Bn，它们之中记录数较少的会被当作驱动结果集，然后Oracle会用这个驱动结果集的Hash Bucket里记录的hash_value_2来构建新的Hash Table，另外一个记录数较大的会被当作被驱动结果集，然后Oracle会用这个被驱动结果集的Hash Bucket里记录的hash_value_2去上述构建的新Hash Table中找匹配记录；注意，对每一对儿Sn和Bn而言，Oracle始终会选择它们中记录数较少的来作为驱动结果集，所以每一对儿Sn和Bn的驱动结果集都可能会发生变化，这就是所谓的“动态角色互换”；

15、 步骤14中如果存在匹配记录，则该匹配记录也会作为满足目标SQL连接条件的记录返回；

16、 上述处理Sn和Bn的过程会一直持续下去，直到遍历完所有的Sn和Bn为止。

 

对于哈希连接的优缺点及适用场景，我们有如下总结：

Ÿ     哈希连接不一定会排序，或者说大多数情况下都不需要排序；

Ÿ     哈希连接的驱动表所对应的连接列的可选择性应尽可能的好，因为这个可选择性会影响对应Hash Bucket中的记录数，而Hash Bucket中的记录数又会直接影响从该Hash Bucket中查找匹配记录的效率；如 果一个Hash Bucket里所包含的记录数过多，则可能会严重降低所对应哈希连接的执行效率，此时典型的表现就是该哈希连接执行了很长时间都没有结束，数据库所在 database server上的CPU占用率很高，但目标SQL所消耗的逻辑读却很低，因为此时大部分时间都耗费在了遍历上述Hash Bucket里的所有记录上，而遍历Hash Bucket里记录这个动作是发生在PGA的工作区里，所以不耗费逻辑读；

Ÿ     哈希连接只适用于CBO、它也只能用于等值连接条件（即使是哈希反连接，Oracle实际上也是将其转换成了等价的等值连接）；

Ÿ     哈希连接很适合于一个小表和大表之间的表连接，特别是在小表的连接列的可选择性非常好的情况下，这时候哈希连接的执行时间就可以近似看作是和全表扫描那个大表所耗费的时间相当；

Ÿ     当两个表做哈希连接时，如果这两个表在施加了目标SQL中指定的谓词条件（如果有的话）后得到的结果集中数据量较小的那个结果集所对应的Hash Table能够完全被容纳在内存中时（PGA的工作区），则此时的哈希连接的执行效率会非常高。

 

我们可以借助于10104事件所产生的trace文件来观察目标SQL在做哈希连接时的大致过程和一些统计信息（比如用了多少个Hash Partition、多个少Hash Bucket以及各个Hash Bucket都分别有多少条记录等），10104事件在我们实际诊断哈希连接的性能问题时非常有用。

 

使用10104事件观察目标SQL做哈希连接的具体过程为：

oradebug setmypid

oradebug event 10104 trace name context forever, level 1

set autotrace traceonly

实际执行目标SQL（必须要实际执行该SQL，不能用explain plan for）

oradebug tracefile_name