查看: 2006|回复: 0

[Oracle数据库] Not in 和 not exists

发表于 2018-1-10 08:00:01

本来之前以为,not exists 和之前的参数一样的也是需要分情况来说,但是做了实验测试之后发现。Not exists 和not in 的选择方法十分的简单,就是只选 not exists 因为 not in加上了不会走索引。而not exists 会走。这样就限定了,如果要使用的话就尽可能使用not exists。
Not exists 的意思是,关联查询,返回除了关联子查询所得结果之外的值,
看如下的执行计划和代价便可以看出来。两者的差距。

  1. SCOTT@ rac1>select * from emp where empno not in (select empno from t4 where t4.deptno=20) ;
  2. 9 rows selected.
  3. Elapsed: 00:00:01.72
  4. Execution Plan
  5. ----------------------------------------------------------
  6. Plan hash value: 3504968978
  7. ---------------------------------------------------------------------------
  8. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  9. ---------------------------------------------------------------------------
  10. | 0 | SELECT STATEMENT | | 20 | 1120 | 6130 (2)| 00:00:01 |
  11. |* 1 | HASH JOIN ANTI NA | | 20 | 1120 | 6130 (2)| 00:00:01 |
  12. | 2 | TABLE ACCESS FULL| EMP | 20 | 600 | 3 (0)| 00:00:01 |
  13. |* 3 | TABLE ACCESS FULL| T4 | 1354K| 33M| 6120 (2)| 00:00:01 |
  14. ---------------------------------------------------------------------------
  15. Predicate Information (identified by operation id):
  16. ---------------------------------------------------
  17. 1 - access("EMPNO"="EMPNO")
  18. 3 - filter("T4"."DEPTNO"=20)
  19. Note
  20. -----
  21. - dynamic sampling used for this statement (level=2)
  22. Statistics
  23. ----------------------------------------------------------
  24. 0 recursive calls
  25. 1 db block gets
  26. 43236 consistent gets
  27. 21573 physical reads
  28. 0 redo size
  29. 1391 bytes sent via SQL*Net to client
  30. 524 bytes received via SQL*Net from client
  31. 2 SQL*Net roundtrips to/from client
  32. 0 sorts (memory)
  33. 0 sorts (disk)
  34. 9 rows processed
  35. SCOTT@ rac1>select * from emp where not exists (select empno from t4 where emp.deptno=t4.deptno and t4.deptno=20) ;
  36. 9 rows selected.
  37. Elapsed: 00:00:05.45
  38. Execution Plan
  39. ----------------------------------------------------------
  40. Plan hash value: 3745834269
  41. --------------------------------------------------------------------------------
  42. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  43. --------------------------------------------------------------------------------
  44. | 0 | SELECT STATEMENT | | 20 | 860 | 2033 (98)| 00:00:01 |
  45. | 1 | NESTED LOOPS ANTI | | 20 | 860 | 2033 (98)| 00:00:01 |
  46. | 2 | TABLE ACCESS FULL| EMP | 20 | 600 | 3 (0)| 00:00:01 |
  47. |* 3 | INDEX RANGE SCAN | DEPTNOIND | 1 | 13 | 101 (99)| 00:00:01 |
  48. --------------------------------------------------------------------------------
  49. Predicate Information (identified by operation id):
  50. ---------------------------------------------------
  51. 3 - access("T4"."DEPTNO"=20)
  52. filter("EMP"."DEPTNO"="T4"."DEPTNO")
  53. Note
  54. -----
  55. - dynamic sampling used for this statement (level=2)
  56. Statistics
  57. ----------------------------------------------------------
  58. 0 recursive calls
  59. 0 db block gets
  60. 7754 consistent gets
  61. 7724 physical reads
  62. 0 redo size
  63. 1374 bytes sent via SQL*Net to client
  64. 524 bytes received via SQL*Net from client
  65. 2 SQL*Net roundtrips to/from client
  66. 0 sorts (memory)
  67. 0 sorts (disk)
  68. 9 rows processed
复制代码

最后来写个总结,之前到现在一共写了三篇关于 exists 的文章,自觉有点繁琐。但也是自己不断学习的过程。很多东西还是需要自己不断的去操作,思考。总结。言归正传。
exists 和 in 在两张表差不多大小的情况下,效率,速度,是不会相差很大的。
在一大一小的情况下是存在效率上的差别的。尽管他们的执行计划有可能是相同的。 exists 更适用于 子表大,in 适用于父表大。具体请看第二篇。
not exsits 与not in 相比,not in 之前已经看过了,并不会走相关的索引。所以,尽量使用 not exists。
另,这两个查询中如果有null值,会返回全部的结果集。所以注意写语句的时候尽量避开null值。
在这里祝大家,新年快乐吧,给自己定一个小目标。只要是工作日,每天坚持写一篇博客。努力学习,争取早日变成一个自己所期待的样子!加油2018.



回复

使用道具 举报