Oracle中的Hash Join连接分析

1、Oracle中的Hash Join连接分析Oracle中的Hash Join连接分析1、Hash Join基本原理哈希连接(HASH JOIN)是一种两个表在做表连接时主要依靠哈希运算来得到连接结果集的表连接方法。本质上说，Hash Join连接是借助Hash算法，连带小规模的Nest Loop Join，同时利用内存空间进行高速数据缓存检索的一种算法。Hash Join算法主要包括以下几个步骤：(1) Hash Join连接对象依然是两个数据表，首先选择出其中一个“小表”。这里的小表，就是参与连接操作的数据集合数据量小。对连接列字段的所有数据值，进行Hash函数操作。Hash函数是计算机科学

2、中经常使用到的一种处理函数，利用Hash值的快速搜索算法已经被认为是成熟的检索手段。Hash函数处理过的数据特征是“相同数据值的Hash函数值一定相同，不同数据值的Hash函数值可能相同”；(2) 经过Hash处理过的小表连接列，连同数据一起存放到Oracle PGA空间中。PGA中存在一块空间为hash_area，专门存放此类数据。并且，依据不同的Hash函数值，进行划分Bucket操作。每个Bucket中包括所有相同hash函数值的小表数据。同时建立Hash键值对应位图。(3) 之后对进行Hash连接大表数据连接列依次读取，并且将每个Hash值进行Bucket匹配，定位到适当的Bucket

3、上(应用Hash检索算法)；(4) 在定位到的Bucket中，进行小规模的精确匹配。因为此时的范围已经缩小，进行匹配的成功率精确度高。同时，匹配操作是在内存中进行，速度较Merge Sort Join时要快很多。从理论上来说，哈希连接的执行效率会比排序合并连接和嵌套循环连接的执行效率要高，当然，实际情况并不总是这样。在Oracle 10g及其以后的Oracle数据库版本中，优化器(实际上是CBO，因为哈希连接仅适用于CBO)在解析目标SQL时是否考虑哈希连接是受限于隐含参数_HASH_JOIN_ENABLED，而在Oracle 10g以前版本中，CBO在解析目标SQL时是否考虑哈希连接是受限于

4、参数HASH_JOIN_ENABLED。_HASH_JOIN_ENABLED的默认值是TRUE，表示允许CBO在解析目标SQL时考虑哈希连接。当然，即使你将该参数的值改成了FALSE，使用USE_HASH Hint依然可以让CBO在解析目标SQL时考虑哈希连接，这说明USE_HASH Hint的优先级高于参数_HASH_JOIN_ENABLED。2、Hash Join分析2.1案例下面是最常见的一个SQL写法：select * from userinfo a,userlog bwhere a.id=b.idand /doc/e88e0bf6ff00

5、bed5b8f31d44.html=1aUserinfo为用户信息表，假设Userinfo字段通过name字段过滤后返回10条记录，Userlog为用户交易详情表，一个用户在此表中有多条记录,假设平均每个用户10条记录。create table userinfo(id number,name varchar2(20),other_column char(2000); create table userlog(id varchar2(20),tran_date date,other_column char(2000);beginfor i in 1 . 1000 loopfor j in 1 .

6、 10 loopinsert into userlog values (i, sysdate-trunc(dbms_random.value(0,30), j | .);end loop;commit;end loop;end;/declarev_sql varchar2(8000) := insert into userinfo values (:b1,:b2,:b3);beginfor i in 1.1000 loopexecute immediate v_sql using i,i|a,other_desc.;commit;end loop;end;/2.2 Hash Join各种连接顺

7、序和访问路径成本分析全扫userinfo执行，对于每条记录中的id字段，应用下面的hash函数，保存在内存中。(1) 表连接顺序userinfo - userlogUserinfo表name字段存在索引Userinfo表单表访问路径为索引，返回10条，将id字段应用hash函数，平均打散到多个桶中, 接下来开始扫描Userlog表，由于该表不存在过滤条件，因此单表访问路径为全表扫描。此时总成本为对userlog表进行1次索引扫描，加上userinfo表走索引的成本。Userinfo表name字段不存在索引Userinfo表表单表访问路径为全表扫描，返回10条，将id字段应用hash函数，平均打

8、散到多个桶中，接下来开始扫描Userlog表，由于该表不存在过滤条件，因此单表访问路径为全表扫描。此时总成本为对userlog表进行1次索引扫描，加上userinfo表走一次全表扫描索引的成本。(2) 表连接顺序userlog - userinfoUserlog表不存在过滤条件，Userlog表单表访问路径为全表扫描，返回10000条，将id字段应用hash函数，平均打散到多个桶中, 接下来开始扫描Userinfo 表。Userinfo表name字段存在索引则userinfo表单表访问路径为走一次name字段的索引，返回10条，将id字段应用hash函数，到桶中进行比对。此时总成本为对userlog表进行1次全表扫描，加上us