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

半连接的原理及等价改写

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)

原文发表时间:2018-03-26

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏乐沙弥的世界

Oracle 监控索引的使用率

    Oracle提供了索引监控特性来判断索引是否被使用。在Oracle 10g中,收集统计信息会使得索引被监控,在Oracle 11g中该现象不复存在。尽管...

23230
来自专栏杨建荣的学习笔记

通过使用hint unnest调优sql语句(r4笔记第38天)

生产环境中有一条sql语句通过sql_monitor看到执行的时间实在是太惊人了,竟然达到了13个小时,而且还没有执行完。 SessionAPPC (20015...

32580
来自专栏杨建荣的学习笔记

关于统计信息过期的性能落差(r5笔记第36天)

今天客户反馈某一个应用部署补丁的时候,执行了一个脚本一个多小时还没有执行完。 语句是下面这样的形式。 insert into em1_rater_00068_0...

42860
来自专栏杨建荣的学习笔记

sql_profile的使用(一) (r2笔记29天)

今天看了老熊关于sql_profile的讲解,受益匪浅,自己在本机也做了一通,感觉好记性不如烂笔头还是得多总结总测试才能真正理解。 准备的数据如下,创建两个表,...

29090
来自专栏黑泽君的专栏

day43_Oracle学习笔记_02

7820
来自专栏乐沙弥的世界

SQL 基础--> 视图(CREATE VIEW)

视图来源于表,所有对视图数据的修改最终都会被反映到视图的基表中,这些修改必须服从基表的完整性约束,并同样会触发定义

8130
来自专栏杨建荣的学习笔记

一条简单的sql在11g和12c中的不同(r5笔记第2天)

今天在查看awr报告的时候,有一句很简单的sql语句引起了我的注意,因为它排在SQL Order by Reads的第2位。 Physical ReadsExe...

36540
来自专栏杨建荣的学习笔记

生产系统调优之_敢于质疑(90天)

接着昨天的那个问题来说。有个sql语句在做了统计信息收集之后,速度有了一定的提升,从5秒的响应降低到了2秒。但是和预期还是有一定 的差距,按照80条查询请求在短...

27070
来自专栏杨建荣的学习笔记

循序渐进调优union相关的sql(r2笔记23天)

今天在生产中发现一条sql语句消耗了大量的cpu资源。使用top -c来查看。 PID USER PR NI VIRT RES SHR S...

38460
来自专栏乐沙弥的世界

SQL 基础--> ROLLUP与CUBE运算符实现数据汇总

--=============================================

13230

扫码关注云+社区

领取腾讯云代金券