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

最新推荐文章于 2024-04-01 09:07:54 发布
最新推荐文章于 2024-04-01 09:07:54 发布
阅读量104 收藏
点赞数
文章标签： 数据库

版本：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  ;

 

    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需要注意的地方。

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16179598/viewspace-671211/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16179598/viewspace-671211/

cixlv48826
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
sql调优之执行计划之笛卡尔连接————MERGE JOIN CARTESIAN
yuzhenhuan01的专栏
08-29 1万+
生产环境中遭遇temp空间不足的报错 定位产生异常的语句如下：select * from (select t.*, rownum rn from (select p.video_id, c.path, c.hosts, t.sha1 from converttype c left join tasks...
merge semi join and merge anti join
Laughing
05-04 767
版本：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
ORACLE执行计划之【MERGE JOIN ANTI】与【MERGE JOIN SEMI】
QianXun
03-05 1486
简要说明执行计划两者区别： 1.SQL&gt; ALTER TABLE DEPT ADD CONSTRAINTS PK_DEPT PRIMARY KEY (deptno); 2.执行两次SQL 如下图所示： 3.结果：同样的SQL，因为in与not in 执行计划走了不同的链接方式， merge join semi（也叫组合排序半连接），在外部行集，查找内部（即子查询）行集，...
SQL Server的三种物理连接之Merge join（二）
zhengyuepo的专栏
11-11 487
SQL Server的三种物理连接之Merge join（二）
详解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）或子...
Merge, Join, Concat
浮汐潇潇
10-09 248
Merge 首先merge的操作非常类似sql里面的join，实现将两个Dataframe根据一些共有的列连接起来，当然，在实际场景中，这些共有列一般是Id，可以选择inner(默认)，left,right,outer 这几种模式，分别对应的是内连接，左连接，右连接 import numpy as np import pandas as pd dframe1 = pd.DataFrame({'...
ob优化之：Merge Join
最新发布
viviliving的专栏
04-01 341
Merge Join 其实是 Nested Loops Joins 的一种变种。如果连接的两个数据集尚未排序，则数据库会对它们进行排序。对于第一个数据集中的每一行，数据库会探测第二个数据集中的匹配行并将它们连接起来，其起始位置基于上一次迭代中的匹配。这一点与 Nested Loops Joins 不同，Nested Loops Joins 遍历第二个数据集时每次都是从第一行数据开始。Merge Join 的执行伪代码如下所示。
2013-04-16由MERGE JOIN SEMI引发的性能问题
关注系统性能调优
04-17 1262
检查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
认识优化查询中的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优化器创建一个计划开销是基于查询开销的，并依据此来选择最佳连接类型。         那查询优化器究竟是怎样从内部选择连接类型的呢
关于semi-join/anti-join的一点探讨
congdiku5262的博客
07-27 1043
create table dept (deptno number(5), dname varchar2(20));create table emp (deptno number(5), name varchar2(20));in...
多表连接的三种方式详解 hash join、merge join、 nested loop
weixin_33836223的博客
04-30 2131
在多表联合查询的时候，如果我们查看它的执行计划，就会发现里面有多表之间的连接方式。多表之间的连接有三种方式：Nested Loops，Hash Join 和 Sort Merge Join.具体适用哪种类型的连接取决于 当前的优化器模式 （ALL_ROWS 和 RULE） 取决于表大小 取决于连接列是否有索引 取决于连接列是否排序 下面来介绍三种不同连接工作方式的不同： 实验s...
16 反连接(anti-join)--优化主题系列
leo0805的博客
10-11 1万+
反连接(anti-join) 反连接其实是特殊的半连接。只是把in/exists换成了not in/not exists 执行计划中，看到有 NESTED LOOPS ANTI/HASH JOIN ANTI 就表示有反连接
执行计划
Th record of huo.
02-27 658
oracle用来执行目标sql语句的这些步骤的组合就被成为执行计划查看执行计划:explain plan for dbms_xplansqlplus中autotrace的开关10046事件10053事件AWR报告一些个性化的脚本在oracle数据库中判断执行计划是否准确，就是看目标sql是否真正被执行过，真正执行过的sql所对因的执行计划就是准的，泛指有可能不准。查看执行计划的顺序：可以根据tom...
Teradata SQL调优：应对Skew与MPP架构策略
2. **性能调优过程**：包括对SQL查询的分析，如使用Explain Plan来评估查询的执行计划，关注信心水平、行估计、物理I/O访问、全文搜索等细节。此外，还会考虑如何通过调整查询语句或使用DBQL（Database Query ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值