最近,查阅了部分关于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的性能,最好保证整个小表集合的数据可以完全放入内存中,但是在完全放入内存后,再继续增加hash
erea意义不大,而且可能使效率下降(例如,消耗在内存管理)。
在单个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%。为了使用大的内存用于hash
join,可以将wordarea_size_police设置为手工模式,以便于手工指定各个area的内存大小。
5。HAHS_MULTIBLOCK_IO_COUNT
这个参数决定每次读入hash
area的数据块数量。因此它会对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 的过程:
Hash
join算法的一个基本思想就是根据小的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为hash
area内存可以使用的百分比,一般为0.8左右;M为Hash_area_size的大小。
第2步:读取部分小表S数据,对每一条数据计算其hash1和hash2的值,hash1、hash2分别采用不同的hash算法获得,hash1主要用于将记录和分区对应,而hash2主要将记录和bucket对应,每个分区下含有多个bucket。创建hash
table是依据hash2的。
第3步,依据hash值在更新位图向量,将对应bucket的标识位置为1
第4步:将记录写入对应分区,如果内存不够,则将内存中数据量最大的分区写入硬盘,如果在此之前分区已经写入硬盘,则直接将该记录写入硬盘上的对应分区,写入的数据包括select中的列和hash2。
第5步:读取小表S的剩余部分,2-4,直至小表S全部读完。
第6步:将所有S的分区,按照数据量大小进行排序,然后按照从小到大的顺序读入分区置内存,从而使内存中可以包含最多的分区。
第7步:依据hash2建立hash
table。
第8步:读取大表b的记录,计算hash1和hash2,通过hash2查询位图向量,如果对应的标识位为0,则丢弃该记录,否则继续(这种做法称为“位图过滤”)。
第9步:对通过过滤的数据依据hash1将记录映射到相应的分区中去,如果分区在内存中,则通过hash2与hash table链接,查询对应的表1记录,并将连接结果写入硬盘或者返回给客户端。如果对应分区在硬盘中,则将该记录写入与小表分区对应的大表分区。
第10步:继续读取大表,直至表B读取完毕。
第11步:对存储在硬盘中的小表分区和大表分区做hash
链接,这时并不一定以小表的分区为驱动表,而是选择两个分区中的数据量较小的分区为驱动分区,这种机制称为“动态角色互换”。
第12步:重复11,直至所有分区处理完毕。
注意:如果某个驱动分区过大,无法再内存中装入,oracle会分批次读入该分区的部分数据,建立hash table,没一部分驱动数据均会与探测分区的全部数据进行匹配,从而造成探测分区的多次读入,导致性能的下降。这称为 nested-loop
hash join.如果有nested-loop hash join发生,我们可以尝试降低HAHS_MULTIBLOCK_IO_COUNT的取值,从而增加分区数量来解决这个问题。
三种模式:
optimal:此时hash_area_size的大小,可以完全装载驱动表,因此,不会有分区写入到硬盘,此时效率最高。
onepass:此时hash_area_size的大小可以容纳单个或多个分区,但是无法装载全部驱动表,因此,部分分区写入硬盘,导致效率降低。
multipass:此时hash_arem_size的大小不足以装载单个分区的大小,因此,会产生nested-loop
hash join,这是最复杂,最糟糕的hash join,性能会急剧下降。
分享到:
相关推荐
针对这种情况,Oracle在连接键利用一个hash函数将build input和probe input分割成多个不相连的分区(分别记作Si和Bi),这个阶段叫做分区阶段;然后各自相应的分区,即Si和Bi再做Hash join,这个阶段叫做join阶段。
oracle hash join算法原理
Oracle CBO 学习笔记之(1) : 深入理解Oracle Hash Join的代价模型及其执行流程:word,pdf,图例
Oracle中hash join研究.pdf
Hash join算法原理 详细讲述了oracle sql语句的连接方式 对于sql调优提高有很大帮助
ORACLE的优化器共有3种 A、RULE (基于规则) b、COST (基于成本) c、CHOOSE (选择性) 设置缺省的优化器,可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS 。 ...
Oracle的三种表连接方式 详细讲述• sort merge join(SMJ) • nest loop(NL) • hash join(HJ)
oracle9i的查询优化
说明:用于连接到oracle数据库,也可实现用户的切换 用法:conn 用户名/密码 [as sysdba/sysoper] 注意:当用特权用户连接时,必须带上sysdba或sysoper 例子: 3. 断开连接(disc) 说明:断开与当前数据库的连接 ...
Z、连接对应的Oracle实例找出执行SQL B、进行了占用内存大小自动转换,快速查看占有大小是G、M、K 3、将来版本 A、提供Socket侦听,使用telnet直接可以查看 B、提供telnet扩展命令,直接查看性能与语句 C、...
OracleHashJoin算法原理分享.pdf
hash join/merge join/nest loop(cluster join)/index join 2:不借助第三方工具,怎样查看sql的执行计划 set autot on explain plan set statement_id = &item_id for &sql; select * from table(dbms...
Sql中的三种物理连接操作 嵌套循环连接(Nested Loop Join) 合并连接(Merge Join) 哈希匹配(Hash Join)
HASH JOIN会将一个表格和一个已经被执行的JOIN声明的结果连接在一起。JOIN HINT会导致数据库性能的极端低下并应留给DBA处理。 你的另一个选择是使用插入来控制JOIN的执行顺序,下面是使用实例,其中Catalog, Product...
第一部分 ORACLE系统优化基本知识 23 第1章 ORACLE结构回顾 23 §1.1 Oracle数据库结构 23 §1.1.1 Oracle数据字典 23 §1.1.2 表空间与数据文件 24 §1.1.3 Oracle实例(Instance) 24 §1.2 Oracle文件 26 §1.2.1...
本节我使用一个三表连接的sql来说明连续hashjoin时,不同分发方式的不同行为。测试三个表连接的sql如下,加入part表,使用hint让优化器两次hashjoin都使用broadcast分发。ReplicateSQL查询性能类似。SQL执行时间为42...
无论对于broadcast或者hash分发,数据需要通过进程或者节点之间通信的完成传输,分发的数据越多,消耗的dbcpu越多。并行执行时,数据需要分发,本质上是因为...如果hashjoin有一边在连接键上做hash分区,那么优化器可
hash join/merge join/nest loop(cluster join)/index join 2:不借助第三方工具,怎样查看sql的执行计划 set autot on explain plan set statement_id = &item_id for &sql; select * from table(dbms_...
6.2.7 index_join 120 6.2.8 index_ss hint 120 6.3 表关联顺序的hint 125 6.3.1 leading hint 125 6.3.2 ordered hint 126 6.4 表关联操作的hint 127 6.4.1 use_hash,use_nl和use_merge hint 127 6.4.2 no_use_...
且看接下来的具体分析:in其实是将外表和内表进行hash join,exists是先对外表进行loop操作,然后每次loop后再对内表进行查询。 如果两张表大小差不多,那么exists和in的效率差不多。 例如: 一张大表为A,一张小表B...