【SQL优化】不要使用not in

准备测试数据：

SQL> create table tmp01 as select object_id,object_name from dba_objects;


表已创建。


已用时间:  00: 00: 03.35
SQL> create table tmp02 as select object_id,object_name from dba_objects where mod(object_id,2)=0;


表已创建。


已用时间:  00: 00: 00.21
SQL> insert into tmp01 select * from tmp01;


已创建72522行。


已用时间:  00: 00: 00.39
SQL> insert into tmp01 select * from tmp01;


已创建145044行。


已用时间:  00: 00: 00.20
SQL> insert into tmp01 select * from tmp01;


已创建290088行。


已用时间:  00: 00: 00.29
SQL> insert into tmp01 select * from tmp01;


已创建580176行。


已用时间:  00: 00: 00.76
SQL> commit;


提交完成。


已用时间:  00: 00: 00.01
SQL> insert into tmp02 select * from tmp02;


已创建36151行。


已用时间:  00: 00: 00.17
SQL> insert into tmp02 select * from tmp02;


已创建72302行。


已用时间:  00: 00: 00.06
SQL> insert into tmp02 select * from tmp02;


已创建144604行。


已用时间:  00: 00: 00.14
SQL> commit;


提交完成。


已用时间:  00: 00: 00.01
SQL> create index idx_tmp01_id on tmp01(object_id);


索引已创建。


已用时间:  00: 00: 00.87
SQL> create index idx_tmp02_id on tmp02(object_id);


索引已创建。


已用时间:  00: 00: 00.20

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',tabname => 'TMP01',cascade => true);


PL/SQL 过程已成功完成。


已用时间:  00: 00: 04.89
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',tabname => 'TMP02',cascade => true);


PL/SQL 过程已成功完成。


已用时间:  00: 00: 00.53
SQL>

我们要查询object_id在tmp01里面而不在tmp02里面的记录，怎么写？

开发一般会这样写，因为这样很容易理解，也很容易想到：

SQL> set autot trace exp stat
SQL> select count(*)
  2  from tmp01
  3  where tmp01.object_id not in (select tmp02.object_id from tmp02);


已用时间:  00: 00: 00.34


执行计划
----------------------------------------------------------
Plan hash value: 1947072100


------------------------------------------------------------------------------------------
| Id  | Operation                | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |       |     1 |    10 |       |  3076   (1)| 00:00:37 |
|   1 |  SORT AGGREGATE          |       |     1 |    10 |       |            |          |
|*  2 |   HASH JOIN RIGHT ANTI NA|       |   586K|  5723K|  4808K|  3076   (1)| 00:00:37 |
|   3 |    TABLE ACCESS FULL     | TMP02 |   289K|  1412K|       |   381   (1)| 00:00:05 |
|   4 |    TABLE ACCESS FULL     | TMP01 |  1160K|  5665K|       |  1516   (1)| 00:00:19 |
------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   2 - access("TMP01"."OBJECT_ID"="TMP02"."OBJECT_ID")




统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       6927  consistent gets
          0  physical reads
          0  redo size
        425  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


SQL>

其实可以使用左外连接或者not exists：

SQL> select count(*)
  2  from tmp01 left join tmp02
  3  on tmp01.object_id = tmp02.object_id
  4  where tmp02.object_id is null;


已用时间:  00: 00: 00.40


执行计划
----------------------------------------------------------
Plan hash value: 2699572186


-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |              |     1 |    10 |       |  2872   (1)| 00:00:35 |
|   1 |  SORT AGGREGATE        |              |     1 |    10 |       |            |          |
|*  2 |   HASH JOIN RIGHT ANTI |              |   586K|  5723K|  4808K|  2872   (1)| 00:00:35 |
|   3 |    INDEX FAST FULL SCAN| IDX_TMP02_ID |   289K|  1412K|       |   177   (2)| 00:00:03 |
|   4 |    TABLE ACCESS FULL   | TMP01        |  1160K|  5665K|       |  1516   (1)| 00:00:19 |
-----------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   2 - access("TMP01"."OBJECT_ID"="TMP02"."OBJECT_ID")




统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       6195  consistent gets
          0  physical reads
          0  redo size
        425  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


SQL>

巧妙地走索引，并且一致读取也略低。

SQL> create table t_notin1(name varchar2(10));


表已创建。


SQL> create table t_notin2(name varchar2(10));


表已创建。


SQL> insert into t_notin1 values('t1');


已创建 1 行。


SQL> insert into t_notin1 values('t2');


已创建 1 行。


SQL> insert into t_notin2 values('');


已创建 1 行。


SQL> insert into t_notin2 values('');


已创建 1 行。


SQL> insert into t_notin2 values('t1');


已创建 1 行。


SQL> commit;


提交完成。


SQL>

SQL> select * from t_notin1;


NAME
----------
t1
t2


SQL> select nvl(name,'NULL') from t_notin2;


NVL(NAME,'
----------
NULL
NULL
t1

SQL>

找出在t_notin1中而不在t_notin2中的记录，注意t_notin2中有null记录。怎么写？not in？

SQL> select * from t_notin1 where name not in (select name from t_notin2);


未选定行


SQL>

无记录返回，为什么？not in必须要求where的子查询不返回null值。此时必须改用外连接：

SQL> select t_notin1.name from t_notin1 left join t_notin2 on t_notin1.name=t_notin2.name where t_notin2.name is null;


NAME
----------
t2


或者使用not exists方法：
SQL> select * from t_notin1 where not exists (select 1 from t_notin2 where t_notin1.name=t_notin2.name);


NAME
----------
t2


SQL>