oracle表连接-----)哈希连接(HashJoin)

哈希连接（hash join）是一种两个表在做表连接时主要依靠哈希运算来得到连接结果集的表连接方法。

对于排序合并连接，如果两个表在施加了目标sql中指定的谓词条件后得到的结果集很大而且需要排序，则排序合并连接的执行效率一定不高；而对于嵌套循环连接，如果驱动表所对应的驱动结果集的记录数很大，即便在被驱动表的连接列上存在索引，此时使用嵌套循环连接的执行效率也会同样不高。为了解决这个问题，于是oracle引进了哈希连接。在oracle 10g及其以后的版本中，优化器 （实际上是cbo，因为哈希连接仅适用于cbo）在解析目标sql的时候是否考虑哈希连接受限于隐含参数_hash_join_enabled,默认值是true.

对于哈希连接的优缺点及适用场景如下：

a,哈希连接不一定会排序，或者说大多数情况下都不需要排序

b,哈希连接的驱动表所对应的连接列的选择性尽可能好。

c,哈希只能用于cbo，而且只能用于等值连接的条件。（即使是哈希反连接，oracle实际上也是将其换成等值连接）。

c,哈希连接很适用小表和大表之间做连接且连接结果集的记录数较多的情形，特别是小表的选择性非常好的情况下，这个时候哈希连接的执行时间就可以近似看做和全表扫描个个大表的费用时间相当。

e,当两个哈希连接的时候，如果在施加了目标sql中指定的谓词条件后得到的数据量较小的那个结果集所对应的hash table能够完全被容纳在内存中（pga的工作区），此时的哈希连接的执行效率非常高。

oracle表之间的连接之哈希连接(hash join)，其特点如下：

1，驱动表和被驱动表都是最多只被访问一次。

2，哈希连接的表有驱动顺序。

3，哈希表连接的表无需要排序，但是他在做连接之前做哈希运算的时候，会用到hash_area_size来创建哈希表。

4，哈希连接不适用于的连接条件是：不等于<>，大于>，小于<，小于等于<=，大于等于>=，like。

5，哈希连接索引列在表连接中无特殊要求，与单表情况无异。

下面我来做个实验来证实如上的结论：

具体的测试基础表请查看本人blog 如下链接：

oracle表连接之----〉嵌套循环(nested loops join)

测试t2表仅被访问1次

sql> select /*+ leading(t1) use_hash(t2)*/ * from t1,t2 where t1.id=t2.t1_id;

此处省略执行的结果记录集

sql> select sql_id, child_number, sql_text from v$sql where sql_text like '%use_hash(t2)%';

sql_id child_number sql_text
------------- ------------ --------------------------------------------------------------------------------
7d64k5stnc3sk 0 select sql_id, child_number, sql_text from v$sql where sql_text like '%use_hash
036fyatp73h9n 0 select /*+ leading(t1) use_hash(t2)*/ * from t1,t2 where t1.id=t2.t1_id

sql> select * from table(dbms_xplan.display_cursor('036fyatp73h9n',0,'allstats last'));

plan_table_output
--------------------------------------------------------------------------------
sql_id 036fyatp73h9n, child number 0
-------------------------------------
select /*+ leading(t1) use_hash(t2)*/ * from t1,t2 where t1.id=t2.t1_id
plan hash value: 1838229974
--------------------------------------------------------------------------------
| id | operation | name | starts| e-rows | a-rows | a-time | buff
--------------------------------------------------------------------------------
|* 1 | hash join | | 1 | 100 | 100 |00:00:00.04 | 1
| 2 | table access full| t1 | 1 | 100 | 100 |00:00:00.01 |
| 3 | table access full| t2 | 1 | 100k| 100k|00:00:00.01 | 1
--------------------------------------------------------------------------------
predicate information (identified by operation id):
---------------------------------------------------
1 - access("t1"."id"="t2"."t1_id")
note

plan_table_output
--------------------------------------------------------------------------------
-----
- dynamic sampling used for this statement

23 rows selected

从上面的执行计划可以看出，hash连接中，驱动表和被驱动表都只会被访问1次。

下面的实验来证明驱动表和被驱动表都是被访问0次。

sql> select /*+ leading(t1) use_hash(t2)*/ * from t1,t2 where t1.id=t2.t1_id and 1=2;

id num information id t1_id num information
---------- ---------- -------------------------------------------------------------------------------- ---------- ---------- ---------- --------------------------------------------------------------------------------
sql> select sql_id, child_number, sql_text from v$sql where sql_text like '%use_hash(t2)%';

sql_id child_number sql_text
------------- ------------ --------------------------------------------------------------------------------
7d64k5stnc3sk 0 select sql_id, child_number, sql_text from v$sql where sql_text like '%use_hash
cknub2x1sx8tn 0 select /*+ leading(t1) use_hash(t2)*/ * from t1,t2 where t1.id=t2.t1_id and 1=2
2jhn0mg57v1tz 0 select sql_id, child_number, sql_text from v$sql where sql_text like '%use_hash
036fyatp73h9n 0 select /*+ leading(t1) use_hash(t2)*/ * from t1,t2 where t1.id=t2.t1_id

sql> select * from table(dbms_xplan.display_cursor('cknub2x1sx8tn',0,'allstats last'));

plan_table_output
--------------------------------------------------------------------------------
sql_id cknub2x1sx8tn, child number 0
-------------------------------------
select /*+ leading(t1) use_hash(t2)*/ * from t1,t2 where t1.id=t2.t1_id and 1=2
plan hash value: 487071653
--------------------------------------------------------------------------------
| id | operation | name | starts | e-rows | a-rows | a-time | om
--------------------------------------------------------------------------------
|* 1 | filter | | 1 | | 0 |00:00:00.01 |
|* 2 | hash join | | 0 | 100 | 0 |00:00:00.01 | 7
| 3 | table access full| t1 | 0 | 100 | 0 |00:00:00.01 |
| 4 | table access full| t2 | 0 | 100k| 0 |00:00:00.01 |
--------------------------------------------------------------------------------
predicate information (identified by operation id):
---------------------------------------------------
1 - filter(null is not null)
2 - access("t1"."id"="t2"."t1_id")

plan_table_output
--------------------------------------------------------------------------------
note
-----
- dynamic sampling used for this statement

25 rows selected

通过上面的两个执行计划可以得出结论:hash连接中，驱动表和被驱动表都只会被访问1次或者0次。