专栏首页数据库新发现深入解析:半连接与反连接的原理和等价改写方法

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

半连接的原理及等价改写

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 不能改驱动表。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Oracle诊断案例-Job任务停止执行

    Last Updated: Saturday, 2004-11-20 12:47 Eygle

    数据和云01
  • Oracle诊断案例-Job任务停止执行[最终版]

    Last Updated: Friday, 2004-11-26 9:48 Eygle

    数据和云01
  • Oracle诊断案例-SGA与Swap之一

    案例描述: 用户报告,服务器启动一段时间以后,无法建立数据库连接 重新启动几分钟以后,再次无法连接 系统无法正常使用.

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

    半连接的原理及等价改写 1. 什么是半连接 当两张表进行关联,只返回匹配上的数据并且只会返回一张的表的数据,半连接一般就是指的在子查询中出现 IN 和 EXI...

    数据和云
  • 新建、删除文件夹需要强制刷新的问题

    用户2657851
  • 分析一个跨平台DDOS僵尸网络

    从最近“恶意软件必须死”的帖子了解到一些关于DNS放大攻击相关的LINUX恶意软件样本。我对linux恶意软件的研究非常感兴趣,而且这个很特别,因为他有一个DD...

    FB客服
  • 利用HTC One漏洞破解手机PIN密码

    HTC One手机运行的是Android 4.2.2、HBOOT 1.54.0000,它存在一个名为Bootloader的漏洞。这个漏洞早在2014年2月份就报...

    FB客服
  • 你懂00后吗?——首个00后QQ移动社交行为报告

    作者: yoyozeng,腾讯数据分析师,爱数据,爱电影,爱逛街的宅宅的射手女 00后作为移动互联网时代最重要的小鲜肉,这一代人在成长早期阶段就参与到移动互联网...

    腾讯大讲堂
  • CAN总线简介:如何以编程方式控制汽车

    最近,我正与Voyage公司的朋友合作研究,以实现福特Fusion空调系统(A/C)的编程控制。目前,Voyage公司正努力打造自动驾驶的终极目标:能够以低廉的...

    FB客服
  • 数据库时间出现'0000/00/00',难道我穿越了?

    前几天有个朋友遇到一个问题,在做日期类型数据的运算的时候出现了‘0000-00-00’的结果,不得其解。你是否遇到过同样的问题呢?这样一个并不存在的时间点,难道...

    数据和云

扫码关注云+社区

领取腾讯云代金券