【sql调优之执行计划】merge semi join and merge anti join

最新推荐文章于 2022-10-23 16:15:43 发布
最新推荐文章于 2022-10-23 16:15:43 发布
阅读量412 收藏
点赞数
分类专栏： oracle 文章标签： SQL Access .net Oracle
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/longflang/article/details/83738417
版权

【sql调优之执行计划】merge semi join and merge anti join

Semi join （也有叫半连接的）多在子查询in或者exists等中使用，对于外部行集，查找内部（即子查询）行集，匹配第一行之后就返回，不再往下查找例如：

SQL> select b.*

 2   from scott.dept b

 3  where b.deptno in (select deptno from scott.emp a)

 4 ;

 

   DEPTNO DNAME         LOC

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

       10 ACCOUNTING    NEW YORK

       20 RESEARCH      DALLAS

       30 SALES         CHICAGO

 

 

Execution Plan

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

Plan hash value: 1090737117

 

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

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

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

|  0 | SELECT STATEMENT            |        |    3 |   69 |    6 (17)| 00:00:01 |

|  1 | MERGE JOIN SEMI            |        |    3 |   69 |    6 (17)| 00:00:01 |

|  2 |  TABLE ACCESS BY INDEX ROWID| DEPT   |    4 |   80 |    2  (0)| 00:00:01 |

|  3 |   INDEX FULL SCAN          | PK_DEPT |    4 |      |    1  (0)| 00:00:01 |

|* 4 |  SORT UNIQUE               |        |   14 |   42 |    4 (25)| 00:00:01 |

|  5 |   TABLE ACCESS FULL        | EMP    |   14 |   42 |    3  (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

  4 - access("B"."DEPTNO"="DEPTNO")

      filter("B"."DEPTNO"="DEPTNO")

 

 

Statistics

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

         0 recursive calls

         0 db block gets

         7 consistent gets

         0 physical reads

         0 redo size

       614 bytes sent via SQL*Net to client

       400 bytes received via SQL*Net from client

         2 SQL*Net roundtrips to/from client

         1 sorts (memory)

         0 sorts (disk)

         3 rows processed

 

SQL>

或者：

SQL> select b.*

 2   from scott.dept b

 3  where exists (select 1 from scott.emp a where a.deptno = b.deptno)

 4 ;

 

   DEPTNO DNAME         LOC

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

       10 ACCOUNTING    NEW YORK

       20 RESEARCH      DALLAS

       30 SALES         CHICAGO

 

 

Execution Plan

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

Plan hash value: 1090737117

 

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

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

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

|  0 | SELECT STATEMENT            |        |    3 |   69 |    6 (17)| 00:00:01 |

|  1 | MERGE JOIN SEMI            |        |    3 |   69 |    6 (17)| 00:00:01 |

|  2 |  TABLE ACCESS BY INDEX ROWID| DEPT   |    4 |   80 |    2  (0)| 00:00:01 |

|  3 |   INDEX FULL SCAN          | PK_DEPT |    4 |      |    1  (0)| 00:00:01 |

|* 4 |  SORT UNIQUE               |        |   14 |   42 |    4 (25)| 00:00:01 |

|  5 |   TABLE ACCESS FULL        | EMP    |   14 |   42 |    3  (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

  4 - access("A"."DEPTNO"="B"."DEPTNO")

      filter("A"."DEPTNO"="B"."DEPTNO")

 

 

Statistics

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

         1 recursive calls

         0 db block gets

         7 consistent gets

         0 physical reads

         0 redo size

       614 bytes sent via SQL*Net to client

       400 bytes received via SQL*Net from client

         2 SQL*Net roundtrips to/from client

         1 sorts (memory)

         0 sorts (disk)

         3 rows processed

 

SQL>

可以看到这种情况下，in和exsits的执行计划完全相同，且都使用了merge join semi的oporation

而not in或者not exists则不同，Oracle7.3版本之前not exists和not in还使用的tilter，merge anti join和hash anti join访问路径是后来增加的。

例子：

SQL> select b.*

 2   from scott.dept b

 3  where not exists (select 1 from scott.emp a where a.deptno = b.deptno)

 4 ;

 

   DEPTNO DNAME         LOC

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

       40 OPERATIONS    BOSTON

 

 

Execution Plan

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

Plan hash value: 1353548327

 

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

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

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

|  0 | SELECT STATEMENT            |        |    1 |   23 |    6 (17)| 00:00:01 |

|  1 | MERGE JOIN ANTI            |        |    1 |   23 |    6 (17)| 00:00:01 |

|  2 |  TABLE ACCESS BY INDEX ROWID| DEPT   |    4 |   80 |    2  (0)| 00:00:01 |

|  3 |   INDEX FULL SCAN          | PK_DEPT |    4 |      |    1  (0)| 00:00:01 |

|* 4 |  SORT UNIQUE               |        |   14 |   42 |    4 (25)| 00:00:01 |

|  5 |   TABLE ACCESS FULL        | EMP    |   14 |   42 |    3  (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

  4 - access("A"."DEPTNO"="B"."DEPTNO")

      filter("A"."DEPTNO"="B"."DEPTNO")

 

 

Statistics

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

         0 recursive calls

         0 db block gets

         5 consistent gets

         0 physical reads

         0 redo size

       535 bytes sent via SQL*Net to client

       400 bytes received via SQL*Net from client

         2 SQL*Net roundtrips to/from client

         1 sorts (memory)

         0 sorts (disk)

         1 rows processed

这里是merge join anti（也叫反连接），和semi相反，只有外部行在内部不能匹配的时候才返回。

而，not in则和not exsits不同，执行计划显示的是filter：

SQL> select b.*

 2   from scott.dept b

 3  where b.deptno not in (select deptno from scott.emp a)

 4 ;

 

   DEPTNO DNAME         LOC

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

       40 OPERATIONS    BOSTON

 

 

Execution Plan

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

Plan hash value: 3547749009

 

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

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

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

|  0 | SELECT STATEMENT  |     |    3 |   60 |    7  (0)| 00:00:01 |

|* 1 | FILTER           |     |      |      |           |         |

|  2 |  TABLE ACCESS FULL| DEPT |    4 |   80 |    3  (0)| 00:00:01 |

|* 3 |  TABLE ACCESS FULL| EMP |    2 |    6 |    2  (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

  1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "SCOTT"."EMP" "A" WHERE

             LNNVL("DEPTNO"<>:B1)))

  3 - filter(LNNVL("DEPTNO"<>:B1))

 

 

Statistics

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

         1 recursive calls

         0 db block gets

        19 consistent gets

         5 physical reads

         0 redo size

       535 bytes sent via SQL*Net to client

       400 bytes received via SQL*Net from client

         2 SQL*Net roundtrips to/from client

         0 sorts (memory)

         0 sorts (disk)

         1 rows processed

 

SQL>

我们知道not in与not exsits并不能等同，从执行计划上来看，not in的执行计划的operation是filter，而且内表和外表都是全表，没有使用索引，而从谓词信息中来看，operation 1为：

1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "SCOTT"."EMP" "A" WHERE

             LNNVL("DEPTNO"<>:B1)))

Null值对not in影响较大，如果稍稍修改一下这个查询，则又有不同了：

SQL> select b.*

 2   from scott.dept b

 3  where b.deptno not in (select nvl(deptno,0) from scott.emp a);

 

   DEPTNO DNAME         LOC

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

       40 OPERATIONS    BOSTON

 

 

Execution Plan

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

Plan hash value: 1353548327

 

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

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

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

|  0 | SELECT STATEMENT            |        |    1 |   23 |    6 (17)| 00:00:01 |

|  1 | MERGE JOIN ANTI            |        |    1 |   23 |    6 (17)| 00:00:01 |

|  2 |  TABLE ACCESS BY INDEX ROWID| DEPT   |    4 |   80 |    2  (0)| 00:00:01 |

|  3 |   INDEX FULL SCAN          | PK_DEPT |    4 |      |    1  (0)| 00:00:01 |

|* 4 |  SORT UNIQUE               |        |   14 |   42 |    4 (25)| 00:00:01 |

|  5 |   TABLE ACCESS FULL        | EMP    |   14 |   42 |    3  (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

  4 - access("B"."DEPTNO"=NVL("DEPTNO",0))

      filter("B"."DEPTNO"=NVL("DEPTNO",0))

 

 

Statistics

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

         1 recursive calls

         0 db block gets

         5 consistent gets

         0 physical reads

         0 redo size

       535 bytes sent via SQL*Net to client

       400 bytes received via SQL*Net from client

         2 SQL*Net roundtrips to/from client

         1 sorts (memory)

         0 sorts (disk)

         1 rows processed

 

SQL>

奇怪的是，这里使用了索引，因为告诉了oracle不会有null值，而且谓词信息也发生了改变：

4 - access("B"."DEPTNO"=NVL("DEPTNO",0))

这也是使用not in需要注意的地方。

 

转自：http://space.itpub.net/16179598/viewspace-671211

longflang
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
源码解读：semi join 如何避免拉取大表数据？（一）
阿里云云栖号
02-27 541
PolarDB-X是面向HTAP设计的分布式数据库，在复杂查询时也会重点考虑利用数据库的索引信息来提升join的查询效率，因此有了本文的semi bka join。
merge semi join and merge anti join
Laughing
05-04 766
版本：10.2.0.4 Semi join（也有叫半连接的）多在子查询in或者exists等中使用，对于外部行集，查找内部（即子查询）行集，匹配第一行之后就返回，不再往下查找例如： SQL> select b.* 2 from scott.dept b 3 where b.deptno in (select deptno from scott.emp a) 4 ; DEPTN
semi join and anti join
ck0210的博客
11-22 82
http://www.dbafree.net/?p=224 ...
ORACLE执行计划之【MERGE JOIN ANTI】与【MERGE JOIN SEMI】
QianXun
03-05 1483
简要说明执行计划两者区别： 1.SQL&gt; ALTER TABLE DEPT ADD CONSTRAINTS PK_DEPT PRIMARY KEY (deptno); 2.执行两次SQL 如下图所示： 3.结果：同样的SQL，因为in与not in 执行计划走了不同的链接方式， merge join semi（也叫组合排序半连接），在外部行集，查找内部（即子查询）行集，...
详解PANDAS 数据合并与重塑（join/merge篇）
01-01
在这里，将接着介绍pandas中也常常用到的join 和merge方法 merge pandas的merge方法提供了一种类似于SQL的内存链接操作，官网文档提到它的性能会比其他开源语言的数据操作（例如R）要高效。 和SQL语句的对比可以看...
DataFrame 数据合并实现(merge，join，concat)
01-21
merge merge 函数通过一个或多个键将数据集的行连接起来。 场景：针对同一个主键存在的两张包含不同特征的表，通过主键的链接，将两张表进行合并。合并之后，两张表的行数不增加，列数是两张表的列数之和。 def ...
SQL中Merge用法详解
12-15
MERGE语句是SQL语句的一种。在SQL Server、Oracle数据库中可用，MySQL、PostgreSQL中不可用。MERGE是Oracle9i新增的语法，用来合并UPDATE和INSERT语句。通过MERGE语句，根据一张表（原数据表，source table）或子...
2013-04-16由MERGE JOIN SEMI引发的性能问题
关注系统性能调优
04-17 1258
检查16号上午8:00-12:00的数据库报告，发现一条SQL语句执行时间为1977s，逻辑读为103,491,288。 Elapsed Time (s) CPU Time (s) Executions Elap per Exec (s) % Total DB Time SQL Id SQL Module SQL Text 21,756 16,342
连接查询你一定不陌生，但你知道还有半连接和反连接吗？
Tony.Dong的专栏
08-21 5595
半连接（Semi Join）返回左表中与右表至少匹配一次的数据行，通常体现为 EXISTS 或者 IN 子查询，反连接（Anti Join）返回左表中与右表不匹配的数据行，通常体现为 NOT EXISTS 或者 NOT IN 子查询。
认识优化查询中的Merge Join、Nested Loops和Hash Match
热门推荐
关注业务、关注技术、关注运维
04-05 2万+
1.基本概念:     Merge Join([排序]合并联接)、Nested Loops(嵌套循环联接)、Hash Match都是物理运算符。       Merge Join常执行Inner Join(内部联接)、Left Outer Join(左外部联接)、Left Semi Join(左半部联接)、Left Anti Semi Join(左反半部联接)、Right Outer Joi
Merge join、Hash join、Nested loop join对比分析
开平人生
11-16 1万+
SQL server 内部实现了三种类型的内连接运算，大多数人从来没有听说过这些连接类型，因为它们不是逻辑连接也很少被用于代码中。那么它们什么时候会被用到呢？答案是要依情况而定。这就意味着要依赖于记录集和索引。查询优化器总是智能的选择最优的物理连接类型。我们知道SQL优化器创建一个计划开销是基于查询开销的，并依据此来选择最佳连接类型。         那查询优化器究竟是怎样从内部选择连接类型的呢
值得反复研读的表连接之ANTI JOIN方式
u011868279的博客
10-23 1091
值得反复研读的表连接之ANTI JOIN方式
多表连接的三种方式详解 hash join、merge join、 nested loop
weixin_33836223的博客
04-30 2128
在多表联合查询的时候，如果我们查看它的执行计划，就会发现里面有多表之间的连接方式。多表之间的连接有三种方式：Nested Loops，Hash Join 和 Sort Merge Join.具体适用哪种类型的连接取决于 当前的优化器模式 （ALL_ROWS 和 RULE） 取决于表大小 取决于连接列是否有索引 取决于连接列是否排序 下面来介绍三种不同连接工作方式的不同： 实验s...
12 排序合并连接(SORT MERGE JOIN)--优化主题系列
leo0805的博客
10-09 9334
排序合并连接(SORT MERGE JOIN) 前文提到嵌套循环以及哈希连接，他们都有驱动表概念。排序合并连接没有驱动表一说，两个表/行源是对等关系。排序合并连接原理是先对两个表/行源根据JOIN列进行排序(当然了排序的时候要踢出不符合where条件的列)，然后再进行连接。排序合并连接可以处理非等值JOIN。有时候，出现了非等值JOIN，还非得走SORT MERGE JOIN不可。根据排序合并的原理，我们知道排序合并连接其实很耗费资源，因为要对2个表/结果集进行排序，所以一般情况下，CBO是不会选择走S
spark sql底层join实现，broadcast hash join，shuffle hash join，sort merge join
最新发布
06-28
### 回答1： Spark SQL底层join实现有三种方式：broadcast hash join、shuffle hash join和sort merge join。其中broadcast hash join适用于小数据量的join操作，可以将一个小的表复制到所有的Executor上，然后和其他的表进行join操作；shuffle hash join适用于大数据量的join操作，可以通过Hash函数将数据分区，然后通过网络进行数据交换，再将分区的数据进行join操作；sort merge join适用于两个表都有序的情况下进行join操作，可以将两个表按照join字段进行排序，然后按顺序进行join操作。 ### 回答2： Spark SQL是Apache Spark的一个组件，提供了一个基于SQL的编程接口，支持分布式数据处理。其底层实现了三种Join操作，分别是Broadcast Hash Join、Shuffle Hash Join和Sort Merge Join。 1.Broadcast Hash Join是在一个表比较小的情况下使用的Join算法。具体流程是，将小表广播给集群中的每个Executor，然后对大表进行Join操作。该算法需要把小表数据拷贝到内存中，可能会导致OOM异常，因此需要在实际使用中谨慎选择。 2.Shuffle Hash Join适用于两个表都比较大的情况下。具体流程是，在两个表都进行Shuffle操作，将Join Key相同的数据放到同一个分区。然后将每个分区的数据交给一个Executor进行Join操作。该算法的缺点是Shuffle会增加网络开销以及I/O操作的负担，因此需要注意调整参数大小。 3.Sort Merge Join适用于两个表都比较大且Join Key有序的情况下。该算法的流程是，在两个表进行Sort操作，将Join Key相同的数据放到同一个分区。然后将每个分区的数据交给一个Executor进行Join操作。该算法的优点是Join Key有序，不需要进行Shuffle操作，因此可以避免Shuffle操作的网络损耗和I/O操作的负担。 综上所述，Spark SQL底层Join的实现使用了三种Join算法，Broadcast Hash Join适用于小表Join，Shuffle Hash Join适用于两个表都比较大的情况下，Sort Merge Join适用于两个表都比较大且Join Key有序的情况下。我们在使用时需要根据实际情况选择合适的Join算法，避免OOM和网络开销等问题。 ### 回答3： Spark SQL是一种针对结构化和半结构化数据处理的高性能分布式计算框架。在使用Spark SQL进行数据处理时，很多情况下需要对数据进行join操作。Spark SQL的join操作有三种实现方式，分别是Broadcast Hash Join、Shuffle Hash Join和Sort Merge Join。 Broadcast Hash Join是一种在内存中进行的join操作，当一个表的大小可以运用内存进行分布并广播到所有节点时，可以采用Broadcast Hash Join。这种join的实现方式是先在driver端对较小的表进行哈希操作，然后将其哈希表广播到所有worker节点上，同时另外一个较大的表再进行哈希操作，将其切分成多个小表，然后将每个小表发到worker上去跟广播的哈希表进行join，最终将所有小表的join结果汇总即可。Broadcast Hash Join的优点是可以减少数据的运输，缩短查询时间。缺点是只能适用于对于较小表以及对于等值join场景，而且如果数据量过大，广播查询也会耗费大量的网络资源，无法解决内存不足的问题。 Shuffle Hash Join是一种使用网络进行数据传输的join操作方式。当一个表的大小无法运用内存进行分布并广播到所有节点时，可以采用Shuffle Hash Join。它的实现方式是将两张表的数据都进行哈希分区，将相同哈希值的分区数据放到同一个节点上，然后在每个节点进行join操作。不同节点之间进行数据交换，需要通过Shuffle进行数据传送。Shuffle Hash Join适用于较大的表，可以支持任何join，但效率较低，因为需要网络传输。 Sort Merge Join是一种对两个表进行排序后再进行join操作的方式。Sort Merge Join的实现方式是对两张表按照join key 进行排序，然后进行合并操作。当然这个过程支持Inner、Full、Left、Right的多种Join操作。Sort Merge Join的优点是适用于超大表的join操作，缺点是需要对两张表进行排序操作，代价较高，且仅适用于等值join，而且排序操作必须保证内存能够承受。 在Spark SQL的JOIN操作中，Broadcast Hash Join适用于大表关联小表的情况；Shuffle Hash Join是对大表关联大表，或者把数据分散在集群节点上的表进行JOIN操作的时候的方法；Sort Merge Join通常用于数据量较大而无法全部载入内存的情况下进行JOIN操作。不同的JOIN操作应根据数据量以及具体的情况来选择。

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值