哈希链接
在上一篇学习笔记二中对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 | | | |
---------------------------------------------------------------------------------------------------------------
由于条件中有1=2,Starts所表示的表访问次数都是0。因此在Hash Join中标的访问次数要么为1次要么为0次。
驱动顺序对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次