HASH JOIN算法

哈希连接(HASH JOIN)前文提到，嵌套循环只适合输出少量结果集。如果要返回大量结果集(比如返回100W数据)，根据嵌套循环算法，被驱动表会扫描100W次，显然这是不对的。看到这里你应该明白为什么有些SQL优化了跑几秒，没优化跑几个小时甚至跑1天都不出结果。返回大量结果集适合走HASH JOIN。HASH JOIN算法非常复杂，这里就不讨论了
下面看一个HASH JOIN的例子(基于SCOTT,Oracle11gR2)SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------SQL_ID f5f0bv3b9b6s0, child number 1
-------------------------------------
select /*+ full(dept) */ ename,job,sal ,dname,loc from emp,dept where
emp.deptno=dept.deptnoPlan hash value: 615168685--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 14 |00:00:00.01 | 15 | | | |
|* 1 | HASH JOIN | | 1 | 14 | 14 |00:00:00.01 | 15 | 825K| 825K| 715K (0)|
| 2 | TABLE ACCESS FULL| DEPT | 1 | 4 | 4 |00:00:00.01 | 7 | | | |
| 3 | TABLE ACCESS FULL| EMP | 1 | 14 | 14 |00:00:00.01 | 8 | | | |
--------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 1 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")HASH JOIN需要耗费PGA:OMEM
1MEM
USED_MEM 耗费的PGAOMem、1Mem为执行所需的内存评估值，0Mem为最优执行模式所需内存的评估值，1Mem为one-pass模式所需内存的评估值。关键词 HASH JOIN DEPT离HASH JOIN关键字最近，表示DEPT是驱动表。 Starts等于1，表示两个表都只扫描了一次。再次强调NESTED LOOPS被驱动表会扫描多次。注意观察Omem,1Mem,Used-Mem，它表示HASH JOIN 会消耗PGA，当驱动表太大，PGA不能容纳驱动表时，就会产生on-disk HASH JOIN。现在再回去看看NESTED LOOPS，它没有Omem,1Mem,Used-Mem，也就是说NESTED LOOPS不消耗PGA。我们再看执行计划中 ID=1 这步，它有*号，前面提到，执行计划中有*表示这个操作有过滤(filter)或者是有access。HASH JOIN属于access。通过谓词过滤信息，我们可以知道HASH JOIN的JOIN列是哪些列在做JOIN。这里就是emp.deptno 和dept.deptno做JOIN。驱动表和被驱动变都只扫描一次2. 讲hash jion 原理了HASH JOIN 需要注意的地方：
1.HASH JOIN 在OLTP环境一般没什么优化的地方，在OLAP环境中可以利用并行优化HASH JOIN。
2.利用等待事件监控HASH JOIN的时候，如果发现在做on-disk HASH JOIN(direct path read/write temp)，
可以加大PGA,或者手工设置 work area 分配较大的PGA内存。4.HASH JOIN 选择小表做驱动表，小表指的不是表的行数，而是指的是 行数*列宽度
例子中，选择dept作为驱动表是因为
dept大小 4*(dname+loc+deptno)宽度 < emp大小14*(ename,job,sal,deptno)宽度
在做HASH JOIN优化的时候要特别注意这点。
5.HASH JOIN只能用于等值连接。为什么 hash join 只能用于等值 连接？？？hash后没法比大小hash 连接 驱动表大小是由什么 决定的？rows*avg_length
行数*列宽度 列指的是select后面的列,不包含where条件的列。如果 hash join 驱动表选错了 怎么办？会出现direct path write temp / read temphash join 耗费 pga 是驱动表耗费内存,把驱动表放到PGA里还是 把 驱动表的什么列放pga？
其实是 放 select列 + join列被驱动表不放入PGA