Not exists 的意趣是,in加上了不会走索引

澳门金冠开户,理所当然以前认为,not exists
和事先的参数一样的也是索要分情状来说,可是做了尝试测试之后察觉。Not
exists 和not in 的挑选方式11分的简练,正是只选 not exists 因为 not
in加上了不会走索引。而not exists
会走。这样就限制了,即使要运用的话就尽也许选取not exists。
Not exists 的情趣是,关联合检查询,再次回到除了关联子查询所得结果之外的值,
看如下的实践安插和代价便能够看出来。两者的差别。

理所当然从前觉得,not exists
和事先的参数一样的也是索要分景况来说,然而做了实验测试之后察觉。Not
exists 和not in 的选拔形式13分的简练,正是只选 not exists 因为 not
in加上了不会走索引。而not exists
会走。那样就限制了,若是要接纳的话就尽也许选拔not exists。
Not exists 的意趣是,关联合检查询,再次回到除了关联子查询所得结果之外的值,
看如下的实施计划和代价便得以看出来。两者的歧异。

 

 

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

最终来写个总计,在此之前到现行反革命壹共写了叁篇有关 exists
的篇章,自觉有点麻烦。但也是本身不停学习的经过。很多东西依然须求协调不停的去操作,考虑。总计。言归正传。
exists 和 in
在两张表大致大小的意况下,功用,速度,是不会离开一点都不小的。
在一大学一年级小的动静下是存在效用上的差异的。即使她们的施行布置有不小希望是如出一辙的。
exists 更适用于 子表大,in 适用于父表大。具体请看第一篇。
not exsits 与not in 相比,not in
从前已经看过了,并不会走相关的目录。所以,尽量利用 not exists。
另,那五个查询中要是有null值,会回来全体的结果集。所以注意写语句的时候尽量回避null值。
在那边祝大家,新年欢畅吧,给协调定贰个小目的。只如若工作日,每日百折不回写1篇博客。努力学习,争取早日成为3个要好所期待的指南!加油201八.

最后来写个计算,在此之前到今后总结写了叁篇关于 exists
的小说,自觉有点麻烦。但也是温馨不停学习的经过。很多事物照旧需求自身不停的去操作,思索。总计。言归正传。
exists 和 in
在两张表大致大小的情事下,功效,速度,是不会分裂的。
在一大学一年级小的情景下是存在效能上的差异的。尽管他们的执行安顿有望是同壹的。
exists 更适用于 子表大,in 适用于父表大。具体请看第一篇。
not exsits 与not in 比较,not in
从前早已看过了,并不会走相关的目录。所以,尽量利用 not exists。
另,那三个查询中若是有null值,会回来全体的结果集。所以注意写语句的时候尽量回避null值。
在这边祝我们,新禧高兴吧,给本身定一个小指标。只若是工作日,每一日锲而不舍写一篇博客。努力学习,争取早日成为三个温馨所期望的榜样!加油201八.

相关文章