oracle表连接----->哈希连接(Hash Join)

最新推荐文章于 2024-10-08 11:27:53 发布
最新推荐文章于 2024-10-08 11:27:53 发布
阅读量2.9w 收藏 27
点赞数 3
分类专栏： Oracle10g 数据库SQL优化 文章标签： Oracle HASH JOIN leading table use_hash
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/waterxcfg304/article/details/25872505
版权

哈希连接（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)

 

1，驱动表和被驱动表的访问次数

测试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次。

 

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

下面是T1作为驱动表

SQL〉select /*+ leading(t1) use_hash(t2)*/ * from t1,t2 where t1.id=t2.t1_id and t1.num=20;

Elapsed: 00:00:00.23

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

SQL_ID        CHILD_NUMBER
------------- ------------
SQL_TEXT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0r4hzmyfz74gh            0
select /*+ leading(t1) use_hash(t2)*/ * from t1,t2 where t1.id=t2.t1_id and t1.num=20

8wyc5rgupmsrc            0
select sql_id,child_number,sql_text from v$sql where sql_text like '%leading(t1) use_hash(t2)%'


Elapsed: 00:00:00.01
SQL> select * from table(dbms_xplan.display_cursor('0r4hzmyfz74gh',0,'allstats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  0r4hzmyfz74gh, child number 0
-------------------------------------
select /*+ leading(t1) use_hash(t2)*/ * from t1,t2 where t1.id=t2.t1_id and t1.num=20

Plan hash value: 1838229974

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN         |      |      1 |      1 |      1 |00:00:00.22 |    3480 |   764K|   764K| 299K (0)|
|*  2 |   TABLE ACCESS FULL| T1   |      1 |      1 |      1 |00:00:00.01 |       6 |       |       |          |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   3 |   TABLE ACCESS FULL| T2   |      1 |  87396 |    100K|00:00:00.01 |    3457 |       |       |          |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."ID"="T2"."T1_ID")
   2 - filter("T1"."NUM"=20)

Note
-----

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   - dynamic sampling used for this statement


24 rows selected.

Elapsed: 00:00:00.01

下面是T2作为驱动表

SQL〉select /*+ leading(t2) use_hash(t1)*/ * from t1,t2 where t1.id=t2.t1_id and t1.num=20;

Elapsed: 00:00:01.27

SQL> select * from table(dbms_xplan.display_cursor('090z3a8qh9mrk',0,'allstats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  090z3a8qh9mrk, child number 0
-------------------------------------
select /*+ leading(t2) use_hash(t1)*/ * from t1,t2 where t1.id=t2.t1_id and t1.num=20

Plan hash value: 2959412835

--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
--------------------------------------------------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN         |      |      1 |      1 |      1 |00:00:01.23 |    3545 |  1072 |   1071 |    14M|  2028K|  18M (0)|    9216 |
|   2 |   TABLE ACCESS FULL| T2   |      1 |  87396 |    100K|00:00:00.01 |    3456 |      0 |      0 |       |       |  |          |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|*  3 |   TABLE ACCESS FULL| T1   |      1 |      1 |      1 |00:00:00.01 |       7 |      0 |      0 |       |       |  |          |
--------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."ID"="T2"."T1_ID")
   3 - filter("T1"."NUM"=20)

Note
-----

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   - dynamic sampling used for this statement


24 rows selected.

Elapsed: 00:00:00.01

从上面的执行计划可以看出，T1表作为驱动表的时候，执行时间是Elapsed: 00:00:00.23，used_mem是299K，而T2表作为驱动表的时候，执行时间是

Elapsed: 00:00:01.27，used_mem是18M。

结论：哈希连接中驱动表的顺序是非常重要的，性能差别也大。

 

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

具体实验如下：

SQL> explain plan for select /*+ leading(t2) use_hash(t1)*/ * from t1,t2 where t1.id<>t2.t1_id and t1.num=20;SQL>

Explained.

Elapsed: 00:00:01.16
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4016936828

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 99999 |    21M| 82709   (1)| 00:15:10 |
|   1 |  NESTED LOOPS      |      | 99999 |    21M| 82709   (1)| 00:15:10 |
|   2 |   TABLE ACCESS FULL| T2   |   100K|    10M|   710   (1)| 00:00:08 |
|*  3 |   TABLE ACCESS FULL| T1   |     1 |   107 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("T1"."NUM"=20 AND "T1"."ID"<>"T2"."T1_ID")

15 rows selected.

Elapsed: 00:00:01.89

从上面可以看出执行计划走的是NESTED LOOPS JOIN。而不是HINT指定的HASH ，按照上面的方法可以证明哈希连接不支持不等值连接不等于<>，大于>，小于<，小于等于<=，大于等于>=，like。

 

 

oracle表hash连接原理,深入理解Oracle表(5)：三大表连接方式详解之Hash Join的定义，原...
weixin_39628551的博客
04-05 593
Hash Join只能用于相等连接，且只能在CBO优化器模式下。相对于nested loop join，hash join更适合处理大型结果集 Hash Join的执行计划第1个是hash表(build table)，第2个探查表(probe table)，一般不叫内外表，nested loop才有内外表 Hash表也就是所谓的内表Hash Join只能用于相等连接，且只能在CBO优化器模式下。相...
oracle 表连接 - hash join 哈希连接
dataminer 的博客
12-18 3586
一. hash 连接(哈希连接)原理 指的是两个表连接时, 先利用两表中记录较少的表在内存中建立 hash 表, 然后扫描记录较多的表并探测 hash 表, 找出与 hash 表相匹配的行来得到结果集的表连接方法. 哈希连接只能用于等值连接条件(=)。 假设下面的 sql 语句中表 T1 和 T2 的连接方式是哈希连接, T1 是驱动表 select * from T1, T2 wher
在 Oracle 中利用 `ORA_HASH` 高效处理大规模数据：并行分片的最佳实践20241008
最新发布
Narutolxy的博客
10-08 1163
oracle
Oracle hash join
weixin_33801856的博客
05-11 128
出处： http://www.alidba.net/index.php/archives/440 =================================================================== hash join是oracle里面一个非常强悍的功能，当做hash join时，oracle会选择一个表作为驱动表，先根据过滤条件...
Oracle中的Hash Join详解
cuixie2370的博客
07-04 138
一、 hash join概念hash join(HJ)是一种用于equi-join（而anti-join就是使用NOT IN时的join）的技术。在Oracle中，它是从7.3开始引入的，以代替sort-merge和nested...
ORACLE连接实现之HASH JOIN
844604778
09-29 299
最近，查阅了部分关于HASH JOIN的资料，现整理总结如下，以备忘。 HASH JOIN是oracle在7.3版本中引入的一种表连接方式，以补充NESTED LOOP 和sort merge。HASH JOIN具有以下特征： 1.只可以运行在CBO模式下 2.由于采用了hash函数的计算方式，因此只适用于等值操作 3.对hash_area_size的大小非常敏感，过大或者过小都会影响...
Oracle表连接操作——Hash Join（哈希连接）上
Saming study ORACLE
03-29 669
在Oracle中，确定连接操作类型是执行计划生成的重要方面。各种连接操作类型代表着不同的连接操作算法，不同的连接操作类型也适应于不同的数据量和数据分布情况。     无论是Nest Loop Join（嵌套循环），还是Merge Sort Join（合并排序连接），都是适应于不同特殊情况的古典连接方法。Nest Loop Join算法虽然可以借助连接列索引，但是带来的随机读成本过大。而Mer
Oracle表连接之哈希连接
miki西游 不积跬步,无以至千里;不积小流,无以成江海
11-02 453
Oracle Hash join 是一种非常高效的join 算法，主要以CPU(hash计算)和内存空间(创建hash table)为代价获得最大的效率。Hash join一般用于大表和小表之间的连接，我们将小表构建到内存中，称为Hash cluster，大表称为probe表。   当两个表做hash join时，oracle会选择一个表作为驱动表，先根据过滤条件排除不必要的数据，然后...
Oracle典型的表连接类型（sort merge join(SMJ) 排序-合并连接、nested loops(NL) 嵌套循环、hash join 哈希连接）
林世广
02-02 2329
1、sort merge join(SMJ) 排序-合并连接 连接过程： 将两个表排序，然后再进行join ①首先生产drving table【驱动表】需要的数据，然后对这些数据按照连接操作关联列进行排序； ②然后生产probed table【被探查表】需要的数据，然后对这些数据按照与driving table对应的连接操作列进行排序； ③最后两边已经排序的行被放在一起执行合并操作。 应用场景： ①排序是一个费时、费资源的操作，特别对于大表。所以SMJ通常不是一个特别有效的连接方法，但是如果driving
oracle哈希连接举例,Oracle表联接之哈希连接
weixin_33050129的博客
04-06 1109
Oracle表连接之哈希连接Oracle Hash join 是一种非常高效的join 算法，主要以CPU(hash计算)和内存空间(创建hash table)为代价获得最大的效率。Hash join一般用于大表和小表之间的连接，我们将小表构建到内存中，称为Hash cluster，大表称为probe表。当两个表做hash join时，oracle会选择一个表作为驱动表，先根据过滤条件排除不必要的...
ORACLE数据库SQL优化---表连接类型.docx
12-30
Oracle数据库的SQL优化是数据库管理员和开发人员关注的重要领域，特别是在处理复杂的表连接时。本文主要探讨了Oracle数据库中表连接类型的优化，包括连接顺序、连接方法和访问单表的方法。 1. 表连接顺序： Oracle...
oracle报错（ORA-00600）问题处理
09-10
例如，如果错误是由于哈希连接（Hash Join）导致的，可以使用以下命令临时禁用哈希连接： ```sql ALTER SESSION SET "_hash_join_enabled"=false; ``` 类似地，如果错误是由于哈希分组（Hash Group By）造成的，...
Oracle的Hash Join之探究整理
hhwgaoshu的专栏
04-08 300
Hash join算法原理 自从oracke 7.3以来，oracle提供了一种新的join技术，就是hash join。Hash Join只能用于相等连接，且只能在CBO优化器模式下。相对于nested loop join，hash join更适合处理大型结果集。Hash join不需要在驱动表上存在索引。 一． Hash Join概述 Hash join算法的一个基...
Oracle的表连接方法（三）哈希连接
cuishan5183的博客
06-05 216
Oracle表连接方法有四种： ● 排序合并连接（Sort Merge Join） ● 嵌套循环连接（Nested Loops Join） ● 哈希连接（Hash Join） ● 笛卡尔积（Cart...
oracle数据库use_hash,Oracle数据库的Hash join
weixin_39870700的博客
04-03 625
在开发过程中，很多人经常会使用到Hash Map或者Hash Set这种数据结构，这种数据结构的特点就是插入和访问速度快。当向集合中加入一个对象时，会调用hash算法来获得hash code，然后根据hash code分配存放位置。访问的时，根据hashcode直接找到存放位置。Oracle Hash join 是一种非常高效的join 算法，主要以CPU(hash计算)和内存空间(创建hash ...
oracle哈希连接举例,Oracle的表连接方法（三）哈希连接
weixin_32120627的博客
04-06 412
Oracle表连接方法有四种：● 排序合并连接(Sort Merge Join)● 嵌套循环连接(Nested Loops Join)● 哈希连接(Hash Join)● 笛卡尔积(Cartesian Product)如果两个表对应的结果集很大，使用排序合并的话，排序操作成本较高；使用嵌套循环的话，则循环次数又很多，需要多次访问被驱动表的结果集；为了提高这种情况下表连接的效率，优化器提供了新的表连...
ORACLE Hash Join
07-08 136
ORACLE Hash Join引自：Oracle中hashjoin研究 2012年第27期 SCIENCE&TECHNOLOGYINFORMATIONhttp://hwhuang.iteye.com/blog...
Oracle哈希连接（Hash Join）算法详解
当分区后的哈希表仍然过大，无法完全容纳时，Oracle会采用嵌套循环哈希连接（Nested-Loop Hash Join）。这种方法是部分构建哈希表，然后遍历所有大表记录与哈希表进行连接，重复此过程直到所有小表记录都被处理。 ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值