事先到近来总括写了三篇有关 exists,以前到前天一共写了三篇关于 exists

 

最终来写个小结,从前到现行反革命计算写了三篇关于 exists
的篇章,自觉有点麻烦。但也是团结不停学习的进度。很多事物依旧须求协调不停的去操作,思考。总括。言归正传。
exists 和 in
在两张表大概大小的动静下,作用,速度,是不会距离一点都不小的。
在一大学一年级小的景况下是存在成效上的歧异的。固然她们的执行布署有恐怕是千篇一律的。
exists 更适用于 子表大,in 适用于父表大。具体请看第3篇。
not exsits 与not in 比较,not in
之前已经看过了,并不会走相关的目录。所以,尽量使用 not exists。
另,那多个查询中假若有null值,会重返全体的结果集。所以注意写语句的时候尽量回避null值。
在那里祝大家,新岁欢欣吧,给协调定二个小指标。只假诺工作日,每一天持之以恒写一篇博客。努力学习,争取早日成为3个投机所企望的指南!加油2018.

本来在此以前觉得,not exists
和前边的参数一样的也是索要分处境的话,不过做了试验测试之后发现。Not
exists 和not in 的选项格局相当的大致,便是只选 not exists 因为 not
in加上了不会走索引。而not exists
会走。那样就限制了,假如要动用的话就尽量使用not exists。
Not exists 的趣味是,关联合检查询,重临除了关联子查询所得结果之外的值,
看如下的实行安排和代价便能够看出来。两者的分化。

 

终极来写个小结,在此以前到后天一起写了三篇关于 exists
的小说,自觉有点麻烦。但也是团结不停学习的进程。很多事物仍然须求自个儿不停的去操作,思考。计算。言归正传。
exists 和 in
在两张表大概大小的图景下,功用,速度,是不会大有不同的。
在一大学一年级小的事态下是存在作用上的差别的。即使他们的实践陈设有或许是一模一样的。
exists 更适用于 子表大,in 适用于父表大。具体请看第②篇。
not exsits 与not in 比较,not in
在此以前早已看过了,并不会走相关的目录。所以,尽量接纳 not exists。
另,那多少个查询中一经有null值,会回到全部的结果集。所以注意写语句的时候尽量回避null值。
在此处祝大家,春节兴奋吧,给自个儿定一个小目的。只假设工作日,每一天坚持不渝写一篇博客。努力学习,争取早日成为三个友好所梦想的规范!加油2018.

SCOTT@ rac1>select * from emp where  empno not in  (select empno from t4 where  t4.deptno=20) ;

9 rows selected.

Elapsed: 00:00:01.72

Execution Plan
----------------------------------------------------------
Plan hash value: 3504968978

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    20 |  1120 |  6130   (2)| 00:00:01 |
|*  1 |  HASH JOIN ANTI NA |      |    20 |  1120 |  6130   (2)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    20 |   600 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T4   |  1354K|    33M|  6120   (2)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - access("EMPNO"="EMPNO")
   3 - filter("T4"."DEPTNO"=20)

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


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



SCOTT@ rac1>select * from emp where  not exists (select empno from t4 where emp.deptno=t4.deptno and t4.deptno=20) ;

9 rows selected.

Elapsed: 00:00:05.45

Execution Plan
----------------------------------------------------------
Plan hash value: 3745834269

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |    20 |   860 |  2033  (98)| 00:00:01 |
|   1 |  NESTED LOOPS ANTI |           |    20 |   860 |  2033  (98)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP       |    20 |   600 |     3   (0)| 00:00:01 |
|*  3 |   INDEX RANGE SCAN | DEPTNOIND |     1 |    13 |   101  (99)| 00:00:01 |
--------------------------------------------------------------------------------

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

   3 - access("T4"."DEPTNO"=20)
       filter("EMP"."DEPTNO"="T4"."DEPTNO")

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


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       7754  consistent gets
       7724  physical reads
          0  redo size
       1374  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          9  rows processed
SCOTT@ rac1>select * from emp where  empno not in  (select empno from t4 where  t4.deptno=20) ;

9 rows selected.

Elapsed: 00:00:01.72

Execution Plan
----------------------------------------------------------
Plan hash value: 3504968978

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    20 |  1120 |  6130   (2)| 00:00:01 |
|*  1 |  HASH JOIN ANTI NA |      |    20 |  1120 |  6130   (2)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    20 |   600 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T4   |  1354K|    33M|  6120   (2)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - access("EMPNO"="EMPNO")
   3 - filter("T4"."DEPTNO"=20)

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


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



SCOTT@ rac1>select * from emp where  not exists (select empno from t4 where emp.deptno=t4.deptno and t4.deptno=20) ;

9 rows selected.

Elapsed: 00:00:05.45

Execution Plan
----------------------------------------------------------
Plan hash value: 3745834269

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |    20 |   860 |  2033  (98)| 00:00:01 |
|   1 |  NESTED LOOPS ANTI |           |    20 |   860 |  2033  (98)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP       |    20 |   600 |     3   (0)| 00:00:01 |
|*  3 |   INDEX RANGE SCAN | DEPTNOIND |     1 |    13 |   101  (99)| 00:00:01 |
--------------------------------------------------------------------------------

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

   3 - access("T4"."DEPTNO"=20)
       filter("EMP"."DEPTNO"="T4"."DEPTNO")

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


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

自然此前认为,not exists
和从前的参数一样的也是内需分景况的话,不过做了尝试测试之后发现。Not
exists 和not in 的抉择格局十分的简要,正是只选 not exists 因为 not
in加上了不会走索引。而not exists
会走。那样就限制了,即便要选择的话就玩命选拔not exists。
Not exists 的情致是,关联合检查询,重返除了关联子查询所得结果之外的值,
看如下的履行陈设和代价便得以看出来。两者的歧异。

相关文章