Hash Join的执行计划第1个是hash表(build table),第2个探查表(probe table),一般不叫内外表,nested loop才有内外表
Hash表也就是所谓的内表,探查表所谓的外表
两者的执行计划形如:
nested loop
outer table --驱动表
inner table
hash join
build table (inner table) --驱动表
probe table (outer table)
先看一张图片,大致了解Hash Join的过程:
下面详细了解一下Hash Join
㈠ Hash join概念
Hash join算法的一个基本思想就是根据小的row sources(称作build input 也就是前文提到的build table,我们记较小的表为S,较大的表为B)
建立一个可以存在于hash area内存中的hash table
然后用大的row sources(称作probe input,也就是前文提到的probe table) 来探测前面所建的hash table
如果hash area内存不够大,hash table就无法完全存放在hash area内存中
针对这种情况,Oracle在连接键利用一个hash函数将build input和probe input分割成多个不相连的分区
分别记作Si和Bi,这个阶段叫做分区阶段;然后各自相应的分区,即Si和Bi再做Hash join,这个阶段叫做join阶段
如果HASH表太大,无法一次构造在内存中,则分成若干个partition,写入磁盘的temporary segment,则会多一个写的代价,会降低效率
至于小表的概念,对于 hash join 来说,能容纳在 pga 中的 hash table 都可以叫小表,通常比如:
pga_aggregate_target big integer 1073741824
hash area size 大体能使用到40多 M ,这样的话通常可能容纳 几十万的记录
hash area size缺省是2*sort_area_size,我们可以直接修改SORT_AREA_SIZE 的大小,HASH_AREA_SIZE也会跟着改变的
如果你的workarea_size_policy=auto,那么我们只需设定pga_aggregate_target
但请记住,这是一个session级别的参数,有时,我们更倾向于把hash_area_size的大小设成驱动表的1.6倍左右
驱动表仅仅用于nested loop join 和 hash join,但Hash join不需要在驱动表上存在索引,而nested loop join则迫切需求
一两百万记录的表 join上 千万记录的表,hash join的通常表现非常好
不过,多与少,大与小,很多时候很难量化,具体情况还得具体分析
如果在分区后,针对某个分区所建的hash table还是太大的话,oracle就采用nested loop hash join
所谓的nested-loops hash join就是对部分Si建立hash table,然后读取所有的Bi与所建的hash table做连接
然后再对剩余的Si建立hash table,再将所有的Bi与所建的hash table做连接,直至所有的Si都连接完了
㈡ 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