表连接方式(2)-Hash Join优化方案总结

一. 什么是HASH JOIN?

我们在《表连接方式-(1)Nested_loop优化方案总结》中提到,嵌套循环应为SQL返回少量数据的需求的最佳连接方式(仅限于等值查询)但是如果表连接关联返回了大量的数据需求时,SQL的执行计划应该走HASH JOIN,并且HASH连接检索内存中的数据要比访问索引的速度快的多,HASH JOIN的实现原理简单来如下(算法非常复杂,节选了相关重要步骤):

1.当两表进行关联时,将对象结果集相对较小(或过滤后结果集相对较小)的表设定为驱动表,将驱动表的查询列(结果集)与Join列读入PGA中

2.将连接列(驱动表结果集)进行HASH 运算,构建HASH TABLE,存储连接列与JOIN列,存入HASH BUCKET,同时构建标记位图,标记每个HASH BUCKET是否存在记录

3.访问被驱动表,读取被驱动表每一行记录,并按照驱动表的连接列做HASH运算,匹配驱动表中已计算好的HASH BUCKET,匹配连接列是否相等,如果相等则将查询列与匹配记录一并返回,如果不能匹配,则将重复构建驱动表标记位图。

二.执行计划相关:

select /*+ gather_plan_statistics */ t.owner,t.object_id from t,t1
where t.owner=t1.username

HASH-1

1.根据HASH JOIN原理,驱动表与被驱动表只需要访问1次即可,所以不像Nested Loop,HASH JOIN的连接列驱动表与被驱动表不需要创建索引。

2.Used-Mem表示消耗多少PGA内存,OMem表示当前操作完成所有内存工作区(Work Aera)操作所总共使用私有内存(PGA)中工作区的大小,1Mem表示当工作区大小无法满足操作所需的大小时,需要将部分数据写入到临时磁盘空间中,写入一次完成操作,为One-Pass;多次写入则为:Multi_Pass,该值为CBO估算,并不能代表真实的使用情况,仅供参考。

三,优化方案总结:

1.HASH连接的驱动表锁对应的连接列选择性要高,如果选择列存在严重倾斜,则会出现驱动表中的HASH BUCKET存在过多的记录,导致很长时间耗费在遍历HASH BUCKET的记录上,现象为SQL长时间不结束,CPU居高不下,逻辑读反而没有正向升高。小表与大表进行HASH JOIN时,小表连接列选择性不错,HASH JOIN的效率可以约等于大表全表扫描的时间(遍历大表,小表HASH BUCKET记录数较少,几乎可以忽略不计)

HASH-2

2.如果PGA采用自动管理,单个进程的PGA work area Limit 为1GB,手动管理则Limit为2GB,如果驱动表超过PGA限制,可以采取并行parallel(x)进行拆分,单个进程能够容纳拆分的数据,避免出现One Pass,Multi_Pass情况.