关于oracle中的反连接

在之前的章节中见到讨论过oracle中的半连接 http://blog.itpub.net/23718752/viewspace-1334483/
与半连接相对应的是反连接，简而言之半连接就是查询条件中的in,exists，反连接就是not in, not exists这种类型的连接。
在asktom中，tom也对大家关心的in,exists,not in, not exists的问题进行了大量的佐证和解释。因为问题是在2001年左右提出来的，当时还是oracle 8的时代，帖子也沉里许久，在2013年的时候，tom在自己的博客中做了全新的解释，说大家都在讨论十几年前的东西了。传统RBO中的in,exists鲜明的对比在CBO中也都做了统一的优化处理，使得我们能够更加专注于程序的逻辑实现。

Followup   May 6, 2013 - 7pm UTC:

you are correct that things change and we actually discussed this in the body of this very very very long posting (13 years of posting ;) ) 

it is the difference between the CBO and RBO. 

here is a more formal write up of it: 

http://www.oracle.com/technetwork/issue-archive/2006/06-may/o36asktom-099107.html

对于反连接，我准备用下面的实例来进行简单的演示。
首先创建两个测试表，我们使用最熟悉的emp,dept表。为了保留原有的数据，我重新创建了两个新的表，因为dept表中的数据太少，我就特意添加了一列数据使得效果更加明显。
create table emp as select *from scott.emp;
create table dept as select *from scott.dept;
insert into dept values(50,'IT','BEIJING');
commit;
emp表中的数据情况如下，deptno目前只分布在3个部门。
SQL> select deptno from emp group by deptno;

    DEPTNO
----------
        30
        20
        10
dept表中有5个部们，那么部门40,50就是emp中不存在的。也就是目前还没有员工在deptno 40,50两个部门。
SQL> select deptno,dname from dept;

    DEPTNO DNAME
---------- --------------
        10 ACCOUNTING
        20 RESEARCH
        30 SALES
        40 OPERATIONS
        50 IT

我们来使用常用的反连接形式。查得deptno 为40,50的记录。这也是我们预期的。
select *from dept where not exists(select null from emp where emp.deptno=dept.deptno);

    DEPTNO DNAME          LOC
---------- -------------- -------------
        50 IT             BEIJING
        40 OPERATIONS     BOSTON
查看执行计划，能够清晰的看到对应的反连接表示anti，当然我们也可以通过hint /*+hash_aj*/来指定为hash 反连接。

Execution Plan
----------------------------------------------------------
Plan hash value: 474461924

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     5 |   215 |     8  (13)| 00:00:01 |
|*  1 |  HASH JOIN ANTI    |      |     5 |   215 |     8  (13)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |     5 |   150 |     4   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP  |    14 |   182 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

我们来看看和not exists等价的not in 形式。注意我在子查询中添加了deptno is not null,如果没有这个条件，not in和not exists是不等价的。如果emp中存在deptno为空的记录，那么整个查询就会返回0行。

SQL> select *from dept where deptno not in (select deptno from emp where deptno is not null);

    DEPTNO DNAME          LOC
---------- -------------- -------------
        50 IT             BEIJING
        40 OPERATIONS     BOSTON
执行计划如下。
Execution Plan
----------------------------------------------------------
Plan hash value: 810774822

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     5 |   215 |     8  (13)| 00:00:01 |
|*  1 |  HASH JOIN ANTI SNA|      |     5 |   215 |     8  (13)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |     5 |   150 |     4   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMP  |    14 |   182 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
下面的这种形式可能也是大家常犯的一个错误，就是会把原本的not in,not exists查询改写为下面的形式。结果就出乎意料了

select dept.* from dept,emp where dept.deptno!=emp.deptno;
。。。。

    DEPTNO DNAME          LOC
---------- -------------- -------------
        50 IT             BEIJING
        50 IT             BEIJING
        50 IT             BEIJING
        50 IT             BEIJING
        50 IT             BEIJING
        50 IT             BEIJING
        50 IT             BEIJING
        50 IT             BEIJING
        50 IT             BEIJING
        50 IT             BEIJING
        50 IT             BEIJING

    DEPTNO DNAME          LOC
---------- -------------- -------------
        50 IT             BEIJING

56 rows selected.
可以从执行计划中看到，直接是对emp,dept做了nested loop join，这种错误需要避免。
Execution Plan
----------------------------------------------------------
Plan hash value: 4192419542

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    56 |  2408 |    11   (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |      |    56 |  2408 |    11   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |     5 |   150 |     4   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMP  |    11 |   143 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------

-->not in ,not exists的等价实现
在实际的工作中，可能根据需要还会对not in ,not exists改写为其他的等价形式。比如使用下面的形式。
SQL> select dept.*　from dept ,emp where dept.deptno=emp.deptno(+) and emp.deptno is null;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        50 IT             BEIJING
        40 OPERATIONS     BOSTON
执行计划如下：
Execution Plan
----------------------------------------------------------
Plan hash value: 474461924

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     5 |   215 |     8  (13)| 00:00:01 |
|*  1 |  HASH JOIN ANTI    |      |     5 |   215 |     8  (13)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |     5 |   150 |     4   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP  |    14 |   182 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

下面的这种形式化就对结果集进行了筛查。如果emp中的deptno为空，就设定一个不存在的deptno值。
select dept.* from dept where deptno not in (select nvl(deptno,'-1') from emp)
SQL> /

    DEPTNO DNAME          LOC
---------- -------------- -------------
        50 IT             BEIJING
        40 OPERATIONS     BOSTON
执行计划如下：
Execution Plan
----------------------------------------------------------
Plan hash value: 810774822

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     5 |   215 |     8  (13)| 00:00:01 |
|*  1 |  HASH JOIN ANTI SNA|      |     5 |   215 |     8  (13)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |     5 |   150 |     4   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP  |    14 |   182 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
还可以使用集合来实现。不过这种方式使用的场景要少一些。这种方式直接把not in改换成了in的格式。
SQL> select *from dept where deptno in (select deptno from dept minus select deptno from emp);

    DEPTNO DNAME          LOC
---------- -------------- -------------
        40 OPERATIONS     BOSTON
        50 IT             BEIJING
执行计划如下：
Execution Plan
----------------------------------------------------------
Plan hash value: 3106111345

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     5 |   150 |    13  (16)| 00:00:01 |
|*  1 |  FILTER              |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL  | DEPT |     5 |   150 |     4   (0)| 00:00:01 |
|   3 |   MINUS              |      |       |       |            |          |
|   4 |    SORT UNIQUE NOSORT|      |     1 |    13 |     5  (20)| 00:00:01 |
|*  5 |     TABLE ACCESS FULL| DEPT |     1 |    13 |     4   (0)| 00:00:01 |
|   6 |    SORT UNIQUE NOSORT|      |     1 |    13 |     4  (25)| 00:00:01 |
|*  7 |     TABLE ACCESS FULL| EMP  |     1 |    13 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

时间： 2024-10-22 20:55:18

关于oracle中的反连接的相关文章

Oracle中SQL语句连接字符串的符号使用介绍_oracle

Oracle中SQL语句连接字符串的符号为|| 复制代码 代码如下: select catstr(tcdm) || (',') from T_YWCJ_RWCJR where cjrjh='009846' and rwid='12050' and jsdm='CJY' 拼接成一条数据并连接一个","

MySQL中的反连接(r12笔记第45天）

  关于Oracle的半连接,反连接,我一直认为这是一个能讲很长时间的话题,所以在我的新书<Oracle DBA工作笔记>中讲性能优化的时候,我花了不少的笔墨做了阐述,结果在做MySQL性能优化的时候,优化思路切换到MySQL层面,我发现要说的东西要更多.总体来看,这部分的优化细节MySQL还在路上,不同的版本中都能够一窥其中的变化,可以看到在不断改进.    在表的连接上,半连接,反连接本身很平常,但是统计信息的不够丰富导致执行计划的评估中可能会出现较大差别,会很可能把半连接,反连接的实现方

ORACLE 半连接与反连接

概念:所谓半连接,就是在进行连接查询的时候,内层如果有相应的记录及返回一个TRUE,而不需要访问余下的行,如果内层表特别巨大的时候将会大大节省时间. 列子:  select /* using in */ department_name       from hr.departments dept       where department_id in (select department_id from hr.employees emp)   ============ Plan Table =

Oracle中插入特殊字符:&amp;amp;和&amp;#39;的解决方法汇总_oracle

今天在导入一批数据到Oracle时,碰到了这样一个问题:Toad提示要给一个自定义变量AMP赋值,一开始我很纳闷,数据是一系列的Insert语句,怎么会有自定义变量呢?后来搜索了一下关键字AMP发现,原来是因为在插入数据中有一个字段的内容如下: http://xxx.com/3DX?uid=0676&sid=rt_060908 Oracle把这里的URL的参数连接符&当成是一个自定义变量了,所以要求我给变量AMP赋值.经过测试之后,总结出以下三种方法: 方法一:在要插入的SQL语句前加上S

oracle中或者or和左连接left join 怎么一起使用????

问题描述 oracle中或者or和左连接left join 怎么一起使用???? 语句 select a.owner_id,count(b1.ship_id) cnt,nvl(sum(b1.dwt),0) dwt from so_shipowner_info a left join order_info_secondhand_formal b1 on b1.ship_business_date>to_char(sysdate,'yyyy') and (b1.buyer_id=a.owner_id

Oracle中删除用户和表空间的常见问题(比如：ORA-01940无法删除当前已连接用户的解决方案)

这时候以管理员身份进入sqlplus命令窗口. 在删除用户的时候有时候会出现以下问题: ORA-01940无法删除当前已连接用户 这时候的解决方案是: 1)查看用户的连接状况   select username,sid,serial# from v$session (2)找到要删除用户的sid,和serial,并删除 例如:你要删除用户'WUZHQ',可以这样做: alter system kill session'532,4562'; 这里的532表示的是sid,4562表示的是seria.

Oracle中字符串连接的实现方法_oracle

和其他数据库系统类似,Oracle字符串连接使用"||"进行字符串拼接,其使用方式和MSSQLServer中的加号"+"一样. 比如执行下面的SQL语句: 复制代码 代码如下: SELECT '工号为'||FNumber||'的员工姓名为'||FName FROM T_Employee WHERE FName IS NOT NULL 除了"||",Oracle还支持使用CONCAT()函数进行字符串拼接,比如执行下面的SQL语句: SELECT

Oracle中简单查询、限定查询、数据排序SQL语句范例和详细注解_oracle

一.简单查询 SQL(Structured Query Language) 结构化查询语言,是一种数据库查询和程序设计语言,用于存取数据以及查询.更新和管理关系数据库系统.ANSI(美国国家标准学会)声称,SQL是关系数据库管理系统的标准语言. Oracle数据库之所以发展的很好,主要也是因为Oracle是全世界最早采用SQL语句的数据库产品. SQL功能强大,概括起来,它可以分成以下几组: 复制代码 代码如下: DML(Data Manipulation Language) 数据操作语言,用于

oracle中110个常用函数介绍_oracle

1. ASCII 返回与指定的字符对应的十进制数; SQL> select ascii(A) A,ascii(a) a,ascii(0) zero,ascii( ) space from dual; A A ZERO SPACE --------- --------- --------- --------- 65 97 48 32 2. CHR 给出整数,返回对应的字符; SQL> select chr(54740) zhao,chr(65) chr65 from dual; ZH C --