一、 hash join概念
hash join(HJ)是一种用于equi-join（而anti-join就是使用NOT IN时的join）的技术。在Oracle中，它是从7.3开始引入的，
以代替sort-merge和nested-loop join方式，提高效率。在CBO（hash join只有在CBO才可能被使用到）模式下，优化器计算代价时，
首先会考虑hash join。
可以通过提示use_hash来强制使用hash join，也可以通过修改会话或数据库参数HASH_JOIN_ENABLED=FALSE（默认为TRUE）强
制不使用hash join。
Hash join的主要资源消耗在于CPU（在内存中创建临时的hash表，并进行hash计算），而merge join的资源消耗主要在于此盘IO
（扫描表或索引）。在并行系统中，hash join对CPU的消耗更加明显。所以在CPU紧张时，最好限制使用hash join。
在绝大多数情况下，hash join效率比其他join方式效率更高：
在Sort-Merge Join(SMJ)，两张表的数据都需要先做排序，然后做merge。因此效率相对最差；
Nested-Loop Join(NL)效率比SMJ更高。特别是当驱动表的数据量很大（集的势高）时。这样可以并行扫描内表。
Hash join效率最高，因为只要对两张表扫描一次。
Hash join一般用于一张小表和一张大表进行join时。Hash join的过程大致如下（下面所说的内存就指sort area，关于过程，后
面会作详细讨论）：
1． 一张小表被hash在内存中。因为数据量小，所以这张小表的大多数数据已经驻入在内存中，剩下的少量数据被放置在临时表空间中；
2． 每读取大表的一条记录，就和小表中内存中的数据进行比较，如果符合，则立即输出数据（也就是说没有读取临时表空间中的小表的数
据）。而如果大表的数据与小表中临时表空间的数据相符合，则不直接输出，而是也被存储临时表空间中。
3． 当大表的所有数据都读取完毕，将临时表空间中的数据以其输出。
如果小表的数据量足够小（小于hash area size），那所有数据就都在内存中了，可以避免对临时表空间的读写。
如果是并行环境下，前面中的第2步就变成如下了：
2． 每读取一条大表的记录，和内存中小表的数据比较，如果符合先做join，而不直接输出，直到整张大表数据读取完毕。如果内存足够，
Join好的数据就保存在内存中。否则，就保存在临时表空间中。
二、 Oracle中与hash join相关的参数
首先，要注意的是，hash join只有在CBO方式下才会被激活。在oracle中与hash join相关的参数主要有以下几个：
1． HASH_JOIN_ENABLED
这个参数是控制查询计划是否采用hash join的“总开关”。它可以在会话级和实例级被修改。默认为TRUE，既可以（不是一定，要看优
化器计算出来的代价）使用。如果设为FALSE，则禁止使用hash join。
2． HASH_AREA_SIZE
这个参数控制每个会话的hash内存空间有多大。它也可以在会话级和实例级被修改。默认（也是推荐）值是sort area空间大小的两倍
（2*SORT_AREA_SIZE）。要提高hash join的效率，就一定尽量保证sort area足够大，能容纳下整个小表的数据。但是因为每个会话都会
开辟一个这么大的内存空间作为hash内存，所以不能过大（一般不建议超过2M）。
在Oracle9i及以后版本中，Oracle不推荐在dedicated server中使用这个参数来设置hash内存，而是推荐通过设置
PGA_AGGRATE_TARGET参数来自动管理PGA内存。保留HASH_AREA_SIZE只是为了向后兼容。在dedicated server中，hash area是从
PGA中分配的，而在MTS(Multi-Threaded Server)中，hash area是从UGA中分配的。
另外，还要注意的是，每个会话并不一定只打开一个hash area，因为一个查询中可能不止一个hash join，这是就会相应同时打开多个
hash area。
3． HAHS_MULTIBLOCK_IO_COUNT
这个参数决定每次读入hash area的数据块数量。因此它会对IO性能产生影响。他只能在init.ora或spfile中修改。在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的过程
一次完整的hash join如下：
1． 计算小表的分区（bucket）数
决定hash join的一个重要因素是小表的分区（bucket）数。这个数字由hash_area_size、hash_multiblock_io_count和
db_block_size参数共同决定。Oracle会保留hash area的20%来存储分区的头信息、hash位图信息和hash表。因此，这个数字的计算公式是：
Bucket数=0.8*hash_area_size/(hash_multiblock_io_count*db_block_size)
2． Hash计算
读取小表数据（简称为R），并对每一条数据根据hash算法进行计算。Oracle采用两种hash算法进行计算，计算出能达到最快速度的hash值
（第一hash值和第二hash值）。而关于这些分区的全部hash值（第一hash值）就成为hash表。
3． 存放数据到hash内存中
将经过hash算法计算的数据，根据各个bucket的hash值（第一hash值）分别放入相应的bucket中。第二hash值就存放在各条记录中。
4． 创建hash位图
与此同时，也创建了一个关于这两个hash值映射关系的hash位图。
5． 超出内存大小部分被移到磁盘
如果hash area被占满，那最大一个分区就会被写到磁盘（临时表空间）上去。任何需要写入到磁盘分区上的记录都会导致磁盘分区被更新。这
样的话，就会严重影响性能，因此一定要尽量避免这种情况。
2-5一直持续到整个表的数据读取完毕。
6． 对分区排序
为了能充分利用内存，尽量存储更多的分区，Oracle会按照各个分区的大小将他们在内存中排序。
7． 读取大表数据，进行hash匹配
接下来就开始读取大表（简称S）中的数据。按顺序每读取一条记录，计算它的hash值，并检查是否与内存中的分区的hash值一致。如果是，返
回join数据。如果内存中的分区没有符合的，就将S中的数据写入到一个新的分区中，这个分区也采用与计算R一样的算法计算出hash值。也就是说这些
S中的数据产生的新的分区数应该和R的分区集的分区数一样。这些新的分区被存储在磁盘（临时表空间）上。
8． 完全大表全部数据的读取
一直按照7进行，直到大表中的所有数据的读取完毕。
9． 处理没有join的数据
这个时候就产生了一大堆join好的数据和从R和S中计算存储在磁盘上的分区。
10． 二次hash计算
从R和S的分区集中抽取出最小的一个分区，使用第二种hash函数计算出并在内存中创建hash表。采用第二种hash函数的原因是为了使数据分布
性更好。
11． 二次hash匹配
在从另一个数据源（与hash在内存的那个分区所属数据源不同的）中读取分区数据，与内存中的新hash表进行匹配。返回join数据。
12． 完成全部hash join
继续按照9-11处理剩余分区，直到全部处理完毕。
整个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适合于小表与大表连接、返回大型结果集的连接。
四、 关于唯一健值的hash位图
这个位图包含了每个hash分区是否有有值的信息。它记录了有数据的分区的hash值。这个位图的最大作用就是，如果S表中的数据没有与内存中的
hash表匹配上，先查看这个位图，已决定是否将没有匹配的数据写入磁盘。那些不可能匹配到的数据（即位图上对应的分区没有数据）就不再写入磁盘。
转载：http://tolywang.itpub.net/post/48/303510
http://space.itpub.net/8183550/viewspace-662623
扫一扫
3577
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
