oracle散列连接,Oracle 表之间的连接方式–哈希连接(hash join)！

在OLAP中，hash join是非常常见的连接方式，而且一般是OLAP系统中，可以使用并行来优化hash join的执行效率。下面先来了解一下hash join是如何运算的：当两个表hash join的时候，Oracle会选择一个表作为驱动表，先根据过滤条件过滤掉不必要的数据，然后会把结果进行hash运算，放入进程的hash area，然后扫描被驱动表，也做hash运算，然后到内存的hash area去探测，如果探测成功，就返回数据，否则跳过这行。由于hash join要使用到hash area，所以这里需要考虑单个进程的PGA大小，Oracle也不会让用户任意的消耗内存，hash area也有一定的限制。

PgSQL

SQL>; select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT

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

SQL_ID f5f0bv3b9b6s0, child number 1

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

select /*+ full(dept) */ ename,job,sal ,dname,loc from emp,dept where

emp.deptno=dept.deptno

Plan hash value: 615168685

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

| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |

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

| 0 | SELECT STATEMENT | | 1 | | 14 |00:00:00.01 | 15 | | | |

|* 1 | HASH JOIN | | 1 | 14 | 14 |00:00:00.01 | 15 | 825K| 825K| 715K (0)|

| 2 | TABLE ACCESS FULL| DEPT | 1 | 4 | 4 |00:00:00.01 | 7 | | | |

| 3 | TABLE ACCESS FULL| EMP | 1 | 14 | 14 |00:00:00.01 | 8 | | | |

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

Predicate Information (identified by operation id):

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

1 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

SQL>;select*fromtable(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT

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

SQL_IDf5f0bv3b9b6s0,childnumber1

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

select/*+ full(dept) */ename,job,sal,dname,locfromemp,deptwhere

emp.deptno=dept.deptno

Planhashvalue:615168685

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

|Id|Operation|Name|Starts|E-Rows|A-Rows|A-Time|Buffers|OMem|1Mem|Used-Mem|

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

|0|SELECTSTATEMENT||1||14|00:00:00.01|15||||

|*1|HASHJOIN||1|14|14|00:00:00.01|15|825K|825K|715K(0)|

|2|TABLEACCESSFULL|DEPT|1|4|4|00:00:00.01|7||||

|3|TABLEACCESSFULL|EMP|1|14|14|00:00:00.01|8||||

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

PredicateInformation(identifiedbyoperationid):

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

1-access("EMP"."DEPTNO"="DEPT"."DEPTNO")

在如上的执行计划中，可以看到id=1部分的内存使用情况，说明hash join占用的用户的PGA。DEPT表作为驱动表，starts等于1，表示两个表都扫描了一次。这里和嵌套循环不一样，嵌套循环的被驱动表扫描次数是驱动表的返回行数。由于hash join需要消耗PGA，当驱动表太大，PGA不能容纳驱动表时，就会产生on-disk hash join，如果看过本博客的nested loops介绍，就知道嵌套循环没有mem使用情况，不会消耗PGA。所以一般是OLAP系统中，PGA一般设置比较大，原因就在这里：OLAP系统中，一般表关联是hash join，sort比较多，group by 等都需要消耗PGA。

如果在优化OLAP系统的时候，发现系统中有很多direct path read/write temp等待事件，就需要关注sql了。查看PGA大小，work area是否分配合理等。在说到嵌套循环的时候，也提到过小表，在hash join中也一样，用小表作为驱动表，不过hash join中的小表不是指标的返回行数，而是指：过滤后的行数*列宽度(select中的列)，就拿如上的执行计划来说，选择dept作为驱动表是因为：dept过滤后的行数 4 * (dname+loc+deptno)宽度 < emp过滤后的行数 14 * (ename,job,sal,deptno)宽度。注意这里的行数一定是过滤后的行数。此外还有一点需要注意的地方：hash join只能用于等值连接，因为值被hash后，无法进行大小比较。

在Oracle内部，hash也分三种模式：optimal，onepass，multipass

optimal:当驱动结果集生成的hash表全部可以放入PGA的hash area时，称为optimal，大致过程如下：

1.先根据驱动表，得到驱动结果集

2.在hash area生成hash bulket，并将若干bulket分成一组，成为一个partition，还会生成一个bitmap的列表，每个bulket在上面占一位

3.对结果集的join键做hash运算，将数据分散到相应partition的bulket中，当运算完成后，如果键值唯一性较高的话，bulket里的数据会比较均匀，也有可能有的桶里面数据会是空的，这样bitmap上对应的标志位就是0，有数据的桶，标志位会是1

4.开始扫描第二张表，对jion键做hash运算，确定应该到某个partition的某个bulket去探测，探测之前，会看这个bulket的bitmap是否会1，如果为0，表示没数据，这行就直接丢弃掉

5.如果bitmap为1，则在桶内做精确匹配，判断OK后，返回数据

这个是最优的hash join，他的成本基本是两张表的full table scan，在加微量的hash运算。

onepass

如果进程的pga很小，或者驱动表结果集很大，超过了hash area的大小，会怎么办？当然会用到临时表空间，此时oracle的处理方式稍微复杂点,需要注意上面提到的有个partition的概念，可以这么理解，数据是经过两次hash运算的，先确定你的partition，再确定你的bulket，假设hash area小于整个hash table，但至少大于一个partition的size，这个时候走的就是onepass

当我们生成好hash表后，状况是部分partition留在内存中，其他的partition留在磁盘临时表空间中，当然也有可能某个partition一半在内存，一半在磁盘，剩下的步骤大致如下：

1.扫描第二张表，对join键做hash运算，确定好对应的partition和bulket

2.查看bitmap，确定bulket是否有数据，没有则直接丢弃

3.如果有数据，并且这个partition是在内存中的，就进入对应的桶去精确匹配，能匹配上，就返回这行数据，否则丢弃

4.如果partition是在磁盘上的，则将这行数据放入磁盘中暂存起来，保存的形式也是partition，bulket的方式

5.当第二张表被扫描完后，剩下的是驱动表和探测表生成的一大堆partition，保留在磁盘上

6.由于两边的数据都按照相同的hash算法做了partition和bulket，现在只要成对的比较两边partition数据即可，并且在比较的时候，oracle也做了优化处理，没有严格的驱动与被驱动关系，他会在partition对中选较小的一个作为驱动来进行，直到磁盘上所有的partition对都join完

可以发现，相比optimal，他多出的成本是对于无法放入内存的partition，重新读取了一次，所以称为onepass，只要你的内存保证能装下一个partition，oracle都会腾挪空间，每个磁盘partition做到onepass。

multipass

这是最复杂，最糟糕的hash join，此时hash area小到连一个partition也容纳不下，当扫描好驱动表后，可能只有半个partition留在hash area中，另半个加其他的partition全在磁盘上,剩下的步骤和onepass比价类似，不同的是针对partition的处理

由于驱动表只有半个partition在内存中，探测表对应的partition数据做探测时，如果匹配不上，这行还不能直接丢弃，需要继续保留到磁盘，和驱动表剩下的半个partition再做join，这里举例的是内存可以装下半个partition，如果装的更少的话，反复join的次数将更多，当发生multipass时，partition物理读的次数会显著增加。

转载请注明: 版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!

最后编辑：2014-02-17作者：Jerry

一个积极向上的小青年，热衷于分享--Focus on DB,BI,ETL