深入解析：半连接与反连接的原理和等价改写方法

最新推荐文章于 2025-11-23 16:37:08 发布
转载 最新推荐文章于 2025-11-23 16:37:08 发布 · 1.8k 阅读 · 2

作者简介

刘志永

云和恩墨交付工程师，有5年的运营商及金融行业 DBA 运维工作经验，对故障处理、性能优化、迁移&升级有丰富的经验。


本文由恩墨大讲堂152期线上分享整理而成。课程回看可点击文末“阅读原文”。 

半连接的原理及等价改写


1. 什么是半连接


当两张表进行关联，只返回匹配上的数据并且只会返回一张的表的数据，半连接一般就是指的在子查询中出现 IN 和 EXISTS。


执行计划最常见的有下面的两个：

  • NESTED LOOPS SEMI

  • HASH JOIN SEMI


1.1 NESTED LOOP SEMI


执行步骤：

  1. 优化器选择主查询中的表为驱动表；

  2. 选择子查询中的表为被驱动表；

  3. 把驱动表的每一行根据连接列去被驱动表中去查找匹配的行；

  4. 如果在被驱动表中匹配上一个或者多个，则返回驱动表中的数据。 


HINT /*+ use_nl(b,a) leading(b) */

看下这个 SQL：

explain plan for

Select a.deptno, a.dname

from dept a  

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


执行计划：

1* select * from table(dbms_xplan.display)

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 3429995275

 

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

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

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

|   0| SELECT STATEMENT   |            |     3 |  105 |     3   (0)| 00:00:01 |

|   1|  NESTED LOOPSSEMI |            |     3 |  105 |     3  (0)| 00:00:01 |

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

|*  3|   INDEX RANGE SCAN | IDX_DEPTNO |    11 |  143 |     0   (0)| 00:00:01 |

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

 

 

PLAN_TABLE_OUTPUT

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

Predicate Information (identified byoperation id):

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


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


用 hint 换下驱动表：

跑下这个 SQL：

Explain plan for

Select /*+ use_nl(b,a) leading(b) */   a.deptno, a.dname

from dept a  

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


执行计划：

SQL> select * fromtable(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 1511694371

 

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

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

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

|   0| SELECT STATEMENT             |         |    3 |   105 |     7 (15)| 00:00:01 |

|   1|  NESTED LOOPS                |         |    3 |   105 |     7 (15)| 00:00:01 |

|   2|   NESTED LOOPS               |         |    3 |   105 |     7 (15)| 00:00:01 |

|   3|    SORT UNIQUE               |         |   14 |   182 |     3  (0)| 00:00:01 |

|   4|     TABLE ACCESS FULL        | EMP     |   14 |   182 |     3  (0)| 00:00:01 |

|*  5|    INDEX UNIQUE SCAN         | PK_DEPT |     1 |      |     0   (0)| 00:00:01 |

 

PLAN_TABLE_OUTPUT

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

|   6|   TABLE ACCESS BY INDEX ROWID|DEPT    |     1 |   22 |     1   (0)| 00:00:01 |

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

 

Predicate Information (identified byoperation id):

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

 

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


等价改写

用 IN 来改写：

Explain plan for

select  a.deptno, a.dname from dept a where a.deptno in (select deptno from empb);


执行计划：

SQL> select * fromtable(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 1505539579

 

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

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

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

|   0| SELECT STATEMENT   |             |     3 |  105 |     3   (0)| 00:00:01 |

|   1|  NESTED LOOPSSEMI |             |     3 |  105 |     3   (0)| 00:00:01 |

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

|*  3|   INDEX RANGE SCAN | IDEX_DEPTNO |    11 |  143 |     0   (0)| 00:00:01 |

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

 

 

PLAN_TABLE_OUTPUT

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

Predicate Information (identified byoperation id):

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

 

   3- access("A"."DEPTNO"="DEPTNO")


改写成 inner join：

Explain plan for

select a.deptno, a.dname from dept a,(select b.deptno from emp b group by b.deptno) c where a.deptno=c.deptno;


执行计划：

SQL> select * fromtable(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 1766026621

 

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

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

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

|   0| SELECT STATEMENT    |             |    14 |  658 |     4  (25)| 00:00:01 |

|   1|  HASH GROUP BY      |             |   14 |   658 |     4 (25)| 00:00:01 |

|   2|   NESTED LOOPS      |             |    14 |  658 |     3   (0)| 00:00:01 |

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

|*  4|    INDEX RANGE SCAN | IDEX_DEPTNO|     4 |    52 |    0   (0)| 00:00:01 |

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

 

PLAN_TABLE_OUTPUT

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

 

Predicate Information (identified byoperation id):

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

 

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


Inner join 多了 group by 的操作，emp 的 depno 值不是唯一的，要保证逻辑上跟半连接的一致就需要把 emp 的连接进行去重操作，跟上面 emp 作为驱动表是一致的。


1.2 HASH JOIN SEMI


执行步骤：

  1. 优化器把主查询中的表当作驱动表，把 select 列和 join 列读入 pga 中，并把 join 列进行 hash 运算构建 hash table；

  2. 把另外一张表的连接列也进行 hash 运算，然后到 pga 中去探测 hash table ，如果关联上就返回驱动表的数据。


Hint /*+ use_hash(a,b) leading(a)*/

看下这个 SQL：

explainplan for

Select  a.*

fromdept a  

whereexists (select   1 from emp b wherea.deptno = b.deptno);


执行计划：

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

 

PLAN_TABLE_OUTPUT

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

Planhash value: 1754319153

 

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

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

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

|   0 | SELECT STATEMENT   |     |     3 |   129 |    6   (0)| 00:00:01 |

|*  1 |  HASH JOIN SEMI   |      |     3 |  129 |     6   (0)| 00:00:01 |

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

|   3 |   TABLEACCESS FULL| EMP  |    14 |  182 |     3   (0)| 00:00:01 |

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

 

 

PLAN_TABLE_OUTPUT

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

PredicateInformation (identified by operation id):

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

 

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


改变下驱动表：

explain plan for

Select/*+ use_hash(a,b) leading(b)*/  a.*

from dept a  

where exists (select     1 from emp b wherea.deptno = b.deptno);


执行计划：

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

 

PLAN_TABLE_OUTPUT

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

Planhash value: 300394613

 

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

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

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

|   0 | SELECT STATEMENT    |     |     3 |   129 |    7  (15)| 00:00:01 |

|*  1 | HASH JOIN          |      |    3 |   129 |     7 (15)| 00:00:01 |

|   2 |   SORT UNIQUE       |     |    14 |   182 |    3   (0)| 00:00:01 |

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

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

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

 

PLAN_TABLE_OUTPUT

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

 

PredicateInformation (identified by operation id):

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

 

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

 

Note

-----

   - dynamic sampling used for this statement(level=2)


Emp 连接列有重复值，需要进行去重操作，保证逻辑一致。


等价改写

用 IN 来改写：

explain plan for

select   a.* from dept a where a.deptno in (selectdeptno from emp b);


执行计划：

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

 

PLAN_TABLE_OUTPUT

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

Planhash value: 1754319153

 

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

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

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

|   0 | SELECT STATEMENT   |     |     3 |   129 |    6   (0)| 00:00:01 |

|*  1 |  HASH JOIN SEMI    |     |     3 |   129 |    6   (0)| 00:00:01 |

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

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

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

 

 

PLAN_TABLE_OUTPUT

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

Predicate Information (identified by operation id):

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

 

   1 -access("A"."DEPTNO"="DEPTNO")


用内连接改写：

explain plan for

select a.*  from dept a ,(select b.deptno fromemp b group by b.deptno) c where a.deptno=c.deptno;


执行计划：

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

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 3977374068

 

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

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

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

|   0 | SELECT STATEMENT     |     |    14 |   602 |    7  (15)| 00:00:01 |

|*  1 | HASH JOIN           |      |   14 |   602 |     7 (15)| 00:00:01 |

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

|   3 |  VIEW               |      |   14 |   182 |     4 (25)| 00:00:01 |

|   4 |   HASH GROUP BY    |      |   14 |   182 |     4 (25)| 00:00:01 |

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

 

PLAN_TABLE_OUTPUT

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

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

 

Predicate Information (identified by operation id):

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

 

   1 -access("A"."DEPTNO"="C"."DEPTNO")


反连接的原理及等价改写


1. 什么是反连接


两表关联只返回主表的数据，并且只返回主表与子表没关联上的数据，这种连接就叫反连接。反连接一般就是指的 NOT IN 和 NOT EXISTS。


执行计划：

NESTED LOOP ANTI

HASH JOIN ANTI

 

1.1 NESTED LOOP ANTI

 

NESTED LOOP ANTI 执行步骤：

  1. 优化器选择主查询中的表为驱动表；

  2. 选择子查询中的表为被驱动表；

  3. 把驱动表的每一行根据连接列去被驱动表中去查找匹配的行；

  4. 如果在被驱动表中没有匹配上，则返回驱动表中的没有匹配上的数据。


Hint /*+ use_nl(a,b) leading(b) */

在反连接中驱动表不会反生变化，因为反连接等价于外连接+子表连接条件 is null，使用嵌套循环进行关联的时候无法更改驱动表，驱动表会被固定为主表。


我们来看下下面这个执行计划：

explain plan for

Select      a.*

from dept a  

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


执行计划：

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

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 2795798678

 

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

-

 

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

|

 

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

-

 

 

PLAN_TABLE_OUTPUT

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

|   0 | SELECT STATEMENT   |           |     4 |   172 |    3   (0)| 00:00:01

|

 

|   1 | NESTED LOOPS ANTI |           |     4 |   172 |    3   (0)| 00:00:01

|

 

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

|

 

|*  3 |  INDEX RANGE SCAN | IDX_DEPTNO |    1 |    13 |     0  (0)| 00:00:01

|

 

PLAN_TABLE_OUTPUT

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

 

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

-

 

 

Predicate Information (identified by operation id):

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

 

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

 

使用/*+ use_nl(a,b) leading(b) */来更改驱动表看下：

explain plan for

Select    /*+ use_nl(a,b) leading(b) */   a.*

from dept a  

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


执行计划：

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

 

PLAN_TABLE_OUTPUT

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

Planhash value: 2795798678

 

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

-

 

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

|

 

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

-

 

 

PLAN_TABLE_OUTPUT

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

|   0 | SELECT STATEMENT   |           |     4 |   172 |    3   (0)| 00:00:01

|

 

|   1 | NESTED LOOPS ANTI |           |     4 |   172 |    3   (0)| 00:00:01

|

 

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

|

 

|*  3 |  INDEX RANGE SCAN | IDX_DEPTNO |    1 |    13 |     0  (0)| 00:00:01

|

 

PLAN_TABLE_OUTPUT

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

 

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

-

 

 

Predicate Information (identified by operation id):

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

 

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


等价改写

Not exists 改写为 not in，要注意子查询要排除 null 的情况，因为 not in 后面如果有 null 值整个查询都为空。


看下面这条 SQL：

explain plan for

select   a.* from dept a where a.deptno not in(select deptno from emp b where deptno is not null);


执行计划：

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

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 2795798678

 

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

-

 

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

|

 

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

-

 

 

PLAN_TABLE_OUTPUT

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

|   0 | SELECT STATEMENT   |           |     4 |   172 |    3   (0)| 00:00:01

|

 

|   1 |  NESTED LOOPS ANTI |            |     4 |  172 |     3   (0)| 00:00:01

|

 

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

|

 

|*  3 |  INDEX RANGE SCAN | IDX_DEPTNO |    1 |    13 |     0  (0)| 00:00:01

|

 

PLAN_TABLE_OUTPUT

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

 

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

-

 

 

PredicateInformation (identified by operation id):

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

 

   3 -access("A"."DEPTNO"="DEPTNO")

       filter("DEPTNO" IS NOT NULL)


用外连接改写：

根据反连接定义，翻译为标准 SQL 写法就是外连接+子表连接条件 is null。外连接是只返回主表的数据，子表只返回关联的数据，加上子表条件 is null 就是只返回没有关联上的数据。


我们看下这条 SQL：

selecta.*,b.*

  from dept a

   left join emp b on a.deptno = b.deptno;

                           

explainplan for

 select a.*

  from dept a

   left join emp b on a.deptno = b.deptno

 where b.deptno is null;


加上 b.deptno is null 就返回了没有关联上的数据。


执行计划：

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

 

PLAN_TABLE_OUTPUT

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

Planhash value: 2795798678

 

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

-

 

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

|

 

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

-

 

 

PLAN_TABLE_OUTPUT

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

|   0 | SELECT STATEMENT   |           |     4 |  172 |     3   (0)| 00:00:01

|

 

|   1 |  NESTED LOOPS ANTI |            |     4 |  172 |     3   (0)| 00:00:01

|

 

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

|

 

|*  3 |  INDEX RANGE SCAN | IDX_DEPTNO |     1 |   13 |     0   (0)| 00:00:01

|

 

PLAN_TABLE_OUTPUT

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

 

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

-

 

 

PredicateInformation (identified by operation id):

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

 

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


1.2 HASH JOIN ANTI


执行步骤：

  1. 优化器把主查询中的表当作驱动表，把 select 列和 join 列读入 pga 中，并把 join 列进行 hash 运算构建 hash table；

  2. 把另外一张表的连接列也进行 hash 运算，然后到 pga 中去探测 hash table ，如果没有关联上就返回驱动表的数据。


两表关联如果是外连接，要改变 hash 连接的驱动表必须使用 swap_join_inputs。

看下面这个 SQL：

explainplan for

Select     a.*

fromdept a  

wherenot exists (select     1 from emp b wherea.deptno =b.deptno);


执行计划：

PLAN_TABLE_OUTPUT

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

Planhash value: 474461924

 

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

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

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

|   0 | SELECT STATEMENT   |     |     4 |   172 |    6   (0)| 00:00:01 |

|*  1 | HASH JOIN ANTI    |      |    4 |   172 |     6  (0)| 00:00:01 |

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

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

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

 

 

PLAN_TABLE_OUTPUT

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

PredicateInformation (identified by operation id):

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

 

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


加 hint，更改驱动表

explainplan for

Select     /*+ use_hash(dept,emp@c) swap_join_inputs(emp@c)*/   *

fromdept  

wherenot exists (select   /*+ qb_name(c)*/ 1from emp   where  emp.deptno = dept.deptno);


执行计划：

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

 

PLAN_TABLE_OUTPUT

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

Plan hashvalue: 152508289

 

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

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

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

|   0 | SELECT STATEMENT     |     |     4 |   172 |    6   (0)| 00:00:01 |

|*  1 | HASH JOIN RIGHT ANTI|      |     4 |  172 |     6   (0)| 00:00:01 |

|   2 |  TABLE ACCESS FULL  | EMP  |    14|   182 |     3  (0)| 00:00:01 |

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

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

 

 

PLAN_TABLE_OUTPUT

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

PredicateInformation (identified by operation id):

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

 

   1 -access("EMP"."DEPTNO"="DEPT"."DEPTNO")


等价改写

Not in 改写：

explain plan for

select   a.* from dept a where a.deptno not in(select deptno from emp b where deptno is not null);


执行计划：

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

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 474461924

 

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

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

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

|   0 | SELECT STATEMENT   |     |     4 |   172 |    6   (0)| 00:00:01 |

|*  1 | HASH JOIN ANTI    |      |    4 |   172 |     6  (0)| 00:00:01 |

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

|*  3 |  TABLE ACCESS FULL| EMP  |    14 |  182 |     3   (0)| 00:00:01 |

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

 

 

PLAN_TABLE_OUTPUT

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

Predicate Information (identified by operation id):

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

 

   1 -access("A"."DEPTNO"="DEPTNO")

   3 - filter("DEPTNO" IS NOT NULL)


改为外连接：

explain plan for

 select a.*

 from dept a

  left join emp b on a.deptno = b.deptno

 where b.deptno is null;


执行计划：

SQL> select * fromtable(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 474461924

 

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

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

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

|   0| SELECT STATEMENT   |      |    4 |   172 |     6  (0)| 00:00:01 |

|*  1|  HASH JOIN ANTI    |     |     4 |   172 |    6   (0)| 00:00:01 |

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

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

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

 

 

PLAN_TABLE_OUTPUT

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

Predicate Information (identified byoperation id):

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

 

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


总结


通过半连接和反连接的学习，我们可以知道半连接一般是出现在子查询有 IN 或者 EXISTS 的情况，并且只返回主表连接列关联上的数据，如果连接列是唯一值的话可以直接转化为内连接，反连接一般是出现在子查询有 NOT IN 或者 NOT EXISTS 的情况，并且只返回主表连接列没有关联上的数据，NEST LOOP ANTI 不能改驱动表。


资源下载

关注公众号：数据和云（OraNews）回复关键字获取

‘2017DTC’，2017 DTC 大会 PPT

‘DBALIFE’，“DBA 的一天”海报

‘DBA04’，DBA 手记4 经典篇章电子书

‘RACV1’, RAC 系列课程视频及 PPT

‘122ARCH’，Oracle 12.2 体系结构图

‘2017OOW’，Oracle OpenWorld 资料

‘PRELECTION’，大讲堂讲师课程资料


  戳原文，回看视频课程！

数据和云
关注
Hive exists 用法
Top5软件工程硕士，先后在京东、字节从事多年Java后端开发、实时和离线大数据开发
01-10 1万+
where exists(select c2/1/*/key2from tb2 where tb2.key2 = tb1.key1) exists()中的select后面跟其他字段也行，where后面用关联字段即可！ selec * : in : exists(select *) : exists(select 1) : exists(select 其他列) : exists(select 关联列) : ...
Hive查询sql left join exists
10-20
里面 一个例子，说了几个需求，基本能符合，我要使用的功能，左外连接，还有exists替代方案，都很实用，结合文档看一下。
SQL优化——表连接方式
码说芯语的博客
04-19 1606
嵌套循环、HASH连接、排序合并连接、笛卡尔儿连接、标量子查询、半连接、反连接、FILTER、SQL语句的本质
2026年阿里Java面试题库（纯干货，超详细，从题目到答案）
最新发布
程序员高级码农的博客
11-23 1577
问题：请详细解释Java内存模型(JMM)及其在并发编程中的作用答案： Java内存模型定义了Java程序中各种变量的访问规则，以及在JVM中将变量存储到内存和从内存中取出变量的底层细节。JMM主要包含以下关键概念：主内存与工作内存：内存间交互操作：happens-before原则：应用场景：问题：Java并发编程中需要解决的三大核心问题是什么？如何解决？答案： 并发编程需要解决的三大核心问题：可见性问题：原子性问题：有序性问题：问题：详细描述JVM的垃圾回收机制，包括各种GC算法及其适用场景答案： JVM
半连接&反连接
Tale_Zhang的博客
05-24 523
半连接&反连接 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中满足半连接条件
半联结&反联结！
事后诸葛亮的程序人生（微信：zq9017197）
12-26 1105
半联结是在两个数据集（表）之间的联结，其中第一个数据集中的数据行在决定是否返回时会根据在另一个数据集中出现或不出现至少一个相匹配的数据行来确定。“不出先”匹配行——这是半联结的一种特殊形式，称为反联结。 标准的内联结与半联结之间最主要的区别在于在半联结中，第1个数据集中的每一条记录至返回一次，而不管在第二个数据集中有几条匹配的数据。这个定义表明这个查询的实际处理过程可以通过在找到第1个匹配以后马
半连接、物化、反连接
qiyan2012的博客
05-16 1093
半连接、物化、反连接 且将新火试新茶 MySQL查询优化器有多种策略可用于评估子查询： 对于IN（或=ANY）子查询，优化器具有以下选择： 半连接 物化 EXISTS 对于NOT IN（或 <>ALL）子查询，优化器具有以下选择： 物化 EXISTS semi-join半连接 我们来看一个mysql官方给出的案例： 在课程表和班级花名册（每个班级中都有学生）中分别有命名的表 class和roster列出的班级。要列出实际招收学生的课程，您可以使用以下联接： 假设有一个名为cl
连接查询你一定不陌生，但你知道还有半连接和反连接吗？
weixin_44519342的博客
01-11 1188
连接查询你一定不陌生，但你知道还有半连接和反连接吗？
关系数据库优化全解析：查询处理与优化的深度连接
[关系数据库优化全解析：查询处理与优化的深度连接](https://img-blog.csdnimg.cn/452d8662e2d5486bb8514b36d61cb21f.png) # 摘要 关系数据库优化是提高数据存储、检索效率和整体性能的关键。本文系统性地回顾了...
关系查询处理与优化深度解析：掌握等价变换提升性能
随后，详细探讨了基于规则和成本的查询优化方法，以及优化器的选择与判断策略。本文还着重分析了等价变换在提高查询性能和优化实际数据库系统中的实际应用，通过实例展示了不同数据库系统的等价变换实现和优化效果。...
ASDS高级特性深入解析：掌握不为人知的秘密
[ASDS高级特性深入解析：掌握不为人知的秘密](https://www.hovione.com/sites/default/files/styles/half_width_scaled/public/assets/images/webinar-header-image-formulating-asds.png?itok=8t5fwBhQ) ...
量子硬件：cQED与cirQED的深入解析
# 量子硬件：cQED与cirQED的深入解析 ## 1. 电路元件的阻抗特性 在电路分析中，不同元件具有各自独特的阻抗特性。对于电容、电感和电阻，其阻抗表达式分别为： - 电容阻抗：$Z_C = \frac{1}{j\omega C}$ - 电感...
Oracle查询优化改写技巧与实际案例解析
综上所述，该文件系统地整合了Oracle数据库环境下SQL性能优化的理论知识与实战经验，强调通过科学的分析方法和精准的语句改写来解决性能问题，对于DBA、开发人员以及数据工程师具有极高的学习和参考价值。...
达梦反连接与半连接
sinat_32856657的博客
07-26 414
半连接与反连接 除了内连接，外连接等还有两种特殊的连接查询：半连接（Semi Join）和反连接（Anti Join）。由于 SQL 标准没有定义这两种连接查询语法，而是通过子查询的方式实现相同的效果。 1.半连接 半连接返回左表中与右表至少匹配一次的数据行，通常体现为 EXISTS 或者 IN 子查询。半连接的示意图如下： table1 中的 id = 2 在 table2 中没有对应的数据，所以连接的结果不包含该记录。 半连接只会返回左表中的数据，右表只用于条.
Hive中的in、exists和left semi join
日常分享数据分析开发、编程语言内容
07-17 5078
Hive中的in、exists和left semi join
Hive in exists 区别
08-11 1110
IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况
Hive之——怎样写exist/in子句
热门推荐
冰河的专栏
05-02 1万+
转载请注明出处：https://blog.youkuaiyun.com/l1028386804/article/details/80164963Hive 不支持 where 子句中的子查询， SQL 常用的 exist in 子句需要改写。这一改写相对简单。考虑以下 SQL 查询语句：SELECT a.key, a.value FROM a WHERE a.key in54 (SELECT b.key FRO...
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值