Not exists 的意味是,在此以前到未来一共写了三篇有关 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

 

 

最后来写个总括,此前到昨天一共写了叁篇有关 exists
的篇章,自觉有点麻烦。但也是和睦不停学习的进度。大多事物依然需求团结不停的去操作,思虑。总结。言归正传。
exists 和 in
在两张表大约大小的景况下,功能,速度,是不会大有不同的。
在一大学一年级小的情事下是存在功效上的差异的。纵然她们的试行安排有异常的大概率是一样的。
exists 更适用于 子表大,in 适用于父表大。具体请看第一篇。
not exsits 与not in 相比较,not in
从前已经看过了,并不会走相关的目录。所以,尽量采取 not exists。
另,那三个查询中只要有null值,会回到全体的结果集。所以注意写语句的时候尽量避开null值。
在这里祝大家,新禧欢欣吧,给和睦定二个小目标。只如果专门的职业日,每日百折不挠写一篇博客。努力学习,争取早日成为一个友好所期待的样子!加油201八.

末尾来写个小结,此前到前些天总结写了3篇有关 exists
的稿子,自觉有点麻烦。但也是温馨不停学习的历程。多数事物照旧须求和谐不停的去操作,思量。计算。言归正传。
exists 和 in
在两张表大致大小的事态下,功用,速度,是不会距离一点都不小的。
在一大学一年级小的情况下是存在效能上的出入的。就算她们的实施安排有非常的大希望是如出1辙的。
exists 更适用于 子表大,in 适用于父表大。具体请看第一篇。
not exsits 与not in 比较,not in
从前早已看过了,并不会走相关的目录。所以,尽量选用 not exists。
另,那多个查询中假使有null值,会回到全部的结果集。所以注意写语句的时候尽量避开null值。
在那里祝我们,新禧快乐吧,给本人定三个小目的。只倘诺工作日,每日持之以恒写壹篇博客。努力学习,争取早日成为一个友好所企盼的标准!加油201八.

自然此前感到,not exists
和事先的参数同样的也是必要分意况来说,不过做了实验测试之后察觉。Not
exists 和not in 的选用格局充足的总结,正是只选 not exists 因为 not
in加上了不会走索引。而not exists
会走。那样就限制了,假诺要利用的话就尽量使用not exists。
Not exists 的情致是,关联合检查询,重临除了关联子查询所得结果之外的值,
看如下的施行布置和代价便足以看出来。两者的差别。

本来之前以为,not exists
和前边的参数同样的也是索要分情状来讲,可是做了试验测试之后察觉。Not
exists 和not in 的取舍格局丰硕的简练,正是只选 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
网站地图xml地图