SQL优化之子查询-IN和EXISTS哪个快点

关注我的微信公众号：pythonislover,领取python,大数据，SQL优化相关视频资料！~

Python大数据与SQL优化笔 QQ群：771686295

 

 

 

对SQL优化，有一定理解的人都会知道，SQL优化的核心是减少物理IO的次数，说的通俗点，我们要尽量减少表的扫描次数，这里的表主要是大表。

 

今天说的子查询，我们可以理解为SQL包含IN, NOT IN, EXISTS, NOT EXISTS的语句， 以前经常有人会问IN和EXISTS到底怎么选，也有说EXISTS的性能更好，或者根据内表和外表的数据量来选择IN和EXISTS，其实在我看来，这些比较片面。哪个好，我们还是要看执行计划和执行时间。

 

当SQL中含有IN, NOT IN, EXISTS, NOT EXISTS的时候，优化器会尝试改写，为什么要改写呢？ 因为这些东西会导致一种叫Filter的东西，是不是很熟悉，前面的文章写到过哦，不知道的可以翻翻我前面的文章。

 

下面看看例子：

SQL> explain plan forselect EMPNO,ENAMEfrom empwhere exists(select DEPTNO from deptwhereemp.DEPTNO = dept.DEPTNOand dept.loc = 'BOSTON'union allselect DEPTNO from deptwhereemp.DEPTNO = dept.DEPTNOand dept.loc = 'DALLAS');  2    3    4    5    6    7    8    9   10   11   12   13   14Explained.
SQL> set linesize 400SELECT * FROM TABLE(dbms_xplan.display);SQL>
PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Plan hash value: 1230022885
-----------------------------------------------------------------------------------------| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |-----------------------------------------------------------------------------------------|   0 | SELECT STATEMENT              |         |     5 |    65 |     9   (0)| 00:00:01 ||*  1 |  FILTER                       |         |       |       |            |          ||   2 |   TABLE ACCESS FULL           | EMP     |    14 |   182 |     3   (0)| 00:00:01 ||   3 |   UNION-ALL                   |         |       |       |            |          ||*  4 |    TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    11 |     1   (0)| 00:00:01 ||*  5 |     INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|*  6 |    TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    11 |     1   (0)| 00:00:01 ||*  7 |     INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
   1 - filter( EXISTS ( (SELECT "DEPTNO" FROM "DEPT" "DEPT" WHERE              "DEPT"."DEPTNO"=:B1 AND "DEPT"."LOC"='BOSTON') UNION ALL  (SELECT "DEPTNO" FROM              "DEPT" "DEPT" WHERE "DEPT"."DEPTNO"=:B2 AND "DEPT"."LOC"='DALLAS')))   4 - filter("DEPT"."LOC"='BOSTON')
PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------   5 - access("DEPT"."DEPTNO"=:B1)   6 - filter("DEPT"."LOC"='DALLAS')   7 - access("DEPT"."DEPTNO"=:B1)



SQL> explain plan forselect EMPNO,ENAMEfrom empwhere DEPTNO in(select DEPTNO from