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

最新推荐文章于 2021-04-11 03:50:13 发布
最新推荐文章于 2021-04-11 03:50:13 发布
阅读量442 收藏
点赞数
分类专栏： sql 优化--转载

http://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。


hackshi008
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
解读 Oracle12c 执行计划——HASH JOIN
梅森上校的博客 业精于勤荒于嬉，形成于思毁于随。
09-24 3164
解读 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）核心原理解析
最新发布
专注于数据库技术分享，包含但不限于Oracle，MySQL，PostgreSQL，ElasticSearch及国产数据库等
03-25 792
综上所述，Oracle数据库中的哈希连接通过构建和探查哈希表来实现表间的高效连接，特别是在处理大规模等值连接时表现出色。但效率受制于可用内存以及连接条件的复杂性。实际应用中，需要结合具体的环境选择合适的表连接方式。Oracle数据库中的哈希连接（Hash Join）是一种高效的表连接算法，尤其在处理大量数据时，相比嵌套循环连接或排序合并连接，它在某些情况下能更有效地执行连接操作。
Oracle Hint
hutianci的专栏
09-05 1058
Hint 是Oracle 提供的一种SQL语法，它允许用户在SQL语句中插入相关的语法，从而影响SQL的执行方式。 因为Hint的特殊作用，所以对于开发人员不应该在代码中使用它，Hint 更像是Oracle提供给DBA用来分析问题的工具 。在SQL代码中使用Hint，可能导致非常严重的后果，因为数据库的数据是变化的，在某一时刻使用这个执行计划是最优的，在另一个时刻，却可能很差，这也...
sql 查询强制使用HASH连接性能测试比较
weixin_34208283的博客
07-21 417
HASH JOIN 散列连接 hash join是CBO 做大数据集连接时的常用方式。优化器扫描小表（或数据源），利用连接键（也就是根据连接字段计算hash 值）在内存中建立hash表，然后扫描大表，每读到一条记录就来探测hash表一次，找出与hash表匹配的行。当小表可以全部放入内存中，其成本接近全表扫描两个表的成本之和。如果表很大不能完全放入内存，这时优化器会将它分割成若干不同的分区，不能放...
Oracle数据仓库解决方案-1.ppt
11-22
在设计和管理方面，Oracle8i引入了一系列创新技术，如哈希连接（Hash Join）、位图索引（Bitmap Indexes）、并行优化器（Parallel-Aware Optimizer）等，提高了数据仓库的性能。此外，元数据管理、拖放可视建模方法...
oracle报错（ORA-00600）问题处理
09-10
例如，如果错误是由于哈希连接（Hash Join）导致的，可以使用以下命令临时禁用哈希连接： ```sql ALTER SESSION SET "_hash_join_enabled"=false; ``` 类似地，如果错误是由于哈希分组（Hash Group By）造成的，...
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+面试及答案-经典.docx
12-08
5. **哈希连接（HASH JOIN）**：当两个输入集相对较小且内存足够时，Oracle会使用哈希连接。它将一个表的数据哈希化，然后用另一个表的数据进行匹配。 6. **归并连接（MERGE JOIN）**：适用于两个已排序的输入，...
Oracle数据库表连接笔记.doc
06-03
Oracle数据库中的表连接是数据库查询中的核心操作，它允许从多个表中合并数据，以满足复杂的业务需求。Oracle数据库提供了多种连接方法，包括嵌套循环连接、排序合并连接、集群连接、笛卡尔连接和散列连接，以及特定...
转--一次HASH JOIN 临时表空间不足的分析和优化思路
04-05
Hash JOIN是通过在内存中创建一个或两个表的哈希索引来实现两个数据集的连接。它分为两个阶段：构建阶段和查找阶段。在构建阶段，一个表（称为build table）的数据被完全加载到内存中的哈希表中；在查找阶段，另一个...
ORACLE表连接方式分析及常见用法
11-17
5. 哈希连接（HASH JOIN）：自Oracle 7.3引入，哈希连接在处理大数据量时表现优秀。它将一个表的全部数据哈希化，然后与另一个表的数据进行匹配。适合于内部表小，且可以全部装入内存的情况。 6. 索引连接（INDEX ...
oracle hint用法
skyspark的专栏
07-26 898
常见的 从网上copy的1. /*+ALL_ROWS*/　　表明对语句块选择基于开销的优化方法,并获得最佳吞吐量,使资源消耗最小化.　　例如:　　SELECT /*+ALL+_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO=SCOTT; 　　2. /*+FIRST_ROWS*/　　表明对语句块选择基于开销的优化方法,并获得最佳响
oracle哈希连接举例,Oracle表联接之哈希连接
weixin_33050129的博客
04-06 1064
Oracle表连接之哈希连接Oracle Hash join 是一种非常高效的join 算法，主要以CPU(hash计算)和内存空间(创建hash table)为代价获得最大的效率。Hash join一般用于大表和小表之间的连接，我们将小表构建到内存中，称为Hash cluster，大表称为probe表。当两个表做hash join时，oracle会选择一个表作为驱动表，先根据过滤条件排除不必要的...
oracle哈希连接举例,Oracle的表连接方法（三）哈希连接
weixin_32120627的博客
04-06 396
Oracle表连接方法有四种：● 排序合并连接(Sort Merge Join)● 嵌套循环连接(Nested Loops Join)● 哈希连接(Hash Join)● 笛卡尔积(Cartesian Product)如果两个表对应的结果集很大，使用排序合并的话，排序操作成本较高；使用嵌套循环的话，则循环次数又很多，需要多次访问被驱动表的结果集；为了提高这种情况下表连接的效率，优化器提供了新的表连...
oracle优化之join,oracle性能优化之10046对Hash Join分析
weixin_39614546的博客
04-11 585
优化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 哈希连接的原理和案例详解
06-11
Oracle中的Hash Join是一种常用的连接方式，它利用哈希表的方式来实现连接操作，可以提高连接速度，尤其是对于大数据量的连接操作。 Hash Join的原理是：将连接关系中的一张表（称为驱动表）的连接字段的值作为哈希...

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值