ORACLE连接实现之HASH JOIN

mp7x6996jj

　　最近，查阅了部分关于HASH JOIN的资料，现整理总结如下，以备忘。
　　HASH JOIN是oracle在7.3版本中引入的一种表连接方式，以补充NESTED LOOP 和sort merge。HASH JOIN具有以下特征：
　　

　　1.只可以运行在CBO模式下
　　2.由于采用了hash函数的计算方式，因此只适用于等值操作
　　3.对hash_area_size的大小非常敏感，过大或者过小都会影响到执行效率,因此。建议采用ORACLE的自动内存管理机制；
　　4.hash join属于CPU密集型操作（用于hash运算等），尤其在并行模式下，对cpu的效果更加明显，因此在cpu资源非常紧张的情况下，我们可以尝试屏蔽hashjoin，以便提高数据库的整体性能。
　　5.作为小表的数据在链接列上最好是分布均匀的
　　

　　与hash join 相关的参数：
　　1.HASH_JOIN_ENABLED
　　在10g的版本中，已成为隐藏函数_hash_join_enabled,（要查看隐藏函数，参考：http://blog.csdn.net/yidian815/article/details/12154567）。我们可以在system和session级别来修改这个参数。
　　2.HASH_AREA_SIZE
　　这个参数控制hash join可用的内存区域的大小，默认情况下，该参数为SORT_AREA_SIZE的两倍，ORACLE不建议我们手动修改该参数的值，为了提高hashjoin的性能，最好保证整个小表集合的数据可以完全放入内存中，但是在完全放入内存后，再继续增加hasherea意义不大，而且可能使效率下降（例如，消耗在内存管理）。
　　在单个session中，可能同时存在多个hash area，因此一个sql查询可能同时存在多个hash join。
　　3.worderea_size_policy
　　关于该参数的解释，请看：http://blog.csdn.net/yidian815/article/details/12158537
　　4.pga_aggregate_target
　　该参数指定pga的内存管理是否使用自动内存管理，当采用自动内存管理时，单个session的占用内存不可以超过pga_aggregate_target的5%，而如果采用并行模式，则所有并行进程的总和不可以超过30%。为了使用大的内存用于hashjoin，可以将wordarea_size_police设置为手工模式，以便于手工指定各个area的内存大小。
　　

　　5。HAHS_MULTIBLOCK_IO_COUNT
　　这个参数决定每次读入hasharea的数据块数量。因此它会对IO性能产生影响。在8.0及之前版本，它的默认值是1，在8i及以后版本，默认值是0。一般设置为1-(65536/DB_BLOCK_SIZE)。
在9i中，这个参数是一个隐藏参数：_HASH_MULTIBLOCK_IO_COUNT，可以通过表x$ksppi查询和修改。
另外，在MTS中，这个参数将不起作用（只会使用1）。
它的最大值受到OS的IO带宽和DB_BLOCK_SIZE的影响。既不能大于MAX_IO_SIZE/DB_BLOCK_SIZE。
在8i及以后版本，如果这个值设置为0，则表示在每次查询时，Oracle自己自动计算这个值。这个值对IO性能影响非常大，因此，建议不要修改这个参数，使用默认值0，让Oracle自己去计算这个值。
如果一定要设置这个值，要保证以下不等式能成立：
R/M < Po2(M/C)
其中，R表示小表的大小；M=HASH_AREA_SIZE*0.9；Po2(n)为n的2次方；C=HASH_MULTIBLOCK_IO_COUNT*DB_BLOCK_SIZE。
　　

　　HASH JOIN 的过程：
　　Hashjoin算法的一个基本思想就是根据小的row sources(称作build input，我们记较小的表为S，较大的表为B) 建立一个可以存在于hash area内存中的hash table，然后用大的row sources(称作probe input) 来探测前面所建的hash table。（这里的小表和大表并不是依据表的物理大小，而是依据在当前query条件下，所查询出的数据的大小）。


　　第1步：对小表数据进行分区，分区的大小满足如下条件：
(Number of Partitions) * C<= Favm *M
其中C为Cluster size，其值为DB_BLOCK_SIZE*HASH_MULTIBLOCK_IO_COUNT；Favm为hasharea内存可以使用的百分比，一般为0.8左右；M为Hash_area_size的大小。
第2步：读取部分小表S数据，对每一条数据计算其hash1和hash2的值，hash1、hash2分别采用不同的hash算法获得，hash1主要用于将记录和分区对应，而hash2主要将记录和bucket对应，每个分区下含有多个bucket。创建hashtable是依据hash2的。
　　第3步，依据hash值在更新位图向量，将对应bucket的标识位置为1
　　第4步：将记录写入对应分区，如果内存不够，则将内存中数据量最大的分区写入硬盘，如果在此之前分区已经写入硬盘，则直接将该记录写入硬盘上的对应分区，写入的数据包括select中的列和hash2。
第5步：读取小表S的剩余部分，2-4，直至小表S全部读完。
第6步：将所有S的分区，按照数据量大小进行排序，然后按照从小到大的顺序读入分区置内存，从而使内存中可以包含最多的分区。
第7步：依据hash2建立hashtable。
　　
第8步：读取大表b的记录，计算hash1和hash2，通过hash2查询位图向量，如果对应的标识位为0，则丢弃该记录，否则继续（这种做法称为“位图过滤”）。
第9步：对通过过滤的数据依据hash1将记录映射到相应的分区中去，如果分区在内存中，则通过hash2与hash table链接，查询对应的表1记录，并将连接结果写入硬盘或者返回给客户端。如果对应分区在硬盘中，则将该记录写入与小表分区对应的大表分区。
第10步：继续读取大表，直至表B读取完毕。
　　第11步：对存储在硬盘中的小表分区和大表分区做hash链接，这时并不一定以小表的分区为驱动表，而是选择两个分区中的数据量较小的分区为驱动分区，这种机制称为“动态角色互换”。
第12步：重复11，直至所有分区处理完毕。
　　注意：如果某个驱动分区过大，无法再内存中装入，oracle会分批次读入该分区的部分数据，建立hash table，没一部分驱动数据均会与探测分区的全部数据进行匹配，从而造成探测分区的多次读入，导致性能的下降。这称为 nested-loophash join.如果有nested-loop hash join发生，我们可以尝试降低HAHS_MULTIBLOCK_IO_COUNT的取值，从而增加分区数量来解决这个问题。

　　

　　

　　三种模式：
　　optimal:此时hash_area_size的大小，可以完全装载驱动表，因此，不会有分区写入到硬盘，此时效率最高。
onepass：此时hash_area_size的大小可以容纳单个或多个分区，但是无法装载全部驱动表，因此，部分分区写入硬盘，导致效率降低。
　　multipass：此时hash_arem_size的大小不足以装载单个分区的大小，因此，会产生nested-loophash join，这是最复杂，最糟糕的hash join，性能会急剧下降。