哈希链接
在上一篇学习笔记二中对Nested Loop Join进行了讨论，通过多次测试得出了嵌套循环链接具有以下特点：
- 嵌套循环有驱动表和被驱动表的概念，驱动顺序不同执行计划差异非常大
- 驱动表只被访问一次，被驱动表被访问多次。嵌套循环访问表的次数直接受驱动表的返回记录数的影响。因此应当让实际返回记录数（A-Rows）小的表作为驱动表，返回记录数大的表作为被驱动表。
- 嵌套循环链接是没有排序过程
Nested Loop Join适合数据量较小的表连接，而且Oracle优化器会选择返回记录数较少的表作为驱动表。而Hash连接是基于吞吐量的操作，在返回大数据量的情况下非常高效。Hash连接不涉及排序，但是会使用内存空间，PGA中的HASH_AREA_SIZE参数来控制所能利用的空间大小。Oracle会利用较小（返回记录数较少）的表在内存中构建一个hash table，然后在扫描较大（记录数较多）的表，探测表中的记录是否在hash table中（根据hash key，表连接条件）。如果内存足够容纳较小的表，那么Hash Join的Cost可以视为读取两个连接表数据所花费的时间（这里忽略了cpu的处理时间）。
哈希连接表访问次数
测试用到的表为test_join_t1包含100条记录，test_join_t2包含10000条记录。首先关联查询两张表
select
*
from
test_join_t1 t1, test_join_t2 t2
where
t1.col1 = t2.col2;
Plan hash value: 1262213046
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 99 |00:00:00.08 | 35 | 27 | | | |
|* 1 | HASH JOIN | | 1 | 100 | 99 |00:00:00.08 | 35 | 27 | 909K| 909K| 1232K (0)|
| 2 | TABLE ACCESS FULL| TEST_JOIN_T1 | 1 | 100 | 100 |00:00:00.02 | 2 | 2 | | | |
| 3 | TABLE ACCESS FULL| TEST_JOIN_T2 | 1 | 10000 | 10000 |00:00:00.05 | 33 | 25 | | | |
---------------------------------------------------------------------------------------------------------------------------------
从执行计划可以看出Oraccle优化器选择了hash join。两张表的访问次数均为1，Used-Mem有值，证明Hash Join需要使用额外的内存空间。实际上哈希连接访问数据表的次数是1或0次。看下面的sql执行结果
select
/*+ use_hash */
*
from
test_join_t1 t1, test_join_t2 t2
where
t1.col1 = t2.col2 and 1 = 2;
Plan hash value: 906286283
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | | | |
|* 1 | FILTER | | 1 | | 0 |00:00:00.01 | | | |
|* 2 | HASH JOIN | | 0 | 100 | 0 |00:00:00.01 | 842K| 842K| |
| 3 | TABLE ACCESS FULL| TEST_JOIN_T1 | 0 | 100 | 0 |00:00:00.01 | | | |
| 4 | TABLE ACCESS FULL| TEST_JOIN_T2 | 0 | 10000 | 0 |00:00:00.01 | | | |
---------------------------------------------------------------------------------------------------------------
驱动顺序对Hash Join的影响
Oracle会将较小的表作为驱动表在PGA中构建Hash table，然后扫描探测另一张较大的表完成hash join。如果选择较大的表作为驱动表构建hash table那么势必会增加内存的占用率，并且构建hash table的效率也会较低。如果PGA中的内存不够，那么还需要将部分数据写入磁盘分批次构建hash table以及进行记录探测，引入物理IO势必会降低效率。首先我们看以test_join_t1作为驱动表的执行计划：
select
/*+ leading(t1) use_hash(t2) */
*
from
test_join_t1 t1, test_join_t2 t2
where
t1.col1 = t2.col2;
Plan hash value: 1262213046
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 99 |00:00:00.03 | 35 | 27 | | | |
|* 1 | HASH JOIN | | 1 | 100 | 99 |00:00:00.03 | 35 | 27 | 909K| 909K| 1185K (0)|
| 2 | TABLE ACCESS FULL| TEST_JOIN_T1 | 1 | 100 | 100 |00:00:00.01 | 2 | 2 | | | |
| 3 | TABLE ACCESS FULL| TEST_JOIN_T2 | 1 | 10000 | 10000 |00:00:00.01 | 33 | 25 | | | |
---------------------------------------------------------------------------------------------------------------------------------
以t2作为驱动表的执行计划如下：
select
/*+ leading(t2) use_hash(t1) */
*
from
test_join_t1 t1, test_join_t2 t2
where
t1.col1 = t2.col2;
Plan hash value: 3788972332
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 99 |00:00:00.04 | 35 | 27 | | | |
|* 1 | HASH JOIN | | 1 | 100 | 99 |00:00:00.04 | 35 | 27 | 962K| 962K| 1407K (0)|
| 2 | TABLE ACCESS FULL| TEST_JOIN_T2 | 1 | 10000 | 10000 |00:00:00.01 | 26 | 25 | | | |
| 3 | TABLE ACCESS FULL| TEST_JOIN_T1 | 1 | 100 | 100 |00:00:00.01 | 9 | 2 | | | |
---------------------------------------------------------------------------------------------------------------------------------
从执行计划可以看出Used-Mem所代表的内存空间使用大小，t2表作为驱动表比t1表作为驱动表大。如果两个表的数据相差更大这里的空间使用大小也会相差更大。我们将test_join_t2表数据量扩充为200w条记录，执行计划如下：
Plan hash value: 3788972332
----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp|
---------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 99 |00:00:06.76 | 5945 | 11823 | 5859 | | | | |
|* 1 | HASH JOIN | | 1 | 100 | 99 |00:00:06.76 | 5945 | 11823 | 5859 | 65M| 4522K| 80M (1)| 55296 |
| 2 | TABLE ACCESS FULL| TEST_JOIN_T2 | 1 | 1865K| 2000K|00:00:00.91 | 5937 | 5933 | 0 | | | | |
| 3 | TABLE ACCESS FULL| TEST_JOIN_T1 | 1 | 100 | 100 |00:00:00.01 | 8 | 0 | 0 | | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------
可以看出内存使用大小变为80M，是之前1w条记录的70倍，并且进行过一次磁盘交换。因此可以看出Oracle为什么会选择较小的表作为驱动表构建hash table。
表索引与哈希连接
对于哈希连接，连接条件上的索引起不到快速检索的作用，而查询限制条件上的索引可能会对快速检索数据起到积极作用。因此哈希连接对表索引并没有特别的要求，跟普通的单标索引一样。
通过上面的实验，已经大概了解了hash join这种连接方式的特点：
- 基于大吞吐量，大数据集
- 存在驱动表和被驱动表之分，驱动表用于在内存中构建hash table
- 表驱动顺序对性能影响很大
- 两个表的数据相差比较大时hash join的效果特别明显
- Hash Join的连接条件必须是等值条件
- 驱动表和被驱动表只被访问1次或0次
扫一扫
1293
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
