事先到今天合计写了3篇有关 exists,此前到后天共计写了3篇关于 exists

之前到现在一共写了三篇关于 exists,之前到现在一共写了三篇关于 exists

当然此前以为,not exists
和事先的参数一样的也是索要分景况来说,可是做了试验测试之后察觉。Not
exists 和not in 的精选情势丰硕的简易,正是只选 not exists 因为 not
in加上了不会走索引。而not exists
会走。那样就限制了,假如要运用的话就玩命使用not exists。
Not exists 的趣味是,关联合检查询,再次回到除了关联子查询所得结果之外的值,
看如下的举办安插和代价便能够看出来。两者的异样。

理所当然在此之前觉得,not exists
和事先的参数1样的也是索要分情形来说,不过做了实验测试之后察觉。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
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

最终来写个小结,以前到后天一共写了3篇有关 exists
的篇章,自觉有点麻烦。但也是和谐不停学习的进度。很多东西依然须求协调不停的去操作,思虑。计算。言归正传。
exists 和 in
在两张表大概大小的事态下,效用,速度,是不会距离非常大的。
在一大学一年级小的场合下是存在作用上的歧异的。固然她们的施行安排有望是千篇壹律的。
exists 更适用于 子表大,in 适用于父表大。具体请看第2篇。
not exsits 与not in 相比较,not in
从前已经看过了,并不会走相关的目录。所以,尽量使用 not exists。
另,那八个查询中一经有null值,会重回全体的结果集。所以注意写语句的时候尽量避开null值。
在此处祝大家,新春高兴吧,给协调定三个小目的。只假设工作日,每日百折不挠写1篇博客。努力学习,争取早日成为1个投机所期望的旗帜!加油201⑧.

末尾来写个小结,此前到今天共计写了3篇关于 exists
的稿子,自觉有点麻烦。但也是和谐不停学习的进程。很多事物依然必要团结不停的去操作,思索。计算。言归正传。
exists 和 in
在两张表差不离大小的情况下,作用,速度,是不会离开一点都不小的。
在一大学一年级小的意况下是存在效用上的差其他。固然他们的推行安顿有相当的大可能率是同样的。
exists 更适用于 子表大,in 适用于父表大。具体请看第二篇。
not exsits 与not in 相比,not in
此前已经看过了,并不会走相关的目录。所以,尽量使用 not exists。
另,那五个查询中1经有null值,会回到全体的结果集。所以注意写语句的时候尽量避开null值。
在此间祝我们,新岁开心吧,给自个儿定1个小目的。只假使工作日,天天百折不挠写1篇博客。努力学习,争取早日成为二个团结所希望的样子!加油2018.