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视频
提取码：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)》
《Troubleshooting Oracle Performance》