1、 原理解释
数据库使用Hash Join处理大表的连接，Hash Join处理的两个数据集分别称为构造输入(build input)和探测输入(probe input)。解析器使用
小表做为 build input,如果build input能存储到内存中(依赖hash_area_size 参数设置)，则Hash Join是最优的连接方式，详细步骤如下，
1、以小表做为build input
2、在小表的连接键应用Hash函数，如F(x) to the join key。
3、Hash函数返回一个确定的值。如F(1234)会返回一个确定的值
4、返回值做为Hash table的索引
5、获取数据---在probe input中对连接条件使用Hash函数,判断该条件是否在Hash table。
6、处理Hash 冲突。-----F(1234)和F(5678)可能存在相同的Hash值。需要使用链表或数组方式进一步解决冲突。
如下图所示，
图1 Hash连
接原理
图2 Hash冲突
2、Hash Join特点
1、每张表只扫描一次。
2、使用小表做为build input
3、build input处理完后，才会返回第一条记录
3、跟TOM大神学习Hash Join视频
链接：
https://pan.baidu.com/s/13URbjdKfNFlPmadYsurwOg
提取码：5zwy
4、 Hash Join示例
刷环境脚本
点击(
此处
)折叠或打开
- drop table ht . c_cons_hash ;
- drop table ht . a_amt_hash ;
- create table ht . c_cons_hash as select * from ht . c_cons ; --创建环境
- create table ht . a_amt_hash as select * from ht . a_amt ; --创建环境
- update ht . c_cons_hash set cons_name = 'Hash_Join' where rownum < 5 ;
- commit ;
- create index ht . idx_c_cons_hash_name on ht . c_cons_hash ( cons_name ) ;
- create index ht . idx_a_amt_cons_hash_no on ht . a_amt_hash ( cons_no ) ;
- exec dbms_stats . gather_table_stats ( 'HT' , 'C_CONS_HASH' ) ;
- exec dbms_stats . gather_table_stats ( 'HT' , 'A_AMT_HASH' ) ;
- update ht . c_cons_hash set cons_name = 'Hash_Join' ;
- commit ;
请优化以下语句
select c.org_name,sum(a.amt)
from ht.c_cons_hash c,ht.a_amt_hash a
where c.cons_no=a.cons_no
and c.cons_name='Hash_Join'
group by c.org_name;
优化前执行计划
set autot trace
@待优化SQL
Execution Plan
----------------------------------------------------------
Plan hash value: 3942667065
---------------------------------------------------------------------------------------------------------
| Id | Operation
| Name
| Rows
| Bytes | Cost (%CPU)| Time
|
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT
|
| 2 | 64 | 18 (6)| 00:00:01 |
| 1 | HASH GROUP BY
|
| 2 | 64 | 18 (6)| 00:00:01 |
| 2 | NESTED LOOPS
|
| 12 | 384 | 17 (0)| 00:00:01 |
| 3 | NESTED LOOPS
|
| 12 | 384 | 17 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| C_CONS_HASH
| 2 | 44 | 3 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN
| IDX_C_CONS_HASH_NAME
| 2 |
| 1 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN
| IDX_A_AMT_CONS_HASH_NO | 6 |
| 1 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | A_AMT_HASH
| 6 | 60 | 7 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("C"."CONS_NAME"='Hash_Join')
6 - access("C"."CONS_NO"="A"."CONS_NO")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
61156 consistent gets
0 physical reads
0 redo size
876 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
12 rows processed
优化后执行计划
set autot trace
@待优化SQL
select /*+ use_hash(c,a)*/c.org_name,sum(a.amt)
from ht.c_cons_hash c,ht.a_amt_hash a
where c.cons_no=a.cons_no
and c.cons_name='Hash_Join'
group by c.org_name;
Execution Plan
----------------------------------------------------------
Plan hash value: 4201076277
------------------------------------------------------------------------------------------------------
| Id | Operation
| Name
| Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT
|
|
2 |
64 |
70 (3)| 00:00:01 |
| 1 | HASH GROUP BY
|
|
2 |
64 |
70 (3)| 00:00:01 |
|* 2 | HASH JOIN
|
|
12 |
384 |
69 (2)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| C_CONS_HASH
|
2 |
44 |
3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN
| IDX_C_CONS_HASH_NAME |
2 |
|
1 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL
| A_AMT_HASH
| 59968 |
585K|
65 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C"."CONS_NO"="A"."CONS_NO")
4 - access("C"."CONS_NAME"='Hash_Join')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
334 consistent gets
0 physical reads
0 redo size
876 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
12 rows processed
consistent gets从 61156降低到334，通过本小节的讲解和练习示例，希望大家能掌握Hash Join的原理、实现过程及应用场景。
参数文档
《Oracle? Database Performance Tuning Guide 11g Release 2 (11.2)》
《
https://asktom.oracle.com/pls/apex/asktom.search?tag=hash-join-200606
》
《Troubleshooting Oracle Performance》
编辑推荐：
下一篇：
相关推荐
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效
2 月 9 日消息，雷神 (THUNDEROBOT) 现已宣布推出基于英
-
制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用
2 月 10 日消息，制造商 Musnap 现已在海外推出一款 Oce
热文推荐
- 微课sql优化(15)、表的连接方法(4)-关于Hash Join(哈希连接)
- DB2备份与恢复
DB2备份与恢复
26-03-03 - 微课sql优化(13)、表的连接方法(2)-基础概念
微课sql优化(13)、表的连接方法(2)-基础概念
26-03-03 - 微课sql优化(14)、表的连接方法(3)-关于Nested Loops Join(嵌套循环)
- Oracle面试宝典-锁篇
Oracle面试宝典-锁篇
26-03-03 - 微课sql优化(16)、表的连接方法(5)-关于Merge Join(排序合连接)
- 微课sql优化(17)、不改代码,优化SQL(1)-优化方法总结
微课sql优化(17)、不改代码,优化SQL(1)-优化方法总结
26-03-03 - Oracle日常问题-坏块修复
Oracle日常问题-坏块修复
26-03-03 - Oracle的并行
Oracle的并行
26-03-03 - 江波龙 MWC26 巴塞罗那展示 HLC UFS 与 pTLC UFS 嵌入式闪存解决方案
