`
lizhen3708693
  • 浏览: 76168 次
  • 性别: Icon_minigender_1
  • 来自: 苏州
社区版块
存档分类
最新评论

Oracle表连接操作——Hash Join(哈希连接)下

阅读更多

 


Hash
 Join是Oracle CBO时代经常出现的一种连接方式,对海量数据处理时经常出现在执行计划里。本篇的上篇
介绍了Hash Join的一些外部特征和操作算法流程,下面我们一起看下一些影响到Hash Join的重要参数和内部指标。

 

3Hash Join相关参数

 

Hash JoinCBO优化器才能生成的执行计划操作,如果是选择了RBO就不能生成包括Hash Join的执行计划。此外,与Hash Join相关的Oracle参数还包括下面几个:

 

ü       Hash_Join_Enable

 

该参数是控制CBO启用Hash Join的开关。如果设置为True,则表示CBO可以使用Hash Join连接方式,否则就不可以使用。在目前的版本中,该参数已经演化为一个隐含参数,名称为“_hash_join_enable”。

 

 

SQL> col name for a20;

SQL> col value for a10;

SQL> col DESCRIB for a30;

SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ

 2 FROM SYS.x$ksppi x, SYS.x$ksppcv y

 3 WHERE x.inst_id = USERENV ('Instance')

 4 AND y.inst_id = USERENV ('Instance')

 5 AND x.indx = y.indx

 6 AND x.ksppinm LIKE '%hash_join_enable%';

 

NAME                VALUE     DESCRIB

-------------------- ---------- ------------------------------

_hash_join_enabled  TRUE      enable/disable hash join

 

 

该参数的隐式化,也就说明了CBO已经成熟到一定程度,Oracle官方不希望我们禁用掉这种Hash Join连接方式。当然,我们可以从systemsession两层均可以暂时的禁用掉hash Join

 

//此时_hash_join_enable=true

SQL> explain plan for select * from segs, tabs where segs.segment_name=tabs.table_name;

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 2106473715

---------------------------------------------------------------------------

| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time    |

---------------------------------------------------------------------------

|  0 | SELECT STATEMENT  |     |  990 |  354K|   25  (4)| 00:00:01 |

|* 1 | HASH JOIN        |     |  990 |  354K|   25  (4)| 00:00:01 |

|  2 |  TABLE ACCESS FULL| TABS |  968 |  229K|   11  (0)| 00:00:01 |

|  3 |  TABLE ACCESS FULL| SEGS | 2267 |  274K|   13  (0)| 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

  1 - access("SEGS"."SEGMENT_NAME"="TABS"."TABLE_NAME")

15 rows selected

//session层面禁用hash_join连接

SQL>alter session set "_hash_join_enabled"=false;

Session altered

 

NAME                VALUE     DESCRIB

-------------------- ---------- ------------------------------

_hash_join_enabled  FALSE     enable/disable hash join

 

//相同的SQL,此时参数环境已经变化;

SQL> explain plan for select * from segs, tabs where segs.segment_name=tabs.table_name;

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------------------------------------

Plan hash value: 3475644097

 

------------------------------------------------------------------------------------

| Id | Operation          | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time    |

------------------------------------------------------------------------------------

|  0 | SELECT STATEMENT   |     |  990 |  354K|      |  144  (2)| 00:00:02 |

|  1 | MERGE JOIN        |     |  990 |  354K|      |  144  (2)| 00:00:02 |

|  2 |  SORT JOIN        |     |  968 |  229K|  712K|   65  (2)| 00:00:01 |

|  3 |   TABLE ACCESS FULL| TABS |  968 |  229K|      |   11  (0)| 00:00:01 |

|* 4 |  SORT JOIN        |     | 2267 |  274K|  824K|   79  (2)| 00:00:01 |

|  5 |   TABLE ACCESS FULL| SEGS | 2267 |  274K|      |   13  (0)| 00:00:01 |

------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

  4 - access("SEGS"."SEGMENT_NAME"="TABS"."TABLE_NAME")

      filter("SEGS"."SEGMENT_NAME"="TABS"."TABLE_NAME")

已选择18行。

 

可见,当我们session级别禁用了hash Join连接之后,CBO不能进行Hash Join路径选择。于是选择了Merge Join路径,显然无论是执行时间还是CPU成本,Merge Join略逊一筹。

 

ü       Hash_Area_Size

 

Hash Join操作是依赖独立的私有空间,我们称之为Hash_AreaHash AreaJoin过程中的作用就是将连接小表尽可能的缓存在Hash Area中,供进行Hash匹配和Bucket内部精确匹配。Hash Area是贮存在PGA中,属于会话session独立的一块空间。如果Hash Area较小,不足以存放小表全部数据,就会引起Temp表空间的使用,进而影响Hash Join性能。

 

SQL> show parameter hash

 

NAME                                TYPE       VALUE

------------------------------------ ----------- ------------------------------

hash_area_size                      integer    131072

 

 

因为每一个会话都会开启一个Hash Area进行Hash操作,所以通常Hash Area的大小不会设置很大。与Hash Area类似的空间是Sort Area,用于进行SQL语句中的Order by操作,也是一个依赖分配的参数项目。通常,Hash Area被分配大小为Sort Area的两倍。

 

 

SQL> show parameter sort_area

 

NAME                                TYPE       VALUE

------------------------------------ ----------- ------------------------------

sort_area_retained_size             integer    0

sort_area_size                      integer    65536

 

 

进入Oracle 9i之后,特别是10g出现,Oracle共享内存和独占内存分配策略呈现自动化和自适应化的趋势,而且这种技术也逐渐成熟。DBA只需要确定Oracle数据库总的内存使用大小(memory_target),就会根据算法、负载不断调整实现自适应的内存分区调整。

 

作为PGA分配,Oracle推出的自动调控参数是pga_aggregate_target,表示所有会话的PGA总分配大小。如果不启用PGA自动分配,该参数值就是设置为0

 

SQL> show parameter pga

 

NAME                                TYPE       VALUE

------------------------------------ ----------- ------------------------------

pga_aggregate_target                big integer 0

 

 

 

ü       Hash_multiblock_io_count

 

该参数表示在进行Hash Join连接操作的时候,一次可以读取的块个数。在最新的版本中,该参数已经变成了一个隐含参数。

 

SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ

 2 FROM SYS.x$ksppi x, SYS.x$ksppcv y

 3 WHERE x.inst_id = USERENV ('Instance')

 4 AND y.inst_id = USERENV ('Instance')

 5 AND x.indx = y.indx

 6 AND x.ksppinm LIKE '%hash_multiblock%';

 

NAME                          VALUE     DESCRIB

------------------------------ ---------- ------------------------------

_hash_multiblock_io_count     0         number of blocks hash join wil

                                         l read/write at once

 

 

这个参数可以追溯到Oracle 8i时代,当时设置的默认值为1。在以后的版本中,通常设置为0。这个参数对IO影响重大,不同的硬件环境、系统负载下效果不同。所以,当设置为0的时候,Oracle是会每次自动计算该值。

 

作为我们来讲,最好不要进行该参数的设置。

 

4、连接三模式

 

Hash Join比较Merge Sort Join一个比较优势的地方,就是对PGA空间的有限使用上。但是,使用PGA毕竟是一种风险操作。因为Hash AreaSort Area一样,在小表不能完全装入系统时,会调用Temp表空间的硬盘空间。这样,就会引起一些问题。

 

下面关于三种模式的阐述,借鉴八神前辈的《Oracle Hash Join》(http://www.alidba.net/index.php/archives/440)。特此表示感谢。

 

针对不同的状态,Oracle分别有不同的模式对应。

 

Optimal模式

 

这是我们进行Hash Join的最理想情况。驱动表(小表)生成的Hash数据集合可以完全存放在Hash Area的时候,我们称之为Optimal模式。

 

ü       首先找到驱动表,获取到驱动表。存放在Hash_Area中;

ü       Hash Area中,对驱动表进行Hash操作,形成Hash Bulket,形成对应的分区信息。针对多个Bulket,同时形成一个Bitmap列表,做到BulketBitmap位的联系;

ü       在各个Bulket中,分布着不同的数据行。如果连接列分布比较均匀,Bulket中数据也就比较均匀。如果Bulket中包括数据,对应该BulketBitmap位上为1,否则为0

ü       找被驱动表的每一列,将连接列值进行Hash处理。匹配Bitmap位,如果Bitmap0,表示该列值没有存在,直接抛弃。否则进入Bulket进行精确匹配;

 

 

Onepass模式

 

如果我们设置的PGA空间小,或者连接的小表体积就已经很大了,那么就会利用到临时表空间。具体处理,就是进行两次的Hash处理,在Bulket层面的上面建立Partition分区。

 

当进行Hash操作的时候,出现的情形是一部分的Partition在内存中,另一部分Partition被存放在Temp表空间上。

 

在进行连接匹配的时候,如果能够在Bitmap中确定到Partition在内存中,那么直接在内存中进行检索和精确匹配过程。否则从Temp表空间中将对应的Partition调取到内存中,进行匹配操作。

 

 

Multipass模式

 

这是一种很极端的情况,如果Hash Area小到一个Partition都装不下。当进行Hash操作后,只有半个Partition能装入到Hash Area

 

这种情况下,如果一个Partition匹配没有做到,还不能够放弃操作,要将剩下一半的Partition获取到进行Hash Join匹配。也就是一个Partition要经过两次的Bitmap匹配过程。

 

 

5、结论

 

Hash Join是一种效率很高,CBO时代很常见的连接方式。但是,相对于其他古典算法,Hash Join的综合效率很高,特别在海量数据时代。

 

分享到:
评论

相关推荐

    hash join 原理和算法

    如果某个分区的哈希表仍然过大,Oracle会退化为Nested-Loops Hash Join,逐个对剩余的分区构建哈希表并与之连接。 **二、Hash Join原理** 在实际操作中,Oracle使用哈希函数对连接键进行运算,将数据分到不同的...

    ORACLE表连接方式分析及常见用法

    5. 哈希连接(HASH JOIN):自Oracle 7.3引入,哈希连接在处理大数据量时表现优秀。它将一个表的全部数据哈希化,然后与另一个表的数据进行匹配。适合于内部表小,且可以全部装入内存的情况。 6. 索引连接(INDEX ...

    hash join算法原理

    如果分区后仍然有Hash Table无法完全放入内存,Oracle会采取Nested Loops Hash Join,即对部分Si构建Hash Table,逐个与所有Bi执行连接操作,直到所有Si完成连接。 2. Join阶段:对于每个分区,进行Hash Join操作。...

    Hash join算法原理

    Hash Join 算法是 Oracle 数据库中一种高效的连接操作方法,特别适用于处理大数据量的查询。自从 Oracle 7.3 版本开始引入,它主要用于处理相等连接,并且只在 Cost-Based Optimizer (CBO) 模式下运行。相比Nested ...

    Oracle中hash join研究.pdf

    哈希连接(Hash Join)是Oracle数据库中的一种高效连接方法,主要针对等值连接操作,其引入旨在解决嵌套循环连接(Nested Loop Join)中的大量随机读取问题以及排序合并连接(Sort-Merge Join)中的排序成本过高问题...

    oracle表的连接方式

    Oracle提供了三种主要的表连接方式:NESTED LOOP JOIN、HASH JOIN和SORT MERGE JOIN。每种方法都有其独特的应用场景和性能特点。 #### NESTED LOOP JOIN NESTED LOOP JOIN是一种简单的连接方式,适用于连接的数据...

    转--一次HASH JOIN 临时表空间不足的分析和优化思路

    Hash JOIN是通过在内存中创建一个或两个表的哈希索引来实现两个数据集的连接。它分为两个阶段:构建阶段和查找阶段。在构建阶段,一个表(称为build table)的数据被完全加载到内存中的哈希表中;在查找阶段,另一个...

    Oracle中表的连接及其调整.

    Oracle 数据库中的表连接是构建复杂 SQL 查询的基础,它允许从多个表中组合数据。连接方法的选择直接影响到查询性能,因此了解各种连接类型及其适用场景至关重要。本文将深入探讨 Oracle 中的三种主要连接方式:嵌套...

    Oracle CBO 学习笔记之(1) : 深入理解Oracle Hash Join的代价模型及其执行流程

    Hash Join的基本原理是通过构建一个哈希表来实现两个表的连接。首先,Oracle会选择一个较小的表(称为“build side”),将其所有数据加载到内存中的哈希表中。然后,对较大的表(称为“probe side”)的每一行,CBO...

    OracleHashJoin算法原理分享.pdf

    总的来说,Oracle的Hash Join算法通过哈希表和分区策略优化了大规模数据集的连接操作。它减少了对磁盘I/O的依赖,提高了处理大量数据时的性能。然而,正确估算内存需求和理解数据分布是确保其高效运行的关键。通过...

    Oracle数据库表连接笔记.doc

    Oracle数据库中的表连接是数据库查询中的核心操作,它允许从多个表中合并数据,以满足复杂的业务需求。Oracle数据库提供了多种连接方法,包括嵌套循环连接、排序合并连接、集群连接、笛卡尔连接和散列连接,以及特定...

    ORACLE数据库SQL优化---表连接类型.docx

    Oracle数据库的SQL优化是数据库管理员和开发人员关注的重要领域,特别是在处理复杂的表连接时。本文主要探讨了Oracle数据库中表连接类型的优化,包括连接顺序、连接方法和访问单表的方法。 1. 表连接顺序: Oracle...

    几种常用的表连接方式

    本文将深入探讨四种常用的表连接方式:嵌套循环连接、排列合并连接、哈希连接以及索引连接,并详细阐述每种连接方式的工作原理、适用场景及其优缺点。 #### 1. 嵌套循环连接(Nested Loop Join) 嵌套循环连接是最...

    hash_join.pdf

    这种方法可以显著提高大表连接的效率。 - **反连接**与**外连接**:反连接是指只返回不匹配的记录,而外连接则会返回左表或右表中的所有记录,即使它们没有对应的匹配项。 - **哈希连接原则**:哈希连接基于散列函数...

    Oracle中三种表连接算法的总结

    Oracle数据库在处理表之间的连接操作时,提供了三种主要的连接算法:嵌套循环连接(Nested Loop Join)、排序合并连接(Sort Merge Join)和哈希连接(Hash Join)。这些算法各有特点,适用于不同的数据处理场景。 ...

    Sql中的三种物理连接操作

    在SQL中,数据库管理系统在处理表间的连接时通常会使用三种物理连接操作:嵌套循环连接(Nested Loop Join)、合并连接(Merge Join)和哈希匹配(Hash Join)。理解这三种连接方式对于优化SQL查询性能至关重要。 ...

    关于Oracle的SQL性能调整.pdf

    表连接的方式多样,如哈希连接(Hash Join)、嵌套循环连接(Nested Loop Join)和排序合并连接(Sort Merge Join),每种连接方式有其特定的适用场景。DBA需根据实际情况进行优化选择,这个过程可能较为耗时,因为...

    oracle 优化资料

    Oracle数据库优化是提升数据库性能的关键环节,主要涉及SQL语句优化、表连接策略以及数据库配置等多个方面。以下是对这些知识点的详细解释: 1. **选用适合的Oracle优化器** Oracle提供三种优化器:基于规则(RULE)...

    oracle开发介绍

    **表连接方式**在数据库开发中极为重要,尤其是在Oracle环境下。以下是几种常见的表连接方式及其特点: 1. **嵌套循环连接(Nested Loop Join)** - **定义**:在嵌套循环连接中,Oracle会选择一张表作为驱动表...

    读懂oracle sql执行计划

    下一行显示了具体的连接操作——一个HASH JOIN。紧接着的两条分别表示了DEPT表和EMP表的访问方式,都是通过全表扫描的方式。在这个示例中,执行计划给出了查询各部门员工信息的最优化路径。 对于Oracle性能优化和...

Global site tag (gtag.js) - Google Analytics