Oracle的Hash Join之探究整理

Hash join算法原理

自从oracke 7.3以来，oracle提供了一种新的join技术，就是hash join。Hash Join只能用于相等连接，且只能在CBO优化器模式下。相对于nested loop join，hash join更适合处理大型结果集。Hash join不需要在驱动表上存在索引。

一．        Hash Join概述
Hash join算法的一个基本思想就是根据小的row sources(称作build input，我们记较小的表为S，较大的表为B) 建立一个可以存在于hash area内存中的hash table，然后用大的row sources(称作probe input) 来探测前面所建的hash table。如果hash area内存不够大，hash table就无法完全存放在hash area内存中。针对这种情况，Oracle在连接键利用一个hash函数将build input和probe input分割成多个不相连的分区（分别记作Si和Bi），这个阶段叫做分区阶段；然后各自相应的分区，即Si和Bi再做Hash join，这个阶段叫做join阶段。
如果在分区后，针对某个分区所建的hash table还是太大的话，oracle就采用nested-loops hash join。所谓的nested-loops hash join就是对部分Si建立hash table，然后读取所有的Bi与所建的hash table做连接，然后再对剩余的Si建立hash table，再将所有的Bi与所建的hash table做连接，直至所有的Si都连接完了。
Hash Join算法有一个限制，就是它是在假设两张表在连接键上是均匀的，也就是说每个分区拥有差不多的数据。但是实际当中数据都是不均匀的，为了很好地解决这个问题，oracle引进了几种技术，位图向量过滤、角色互换、柱状图。
二．        Hash Join原理
我们用一个例子来解释Hash Join算法的原理，以及上述所提到的术语。
考虑以下两个数据集。
S={1,1,1,3,3,4,4,4,4,5,8,8,8,8,10}
B={0,0,1,1,1,1,2,2,2,2,2,2,3,8,9,9,9,10,10,11}
Hash Join的第一步就是判定小表（即build input）是否能完全存放在hash area内存中。如果能完全存放在内存中，则在内存中建立hash table，这是最简单的hash join。
如果不能全部存放在内存中，则build input必须分区。分区的个数叫做fan-out。Fan-out是由hash_area_size和cluster size来决定的。其中cluster size等于db_block_size * hash_multiblock_io_count，hash_multiblock_io_count在oracle9i中是隐含参数。这里需要注意的是fan-out并不是build input的大小/hash_ara_size，也就是说oracle决定的分区大小有可能还是不能完全存放在hash area内存中。大的fan-out导致许多小的分区，影响性能，而小的fan-out导致少数的大的分区，以至于每个分区不能全部存放在内存中，这也影响hash join的性能。
Oracle采用内部一个hash函数作用于连接键上，将S和B分割成多个分区，在这里我们假设这个hash函数为求余函数，即Mod(join_column_value,10)。这样产生十个分区，如下表。

分区                B0        B1        B2        B3        B4        B5        B6        B7        B8        B9
        值        0,0,10,10        1,1,1,1,11        2,2,2,2,2,2        3        NULL        NULL        NULL        NULL        8        9,9,9
S0        10        √                                                                       
S1        1,1,1                √                                                               
S2        Null                                                                               
S3        3,3                                √                                               
S4        4,4,4,4                                                                               
S5        5                                                                               
S6        NULL                                                                               
S7        NULL                                                                               
S8        8,8,8,8                                                                        √       
S9        NULL                                                                               
经过这样的分区之后，只需要相应的分区之间做join即可（也就是所谓的partition pairs），如果有一个分区为NULL的话，则相应的分区join即可忽略。
在将S表读入内存分区时，oracle即记录连接键的唯一值，构建成所谓的位图向量，它需要占hash area内存的5%左右。在这里即为{1,3,4,5,8,10}。
当对B表进行分区时，将每一个连接键上的值与位图向量相比较，如果不在其中，则将其记录丢弃。在我们这个例子中，B表中以下数据将被丢弃
{0,0,2,2,2,2,2,2,9,9,9,9,9}。这个过程就是位图向量过滤。
当S1,B1做完连接后，接着对Si,Bi进行连接，这里oracle将比较两个分区，选取小的那个做build input，就是动态角色互换，这个动态角色互换发生在除第一对分区以外的分区上面。
三．        Hash Join算法
第1步：判定小表是否能够全部存放在hash area内存中，如果可以，则做内存hash join。如果不行，转第二步。
第2步：决定fan-out数。
        (Number of Partitions) * C<= Favm *M
        其中C为Cluster size，
其值为DB_BLOCK_SIZE*HASH_MULTIBLOCK_IO_COUNT；Favm为hash area内存可以使用的百分比，一般为0.8左右；M为Hash_area_size的大小。

第3步：读取部分小表S，采用内部hash函数(这里称为hash_fun_1)，将连接键值映射至某个分区，同时采用hash_fun_2函数对连接键值产生另外一个hash值，这个hash值用于创建hash table用，并且与连接键值存放在一起。
第4步：对build input建立位图向量。
第5步：如果内存中没有空间了，则将分区写至磁盘上。
第6步：读取小表S的剩余部分，重复第三步，直至小表S全部读完。

第7步：将分区按大小排序，选取几个分区建立hash table(这里选取分区的原则是使选取的数量最多)。

第8步：根据前面用hash_fun_2函数计算好的hash值，建立hash table。
第9步：读取表B，采用位图向量进行位图向量过滤。
第10步：对通过过滤的数据采用hash_fun_1函数将数据映射到相应的分区中去，并计算hash_fun_2的hash值。
第11步：如果所落的分区在内存中，则将前面通过hash_fun_2函数计算所得的hash值与内存中已存在的hash table做连接， 将结果写致磁盘上。如果所落的分区不在内存中，则将相应的值与表S相应的分区放在一起。
第12步：继续读取表B，重复第9步，直至表B读取完毕。

第13步：读取相应的(Si,Bi)做hash连接。在这里会发生动态角色互换。
第14步：如果分区过后，最小的分区也比内存大，则发生nested- loop hash join。
四．        Hash Join的成本
1.        In-Memory Hash Join
Cost(HJ)=Read(S)+ build hash table in memory(CPU)+Read(B) +
                Perform In memory Join(CPU)
忽略cpu的时间，则
Cost(HJ)=Read(S)+Read(B)
2.        On-Disk Hash Join
根据上述的步骤描述，我们可以看出
Cost(HJ)=Cost(HJ1)+Cost(HJ2)
其中Cost(HJ1)的成本就是扫描S,B表，并将无法放在内存上的部分写回磁盘，对应前面第2步至第12步。Cost(HJ2)即为做nested-loop hash join的成本，对应前面的第13步至第14步。

其中Cost(HJ1)近似等于Read(S)+Read(B)+Write((S-M)+(B-B*M/S))。

因为在做nested-loop hash join时，对每一chunk的build input，都需要读取整个probe input，因此
Cost(HJ2)近似等于Read((S-M)+n*(B-B*M/S))
其中n是nested-loop hash join需要循环的次数。
n=(S/F)/M
一般情况下，如果n在于10的话，hash join的性能将大大下降。从n的计算公式可以看出，n与Fan-out成反比例，提高fan-out，可以降低n。当hash_area_size是固定时，可以降低cluster size来提高fan-out。

从这里我们可以看出，提高hash_multiblock_io_count参数的值并不一定提高hash join的性能。
五．       作用
1．        确认小表是驱动表
2．        确认涉及到的表和连接键分析过了。
3．        如果在连接键上数据不均匀的话，建议做柱状图。
4．        如果可以，调大hash_area_size的大小或pga_aggregate_target的值。
5．        Hash Join适合于小表与大表连接、返回大型结果集的连接。
六  三种模式
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物理读的次数会显著增加