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

最新推荐文章于 2024-03-25 06:47:54 发布
最新推荐文章于 2024-03-25 06:47:54 发布
阅读量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。

 

 

waterxcfg304
关注
  • 3
    点赞
  • 踩
  • 27
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
解读 Oracle12c 执行计划——HASH JOIN
梅森上校的博客 业精于勤荒于嬉，形成于思毁于随。
09-24 3151
解读 Oracle12c 执行计划——HASH JOIN   Oracle官方文档中的解释如下： Hash Joins - Hash joins are used for joining large data sets. The optimizer uses the smaller of the two tables or data sources to build a hash table...
Oracle表连接操作——Hash Join
海鲨MYSQL精英架构师
03-08 2923
在Oracle中，确定连接操作类型是执行计划生成的重要方面。各种连接操作类型代表着不同的连接操作算法，不同的连接操作类型也适应于不同的数据量和数据分布情况。  无论是Nest Loop Join（嵌套循环），还是Merge Sort Join（合并排序连接），都是适应于不同特殊情况的古典连接方法。Nest Loop Join算法虽然可以借助连接列索引，但是带来的随机读成本过大。而Merge Sor
Oracle数据库中的哈希连接（Hash Join）核心原理解析
最新发布
专注于数据库技术分享，包含但不限于Oracle，MySQL，PostgreSQL，ElasticSearch及国产数据库等
03-25 728
综上所述，Oracle数据库中的哈希连接通过构建和探查哈希表来实现表间的高效连接，特别是在处理大规模等值连接时表现出色。但效率受制于可用内存以及连接条件的复杂性。实际应用中，需要结合具体的环境选择合适的表连接方式。Oracle数据库中的哈希连接（Hash Join）是一种高效的表连接算法，尤其在处理大量数据时，相比嵌套循环连接或排序合并连接，它在某些情况下能更有效地执行连接操作。
Oracle表连接操作——Hash Join（哈希连接）
隔壁老王的博客
07-03 513
转自：http://space.itpub.net/17203031/viewspace-697442 在Oracle中，确定连接操作类型是执行计划生成的重要方面。各种连接操作类型代表着不同的连接操作算法，不同的连接操作类型也适应于不同的数据量和数据分布情况。 无论是Nest Loop Join（嵌套循环），还是Merge Sort Join（合并排序连接），都是适应于不同特殊...
Oracle Hint
hutianci的专栏
09-05 1050
Hint 是Oracle 提供的一种SQL语法，它允许用户在SQL语句中插入相关的语法，从而影响SQL的执行方式。 因为Hint的特殊作用，所以对于开发人员不应该在代码中使用它，Hint 更像是Oracle提供给DBA用来分析问题的工具 。在SQL代码中使用Hint，可能导致非常严重的后果，因为数据库的数据是变化的，在某一时刻使用这个执行计划是最优的，在另一个时刻，却可能很差，这也...
sql 查询强制使用HASH连接性能测试比较
weixin_34208283的博客
07-21 414
HASH JOIN 散列连接 hash join是CBO 做大数据集连接时的常用方式。优化器扫描小表（或数据源），利用连接键（也就是根据连接字段计算hash 值）在内存中建立hash表，然后扫描大表，每读到一条记录就来探测hash表一次，找出与hash表匹配的行。当小表可以全部放入内存中，其成本接近全表扫描两个表的成本之和。如果表很大不能完全放入内存，这时优化器会将它分割成若干不同的分区，不能放...
oracle hint用法
skyspark的专栏
07-26 896
常见的 从网上copy的1. /*+ALL_ROWS*/　　表明对语句块选择基于开销的优化方法,并获得最佳吞吐量,使资源消耗最小化.　　例如:　　SELECT /*+ALL+_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO=SCOTT; 　　2. /*+FIRST_ROWS*/　　表明对语句块选择基于开销的优化方法,并获得最佳响
Oracle数据仓库解决方案-1.ppt
11-22
在设计和管理方面，Oracle8i引入了一系列创新技术，如哈希连接（Hash Join）、位图索引（Bitmap Indexes）、并行优化器（Parallel-Aware Optimizer）等，提高了数据仓库的性能。此外，元数据管理、拖放可视建模方法...
ORACLE数据库SQL优化---表连接类型.docx
12-30
Oracle数据库的SQL优化是数据库管理员和开发人员关注的重要领域，特别是在处理复杂的表连接时。本文主要探讨了Oracle数据库中表连接类型的优化，包括连接顺序、连接方法和访问单表的方法。 1. 表连接顺序： Oracle...
Oracle中三种表连接算法的总结
09-10
Oracle数据库在处理表之间的连接操作时，提供了三种主要的连接算法：嵌套循环连接（Nested Loop Join）、排序合并连接（Sort Merge Join）和哈希连接（Hash Join）。这些算法各有特点，适用于不同的数据处理场景。 ...
Oracle 表三种连接方式使用介绍(sql优化)
09-10
Oracle数据库在处理表之间的连接操作时提供了三种主要的连接方式：Nested Loop Join、Hash Join以及Sort Merge Join。这些连接方式各有特点，适用于不同的情景，理解它们的工作原理和适用条件对于SQL查询优化至关...
Oracle答案-经典.docx
12-30
5. **哈希连接（HASH JOIN）**：Oracle使用哈希算法将一个表的数据加载到内存，然后对另一个表的数据进行哈希查找。适合大表之间的连接，尤其是两个表大小相当且没有索引的情况。 6. **归并连接（MERGE JOIN）**：...
oracle哈希连接举例,Oracle表联接之哈希连接
weixin_33050129的博客
04-06 1061
Oracle表连接之哈希连接Oracle Hash join 是一种非常高效的join 算法，主要以CPU(hash计算)和内存空间(创建hash table)为代价获得最大的效率。Hash join一般用于大表和小表之间的连接，我们将小表构建到内存中，称为Hash cluster，大表称为probe表。当两个表做hash join时，oracle会选择一个表作为驱动表，先根据过滤条件排除不必要的...
oracle哈希连接举例,Oracle的表连接方法（三）哈希连接
weixin_32120627的博客
04-06 393
Oracle表连接方法有四种：● 排序合并连接(Sort Merge Join)● 嵌套循环连接(Nested Loops Join)● 哈希连接(Hash Join)● 笛卡尔积(Cartesian Product)如果两个表对应的结果集很大，使用排序合并的话，排序操作成本较高；使用嵌套循环的话，则循环次数又很多，需要多次访问被驱动表的结果集；为了提高这种情况下表连接的效率，优化器提供了新的表连...
oracle优化之join,oracle性能优化之10046对Hash Join分析
weixin_39614546的博客
04-11 578
优化SQL的case，SQL语句如下，big_table为150G大小，small_table很小，9000多条记录，不到1M大小，hash_area_size, sort_area_size均设置足够大，可以进行optimal hash join和memory sort。123456select/*+ leading(b) use_hash(a b) */distincta.IDfrom...
【Oracle优化笔记】哈希连接（HASH JOIN)详解
badly9的专栏
03-13 3749
哈希连接（HASH JOIN)详细执行步骤如下所示： 1.Oracle根据HASH_AREA_SIZE、DB_BLOCK_SIZE和_HASH_MUTBLOCK_IO_COUNT的值来决定HASH Partition的数量（HASH PARTITION是将结果集分为若干个分区，每个分区有相同数量的bucket，bucket是hash映射的单位，而partition则是I/O单位）。Oracle会
oracle 强制走hash,求救啊！SQL走HASH JOIN ，却不走NESTED 导致执行速度很慢
weixin_28785509的博客
04-12 980
SQL 如下：sql: SELECT b.pallet_noFROM sajet.g_sn_status a, sajet.g_pack_pallet bWHERE a.model_id = :b2AND a.pallet_no = b.pallet_noAND b.terminal_id = :b1AND b.close_flag = 'N'AND ROWNUM = 1problem: a ta...
快速了解Oracle哈希连接
寒江的专栏
11-13 773
快速了解Oracle哈希连接 [导读]这里介绍要使Oracle哈希连接有效，需要设置HASH_JOIN_ENABLED=TRUE，缺省情况下该参数为TRUE，另外，不要忘了还要设置 hash_area_size参数。 　　在向大家详细介绍Oracle哈希连接之前，首先让大家了解下Oracle读取row source，然后全面介绍Oracle哈希连接，希望对大家有用。
oracle中hash join 哈希连接的原理和案例详解
06-11
Oracle中的Hash Join是一种常用的连接方式，它利用哈希表的方式来实现连接操作，可以提高连接速度，尤其是对于大数据量的连接操作。 Hash Join的原理是：将连接关系中的一张表（称为驱动表）的连接字段的值作为哈希...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值