11g oracleexits 与in 之争

于 2018-01-21 16:04:00 发布
阅读量92 收藏
点赞数
SQL> create table emp as select * from scott.emp;


Table created.


SQL> create table dept as select * from scott.dept;


Table created.


SQL> set timing on
SQL> set lines 20000
SQL> set  autot on
SQL> select * from dept where deptno NOT IN ( select deptno from emp ) ;


    DEPTNO DNAME   LOC
---------- -------------- -------------
40 OPERATIONS   BOSTON


Elapsed: 00:00:00.04


Execution Plan
----------------------------------------------------------
Plan hash value: 2100826622


---------------------------------------------------------------------------
| Id  | Operation    | Name | Rows  | Bytes | Cost (%CPU)| Time   |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |   | 4 |   172 | 6   (0)| 00:00:01 |
|*  1 |  HASH JOIN ANTI NA |   | 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 |
---------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


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


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




Statistics
----------------------------------------------------------
13  recursive calls
  0  db block gets
22  consistent gets
10  physical reads
  0  redo size
674  bytes sent via SQL*Net to client
523  bytes received via SQL*Net from client
  2  SQL*Net roundtrips to/from client
  0  sorts (memory)
  0  sorts (disk)
  1  rows processed


SQL> /


    DEPTNO DNAME   LOC
---------- -------------- -------------
40 OPERATIONS   BOSTON


Elapsed: 00:00:00.01


Execution Plan
----------------------------------------------------------
Plan hash value: 2100826622


---------------------------------------------------------------------------
| Id  | Operation    | Name | Rows  | Bytes | Cost (%CPU)| Time   |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |   | 4 |   172 | 6   (0)| 00:00:01 |
|*  1 |  HASH JOIN ANTI NA |   | 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 |
---------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


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


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




Statistics
----------------------------------------------------------
  0  recursive calls
  0  db block gets
  6  consistent gets
  0  physical reads
  0  redo size
674  bytes sent via SQL*Net to client
523  bytes received via SQL*Net from client
  2  SQL*Net roundtrips to/from client
  0  sorts (memory)
  0  sorts (disk)
  1  rows processed


SQL> select * from dept where not exists ( select deptno from emp where emp.deptno=dept.deptno) ;


    DEPTNO DNAME   LOC
---------- -------------- -------------
40 OPERATIONS   BOSTON


Elapsed: 00:00:00.01


Execution Plan
----------------------------------------------------------
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 |
---------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


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


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




Statistics
----------------------------------------------------------
  7  recursive calls
  0  db block gets
14  consistent gets
  0  physical reads
  0  redo size
674  bytes sent via SQL*Net to client
523  bytes received via SQL*Net from client
  2  SQL*Net roundtrips to/from client
  0  sorts (memory)
  0  sorts (disk)
  1  rows processed


SQL> /


    DEPTNO DNAME   LOC
---------- -------------- -------------
40 OPERATIONS   BOSTON


Elapsed: 00:00:00.01


Execution Plan
----------------------------------------------------------
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 |
---------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


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


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




Statistics
----------------------------------------------------------
  0  recursive calls
  0  db block gets
  6  consistent gets
  0  physical reads
  0  redo size
674  bytes sent via SQL*Net to client
523  bytes received via SQL*Net from client
  2  SQL*Net roundtrips to/from client
  0  sorts (memory)
  0  sorts (disk)
  1  rows processed



SQL> select * from dept where deptno NOT IN ( select deptno from emp where deptno is not null) and deptno is not null;


    DEPTNO DNAME   LOC
---------- -------------- -------------
40 OPERATIONS   BOSTON


Elapsed: 00:00:00.02


Execution Plan
----------------------------------------------------------
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 |
---------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   1 - access("DEPTNO"="DEPTNO")
   2 - filter("DEPTNO" IS NOT NULL)
   3 - filter("DEPTNO" IS NOT NULL)


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




Statistics
----------------------------------------------------------
  9  recursive calls
  0  db block gets
14  consistent gets
  0  physical reads
  0  redo size
674  bytes sent via SQL*Net to client
523  bytes received via SQL*Net from client
  2  SQL*Net roundtrips to/from client
  0  sorts (memory)
  0  sorts (disk)
  1  rows processed


SQL> /


    DEPTNO DNAME   LOC
---------- -------------- -------------
40 OPERATIONS   BOSTON


Elapsed: 00:00:00.01


Execution Plan
----------------------------------------------------------
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 |
---------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   1 - access("DEPTNO"="DEPTNO")
   2 - filter("DEPTNO" IS NOT NULL)
   3 - filter("DEPTNO" IS NOT NULL)


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




Statistics
----------------------------------------------------------
  0  recursive calls
  0  db block gets
  6  consistent gets
  0  physical reads
  0  redo size
674  bytes sent via SQL*Net to client
523  bytes received via SQL*Net from client
  2  SQL*Net roundtrips to/from client
  0  sorts (memory)
  0  sorts (disk)
  1  rows processed

结论：11g与空值有关，都可以用到anti的半连接算法，执行计划一样，性能一样

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30345407/viewspace-2150360/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30345407/viewspace-2150360/

cj990133
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值