半联结&反联结！

最新推荐文章于 2021-07-26 22:32:50 发布
最新推荐文章于 2021-07-26 22:32:50 发布
阅读量1k 收藏
点赞数
CC 4.0 BY-SA版权
分类专栏： execution plan
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/zq9017197/article/details/8439353

半联结是在两个数据集（表）之间的联结，其中第一个数据集中的数据行在决定是否返回时会根据在另一个数据集中出现或不出现至少一个相匹配的数据行来确定。“不出先”匹配行——这是半联结的一种特殊形式，称为反联结。

标准的内联结与半联结之间最主要的区别在于在半联结中，第1个数据集中的每一条记录至返回一次，而不管在第二个数据集中有几条匹配的数据。这个定义表明这个查询的实际处理过程可以通过在找到第1个匹配以后马上停止第二个查询来进行优化。

从本质上来说，这就是半联结：可以通过在查询2执行完成之前停止处理该查询来进行优化。这种联结技术在oracle基于成本的优化器中查询中又包含在IN或EXISTS句中的子查询时是一种可选方案。

1、半联结

/*+ SEMIJOIN */  进行半联结（优化器选用使用哪种类型）。
/*+ NO_SEMIJOIN */  显示的意味着不行进半联结。

SQL> select * from emp order by job;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
      7788 SCOTT      ANALYST         7566 19-4月 -87           3000                    20
      7902 FORD       ANALYST         7566 03-12月-81           3000                    20
      7934 MILLER     CLERK           7782 23-1月 -82           1300                    10
      7900 JAMES      CLERK           7698 03-12月-81            950                    30
      7369 SMITH      CLERK           7902 17-12月-80            800                    20
      7876 ADAMS      CLERK           7788 23-5月 -87           1100                    20
      7698 BLAKE      MANAGER         7839 01-5月 -81           2850                    30
      7566 JONES      MANAGER         7839 02-4月 -81           2975                    20
      7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10
      7839 KING       PRESIDENT            17-11月-81           5000                    10
      7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0         30

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
      7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30
      7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30
      7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30

已选择14行。

分析上面的数据可以看出JOB='SALESMAN'的DEPTNO有4条重复的记录，值都是30。那么如下的子查询返回的记录就有重复的记录！

select e.ename, e.job, e.sal
  from emp e
 where e.deptno in (select d.deptno from emp d where d.job = 'SALESMAN');

select e.ename, e.job, e.sal
  from emp e
 where exists (select null
          from emp d
         where e.deptno = d.deptno
           and d.job = 'SALESMAN');

看执行计划：

SQL> select e.ename, e.job, e.sal
  2    from emp e
  3   where e.deptno in (select d.deptno from emp d where d.job = 'SALESMAN');

ENAME      JOB              SAL
---------- --------- ----------
JAMES      CLERK            950
TURNER     SALESMAN        1500
BLAKE      MANAGER         2850
MARTIN     SALESMAN        1250
WARD       SALESMAN        1250
ALLEN      SALESMAN        1600

已选择6行。


执行计划
----------------------------------------------------------
Plan hash value: 977554918

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |   448 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN SEMI    |      |    14 |   448 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   294 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMP  |     3 |    33 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("E"."DEPTNO"="D"."DEPTNO")
   3 - filter("D"."JOB"='SALESMAN')

SQL> 
SQL> 
SQL> select e.ename, e.job, e.sal
  2    from emp e
  3   where exists (select null
  4            from emp d
  5           where e.deptno = d.deptno
  6             and d.job = 'SALESMAN');

ENAME      JOB              SAL
---------- --------- ----------
JAMES      CLERK            950
TURNER     SALESMAN        1500
BLAKE      MANAGER         2850
MARTIN     SALESMAN        1250
WARD       SALESMAN        1250
ALLEN      SALESMAN        1600

已选择6行。


执行计划
----------------------------------------------------------
Plan hash value: 977554918

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |   448 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN SEMI    |      |    14 |   448 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   294 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMP  |     3 |    33 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("E"."DEPTNO"="D"."DEPTNO")
   3 - filter("D"."JOB"='SALESMAN')

使用IN和EXISTS的执行计划是一致的，这一点就为了消除长期以来人们认为的使用EXISTS查询与使用IN查询的处理方法很不相同这种观点。注意执行计划中的“SEMI”就代表使用了半联结！

SQL> select e.ename, e.job, e.sal
  2    from emp e
  3   where e.deptno in (select /*+ no_semijoin */
  4                       d.deptno
  5                        from emp d
  6                       where d.job = 'SALESMAN');

ENAME      JOB              SAL
---------- --------- ----------
ALLEN      SALESMAN        1600
WARD       SALESMAN        1250
MARTIN     SALESMAN        1250
BLAKE      MANAGER         2850
TURNER     SALESMAN        1500
JAMES      CLERK            950

已选择6行。


执行计划
----------------------------------------------------------
Plan hash value: 2389097100

---------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |     9 |   306 |     8  (25)| 00:00:01 |
|*  1 |  HASH JOIN           |          |     9 |   306 |     8  (25)| 00:00:01 |
|   2 |   VIEW               | VW_NSO_1 |     2 |    26 |     4  (25)| 00:00:01 |
|   3 |    HASH UNIQUE       |          |     2 |    22 |     4  (25)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL| EMP      |     3 |    33 |     3   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL  | EMP      |    14 |   294 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("E"."DEPTNO"="DEPTNO")
   4 - filter("D"."JOB"='SALESMAN')

SQL> 
SQL> 
SQL> select e.ename, e.job, e.sal
  2    from emp e
  3   where exists (select /*+ no_semijoin */
  4           null
  5            from emp d
  6           where e.deptno = d.deptno
  7             and d.job = 'SALESMAN');

ENAME      JOB              SAL
---------- --------- ----------
ALLEN      SALESMAN        1600
WARD       SALESMAN        1250
MARTIN     SALESMAN        1250
BLAKE      MANAGER         2850
TURNER     SALESMAN        1500
JAMES      CLERK            950

已选择6行。


执行计划
----------------------------------------------------------
Plan hash value: 2561671593

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     5 |   105 |    12   (0)| 00:00:01 |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   294 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMP  |     1 |    11 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter( EXISTS (SELECT 0 FROM "EMP" "D" WHERE
              "D"."JOB"='SALESMAN' AND "D"."DEPTNO"=:B1))
   3 - filter("D"."JOB"='SALESMAN' AND "D"."DEPTNO"=:B1)

使用NO_SEMIJOIN提示关闭了优化器使用半联结的能力。可以看见使用使用IN和EXISTS的执行计划还不一样了。

2、反联结

/*+ ANTIJOIN */  进行反联接（优化器决定具体类型）
/*+ USE_ANTI */  ANTIJOIN提示的旧版本

SQL> select e.ename, e.job, e.sal
  2    from emp e
  3   where e.deptno not in
  4         (select d.deptno from emp d where d.job = 'PRESIDENT');

ENAME      JOB              SAL
---------- --------- ----------
JAMES      CLERK            950
TURNER     SALESMAN        1500
BLAKE      MANAGER         2850
MARTIN     SALESMAN        1250
WARD       SALESMAN        1250
ALLEN      SALESMAN        1600
FORD       ANALYST         3000
ADAMS      CLERK           1100
SCOTT      ANALYST         3000
JONES      MANAGER         2975
SMITH      CLERK            800

已选择11行。


执行计划
----------------------------------------------------------
Plan hash value: 4002838083

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     5 |   160 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN ANTI NA |      |     5 |   160 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   294 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMP  |     3 |    33 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("E"."DEPTNO"="D"."DEPTNO")
   3 - filter("D"."JOB"='PRESIDENT')

SQL> 
SQL> 
SQL> select e.ename, e.job, e.sal
  2    from emp e
  3   where not exists (select null
  4            from emp d
  5           where e.deptno = d.deptno
  6             and d.job = 'PRESIDENT');

ENAME      JOB              SAL
---------- --------- ----------
JAMES      CLERK            950
TURNER     SALESMAN        1500
BLAKE      MANAGER         2850
MARTIN     SALESMAN        1250
WARD       SALESMAN        1250
ALLEN      SALESMAN        1600
FORD       ANALYST         3000
ADAMS      CLERK           1100
SCOTT      ANALYST         3000
JONES      MANAGER         2975
SMITH      CLERK            800

已选择11行。


执行计划
----------------------------------------------------------
Plan hash value: 3353202012

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     5 |   160 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN ANTI    |      |     5 |   160 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   294 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMP  |     3 |    33 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("E"."DEPTNO"="D"."DEPTNO")
   3 - filter("D"."JOB"='PRESIDENT')

执行计划中的“ANTI” 就是反联结的意思，而在NOT IN的执行计划中还有“NA” 表示考虑NULL值的意思。

半连接、反连接的优化案例
24号信仰
08-14 1640
先来理解几个概念： 半连接：两表关联，只返回匹配上的数据并且只会返回一张的表的数据，半连接一般就是指的在子查询中出现 IN 和 EXISTS 反连接：两表关联，只返回主表的数据，并且只返回主表与子表没关联上的数据，这种连接就叫反连接。反连接一般就是指的 NOT IN 和 NOT EXISTS 子查询展开：优化器将嵌套的子查询展开成一个等价的JOIN，然后去优化这个JOIN。如果不展开的情形是...
Oracle中的半联结和反联结详解
09-10
主要介绍了Oracle中的半联结和反联结详解,也称半连接和反连接,其实就是in、exists,需要的朋友可以参考下
深入解析：半连接与反连接的原理和等价改写方法
Enmotech的博客
03-26 1791
作者简介刘志永云和恩墨交付工程师，有5年的运营商及金融行业 DBA 运维工作经验，对故障处理、性能优化、迁移&升级有丰富的经验。本文由恩墨大讲堂152期线上分享整理而成。课程回看可点击文末“阅读原文”。 半连接的原理及等价改写1. 什么是半连接当两张表进行关联，只返回匹配上的数据并且只会返回一张的表的数据，半连接一般就是指的在子查询中出现 IN 和 EXISTS。执行计划最常见的有下面的两个：NE
11 半联结 & 反联结
weixin_34310127的博客
01-15 113
半联结 和 反联结是 oracle 优化器能够选择用来在获取信息时应用的两个密切相关的联结方法(实际上是联结方法的选项)   半联结 IN 的半联结 select /* using in */ department_name   from hr.departments dept where department_id IN (select department_id from hr.e...
半连接
minwang593的专栏
01-03 2178
这里的半连接指的是广义半连接，即由各种运算符所构成的子查询与主查询之间的连接。尽管子查询的种类有很多，但其都是一种为实现子查询与主查询之间连接的表连接。     实际上，大部分子查询的执行计划都是以表连接的形式出现的，且与一般方式的表连接有所不同。由于表连接其实就是集合运算，从逻辑上看，执行连接的两个集合之间是一种水平关系，不论哪个集合被优先执行都不会对结果产生任何影响。既然这两种集合是水平关系
半连接&反连接
Tale_Zhang的博客
05-24 493
半连接&反连接 1. 半连接 半连接返回左表中与右表至少匹配一次的数据行，通常体现为 EXISTS 或者 IN 子查询。左表驱动右表。只返回左表的数据，右表作为筛选条件。 可以用 EXISTS、 IN 或者 =ANY 举例：表t1和表t2做半连接，t1是驱动表，t2是被驱动表，半连接条件为t1.x=t2.y。 这里"t1.x semi= t2.y"的含义是只要在表t2中找到一条记录满足t1.x=t2.y，则马上停止搜索表t2，并直接返回表T1中满足条件t1.x=t2.y的记录。表t2中满足半连接条件
半连接、物化、反连接
qiyan2012的博客
05-16 1022
半连接、物化、反连接 且将新火试新茶 MySQL查询优化器有多种策略可用于评估子查询： 对于IN（或=ANY）子查询，优化器具有以下选择： 半连接 物化 EXISTS 对于NOT IN（或 <>ALL）子查询，优化器具有以下选择： 物化 EXISTS semi-join半连接 我们来看一个mysql官方给出的案例： 在课程表和班级花名册（每个班级中都有学生）中分别有命名的表 class和roster列出的班级。要列出实际招收学生的课程，您可以使用以下联接： 假设有一个名为cl
连接查询你一定不陌生，但你知道还有半连接和反连接吗？
Tony.Dong的专栏
08-21 6618
半连接（Semi Join）返回左表中与右表至少匹配一次的数据行，通常体现为 EXISTS 或者 IN 子查询，反连接（Anti Join）返回左表中与右表不匹配的数据行，通常体现为 NOT EXISTS 或者 NOT IN 子查询。
达梦反连接与半连接
sinat_32856657的博客
07-26 363
半连接与反连接 除了内连接，外连接等还有两种特殊的连接查询：半连接（Semi Join）和反连接（Anti Join）。由于 SQL 标准没有定义这两种连接查询语法，而是通过子查询的方式实现相同的效果。 1.半连接 半连接返回左表中与右表至少匹配一次的数据行，通常体现为 EXISTS 或者 IN 子查询。半连接的示意图如下： table1 中的 id = 2 在 table2 中没有对应的数据，所以连接的结果不包含该记录。 半连接只会返回左表中的数据，右表只用于条.
MySQL5.7新特性之select半连接优化
cuiruidu3106的博客
09-07 363
MySQL的子查询一直以来都是性能差的著称，解决方法是以join代替。 MySQL5.5版本中该查询先把accessLog表中版本为2.2的数据线过滤出来，然后每个符合条件的数据都与内表进行一次select id fro...
SQL Server执行计划图标速查手册
TimChen44的博客
06-14 1772
这是本人收集并整理的SQL Server执行计划中图标的功能说明 图形执行计划图标 Showplan 运算符 说明 无 Aggregate Aggregate运算符计算包含 MIN、MAX、SUM、COUNT 或 AVG 的表达式。Aggregate既是一个逻辑运算符，也是一个物理运算符。 Arithmetic Expression ...
TCP全连接和半连接
热门推荐
一只皮皮熊
10-29 2万+
TCP全连接和半连接
半连接（Semi-join）
weixin_34176694的博客
11-01 5176
2019独角兽企业重金招聘Python工程师标准>>> ...
半连接和内连接的区别
缕缕清风戏豪客的博客
10-12 8140
内连接(join)与半连接(semi join)的区别就是有没有根据匹配字段连接重复字段的数据(其作用几乎相同) 如下表的 dept的 id=1 有两个重复的id 部门编号字段 emp(雇员表) id    dept_id    name 1         1            z s ______________________________________________...
【oracle调优】in和exists
践行见远
01-29 2267
In和exists          相信很多朋友跟我一样，都曾经为这两个关于谁更快的问题纠结过，也曾脑子里不知为什么的记下了一些标准，比如在子表小的情况下in更快，在外表更小的情况下要用exists等。          但实际你的11g中做下测试，你会感到很奇怪。          对于两个子表大的情况和外表更大的情况，in和exists会得到相同的执行计划。 在很早的10g，orac
执行计划中各字段各模块描述
anjichan4261的博客
10-10 165
在SQL语句的执行计划中，包含很多字段项和很多模块，其不同字段代表了不同的含义且在不同的情形下某些字段、模块显示或不显示，下 面的描述给出了执行计划中各字段的含义以及各模块的描述。 有关执行计划中各字段模块的描述请参考： 执行计划中各字段各模块描述 有关由SQL语句来获取执行计划请参考： 使用 EXPLAIN PLAN 获取SQL...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值