`
trophy
  • 浏览: 178463 次
  • 性别: Icon_minigender_1
  • 来自: 济南
社区版块
存档分类
最新评论

HASH JOIN ,MERGE JOIN ,NESTED LOOP 比较

 
阅读更多

 

都是网上抄的,做一下总结

NESTED LOOP:

对于被连接的数据子集较小的情况,嵌套循环连接是个较好的选择。在嵌套循环中,内表被外表驱动,外表返回的每一行都要在内表中检索找到与它匹配的行,因此整个查询返回的结果集不能太大(大于万不适合),要把返回子集较小表的作为外表(CBO 默认外表是驱动表),而且在内表的连接字段上一定要有索引。当然也可以用ORDERED 提示来改变CBO默认的驱动表,使用USE_NL(table_name1 table_name2)可是强制CBO 执行嵌套循环连接。

 

HASH JOIN :

散列连接是CBO 做大数据集连接时的常用方式,优化器使用两个表中较小的表(或数据源)利用连接键在内存中建立散列表,然后扫描较大的表并探测散列表,找出与散列表匹配的行。

这种方式适用于较小的表完全可以放于内存中的情况,这样总成本就是访问两个表的成本之和。但是在表很大的情况下并不能完全放入内存,这时优化器会将它分割成若干不同的分区,不能放入内存的部分就把该分区写入磁盘的临时段,此时要有较大的临时段从而尽量提高I/O 的性能。

也可以用USE_HASH(table_name1 table_name2)提示来强制使用散列连接。如果使用散列连接HASH_AREA_SIZE 初始化参数必须足够的大,如果是9iOracle建议使用SQL工作区自动管理,设置WORKAREA_SIZE_POLICY AUTO,然后调整PGA_AGGREGATE_TARGET 即可。

 

 

MERGE JOIN排序合并连接

通常情况下散列连接的效果都比排序合并连接要好,然而如果行源已经被排过序,在执行排序合并连接时不需要再排序了,这时排序合并连接的性能会优于散列连接。可以使用USE_MERGE(table_name1 table_name2)来强制使用排序合并连接

 

几种方式的操作方式

merge join

merge join的操作通常分三步:

1、对连接的每个表做table access full;

2、对table access full的结果进行排序。

3、进行merge join对排序结果进行合并。

在全表扫描比索引范围扫描再通过rowid进行表访问更可取的情况下,merge join会比nested loops性能更佳。当表特别小或特别巨大的时候,实行全表访问可能会比索引范围扫描更有效。mrege join的性能开销几乎都在前两步。

 

hash join

对两个表进行全表扫描,然后oracle读取涉及连接的其中一个表,并且在内存里创建来自表的连接列的唯一关键字的位图。当读取和处理第二个表的行时,创建值的位图被用做过滤器。如果一个行成功的通过位图过滤,则hash算法用于数据查找和后来的连接。(这里涉及数学问题,我也弄的不是很清楚)。

以下条件下hash join可能有优势:

两个巨大的表之间的连接。

在一个巨大的表和一个小表之间的连接。

 

Nested Loops

会循环外表(驱动表),逐个比对和内表的连接是否符合条件。在驱动表比较小,内表比较大,而且内外表的连接列有索引的时候比较好。当SORT_AREA空间不足的时候,Oracle也会选择使用NL。基于CostOracle优化器(CBO)会自动选择较小的表做外表。

 

 

连接方式总结:

1))嵌套循环(nest loop):

          对于被连接的数据子集较小的情况,嵌套循环连接是较好的选择。在嵌套循环中,外表驱动内表,外表返回的每一行都要在内表中检索找到它匹配的行,因此整个查询返回的结果集不能太大(大于10000不合适),要把返回子集较小的表作为外表(驱动表),而且在内表的连接字段上一定要有索引。

2)哈希连接(hash join):

         哈希连接是大数据集连接时常用的方式,优化器使用两个表中较小的表,利用连接键在内存中建立散列表,然后扫描较大的表并探测散列表,找出与散列表匹配的行。

        这种方式适用于较小的表完全可以放入内存的情况,这样成本就是访问两个表的成本之和。但是在表很大的情况下并不能完全放入内存,这时优化器将它分割成若干不同的分区,不能放入内存的部分就把该分区写入磁盘的临时段。

        哈希连接只能应用于等值连接(WHERE A.COL3 = B.COL4)、非等值连接(WHERE A.COL3 > B.COL4)、外连接(WHERE A.COL3 = B.COL4(+))

3)排序合并连接(Sort Merge Join )

          通常情况下哈希连接的效果都比排序合并连接要好。然而如果行源已经被排过序,在执行排序合并连接时不需要再排序了,这时排序归并连接的性能会忧于哈希连接。

分享到:
评论

相关推荐

    hash join 原理和算法

    如果Hash Join的成本低于其他类型的Join操作,比如Nested Loop或Sort Merge Join,那么CBO会选择Hash Join。 总结来说,Hash Join是一种高效的连接操作方法,尤其适用于大型数据集。它通过构建哈希表和分区策略来...

    MySQL中Nested-Loop Join算法小结

    MySQL在5.7版本中仍然不支持Hash Join,只支持NLJ和Merge Join。在某些情况下,NLJ可以通过选择正确的驱动表(即JOIN顺序)来优化。MySQL会选择预计结果集最小的表作为驱动表,因为这通常会导致更少的循环次数和更高...

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

    1. **查询优化**:检查SQL语句,尝试使用其他类型的JOIN,如Nested Loop JOIN或Merge JOIN,它们可能对内存需求较小。同时,考虑是否可以通过添加索引、减少JOIN条件或使用子查询来降低数据量。 2. **内存调整**:...

    Oracle表连接方式

    根据不同的数据集和查询需求,Oracle提供了多种表连接方式,包括NESTED LOOP、HASH JOIN和SORT MERGE JOIN等。 NESTED LOOP NESTED LOOP是一种基本的表连接方式,适用于被连接的数据子集较小的情况。在nested loop...

    Sql中的三种物理连接操作

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

    数据库管理系统概述英文版课件:12 Join Algorithm.ppt

    包括简单嵌套循环Join(Simple-Nested Loop Join)、块嵌套循环Join(Block-Nested Loop Join)、排序合并Join(Sort-Merge Join)、索引嵌套循环Join(Index-Nested Loop Join)以及哈希Join(Hash Join)。...

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

    同时,理解何时Hash Join比其他类型的JOIN(如Nested Loop Join或Sort Merge Join)更适合,也是提升SQL性能的重要一环。 总之,Oracle CBO的Hash Join是数据库优化的关键技术之一,通过深入理解其代价模型和执行...

    oracle表的连接方式

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

    MySQL 8.0.18 Hash Join不支持left/right join左右连接问题

    这意味着,如果你的查询语句中包含了这些类型的连接,MySQL 将不会使用Hash Join,而是可能回退到其他连接策略,如Nested Loop 或 Merge Join。这对于依赖于这些连接类型的复杂查询来说,可能会带来性能上的影响。 ...

    Oracle中hash join研究.pdf

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

    chapter15 实践习题2

    该练习考察了不同类型的连接操作(Nested Loop Join, Block Nested Loop Join, Merge Join, Hash Join)在不同内存大小(M)条件下的磁盘访问次数。计算了每种连接方法所需的磁盘访问次数,并考虑了内外层关系的交换...

    关于Oracle优化常用概念解析.pptx

    主要有三种连接方式:Hash Join、Nested Loop和Merge Join。Nested Loop适合小表连接大表,当有高选择性索引时效率较高,但对大表连接或无合适索引的情况,效率会下降。Sort Merge适合处理不等值连接,需要额外的...

    Apache Flink 维表关联实战.pdf

    Streaming SQL Join 面向无界数据集的 SQL,无法缓存历史所有数据,因此像 Sort-Merge Join 需要对数据进行排序是无法做到的,Nested-loop Join 和 Hash Join 经过一定的改良则可以满足实时 SQL 的要求。 Flink 中...

    Oracle 表三种连接方式使用介绍(sql优化)

    Oracle数据库在处理表之间的连接操作时提供了三种主要的连接方式:Nested Loop Join、Hash Join以及Sort Merge Join。这些连接方式各有特点,适用于不同的情景,理解它们的工作原理和适用条件对于SQL查询优化至关...

    Oracle优化常用概念.pptx

    表连接主要有三种方式:Hash Join、Nested Loop和Sort Merge Join。Nested Loop适合有高选择性索引和限制性搜索的情况,但对大表和无索引的情况效率较低。Sort Merge Join在缺乏明确索引或进行不等值连接时较为有效...

    郝裕玮_18329015_hw101

    本问题主要讨论了四种不同的联接方式:Nested-loop join、Block nested-loop join、Merge join 和 Hash join,并且分析了它们在不同内存大小(M)下的性能表现,具体涉及的数据是关系r1需要800个block,关系r2需要...

    总体技术方案2.docx

    相比于Sort-Merge Join和Nested-Loop Join,Hash join通常更高效,尤其是在数据量大且内存充足的情况下。 这个总体技术方案旨在构建一个高效、可靠的大数据处理平台,通过优化的分布式数据管理和高性能查询技术,...

Global site tag (gtag.js) - Google Analytics