一、
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就完成了。
四、
关于唯一健值的hash位图
这个位图包含了每个hash分区是否有有值的信息。它记录了有数据的分区的hash值。这个位图的最大作用就是,如果S表中的数据没有与内存中的
hash表匹配上,先查看这个位图,已决定是否将没有匹配的数据写入磁盘。那些不可能匹配到的数据(即位图上对应的分区没有数据)就不再写入磁盘。
原文地址:http://www.hellodba.com/reader.php?ID=144&lang=cn
分享到:
相关推荐
Hash join算法的一个基本思想就是根据小的row sources(称作build input,我们记较小的表为S,较大的表为B) 建立一个可以存在于hash area内存中的hash table,然后用大的row sources(称作probe input) 来探测前面所建...
hash join 原理和算法 1.Hash Join概述 2.Hash Join原理 3.Hash Join算法 4.Hash Join的成本
oracle hash join算法原理
Hash join算法原理 详细讲述了oracle sql语句的连接方式 对于sql调优提高有很大帮助
NULL 博文链接:https://juji1010.iteye.com/blog/1535638
Oracle中hash join研究.pdf
其中最引人注目的莫过于多表连接查询支持 hash join 方式了。我们先来看看官方的描述: MySQL 实现了用于内连接查询的 hash join 方式。例如,从 MySQL 8.0.18 开始以下查询可以使用 hash join 进行连接查询: ...
在MySQL 8.0.18中,增加了Hash Join新功能,它适用于未创建索引的字段,做等值关联查询。在之前的版本里,如果连接的字段没有创建索引,查询速度会是非常慢的,优化器会采用BNL(块嵌套)算法。 Hash Join算法是把...
数据库 我自己在 Java 中实现了 SortMergeJoin 和 HashJoin(来自 SQL 的著名 INNER JOIN)。 在更多信息。
Oracle CBO 学习笔记之(1) : 深入理解Oracle Hash Join的代价模型及其执行流程:word,pdf,图例
给大家介绍Mysql 8.0.18 hash join测试的相关知识,本文通过实例代码讲解,非常不错,具有一定的参考借鉴价值,需要的朋友参考下吧
1. 生成Hash Table:将参与join的左表生成Hash Table 3. FILTER处理:按照不等值条件进行过滤,进而输出最终结果 1. 对语法树生
第一次作业
1. 词法语法解析层 2. 逻辑计划层 3. 物理计划层 4. 物理操作符 1. 将左表的所有数据发送到一台MergerServer上,其中既包括ChunkSe
1、 测试概述 2、 实验环境 3、 实验测试观点说明 4、 实验结果
MySQL 8.0.18 稳定版(GA)已于昨日正式发布,Hash Join 也如期而至。 快速浏览一下这个版本的亮点! 1.Hash Join Hash Join 不需要任何索引来执行,并且在大多数情况下比当前的块嵌套循环算法更有效。 2.EXPLAIN ...