<< 返回文章列表

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

2018年3月27日
刘志永
2418

本文由恩墨大讲堂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’,大讲堂讲师课程资料