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.deptno

Plan 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 耗费的PGA

OMem、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 temp


hash join 耗费 pga 是驱动表耗费内存,把驱动表放到PGA里


还是 把 驱动表的什么列放pga？
其实是 放 select列 + join列


被驱动表不放入PGA

转载于:https://www.cnblogs.com/zhaoyangjian724/p/3797890.html