准备两个测试表

SQL> conn chf/oracle
Connected.
SQL> select * from v$version;
BANNER
----------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SQL> create table t_xifenfei
  2  as select * from dba_objects;
Table created.
SQL> select count(*) from t_xifenfei;
  COUNT(*)
----------
     86259
SQL> create table t_xifenfei1
  2  as select * from dba_objects;
Table created.
SQL> select count(*) from t_xifenfei1;
  COUNT(*)
----------
     86260
--删除部分记录，用来做not in的内部表
SQL> delete from t_xifenfei where object_id>86200;
918 rows deleted.
SQL> commit;
Commit complete.

查询_optimizer_null_aware_antijoin隐含参数默认值

SQL> conn / as sysdba
Connected.
SQL> col name for a52
SQL> col value for a24
SQL> col description for a50
set linesize 150
select a.ksppinm name,b.ksppstvl value,a.ksppdesc description
  from x$ksppi a,x$ksppcv b
 where a.inst_id = USERENV ('Instance')
   and b.inst_id = USERENV ('Instance')
   and a.indx = b.indx
   and upper(a.ksppinm) LIKE upper('%&param%')
order by name
SQL> SQL>   2    3    4    5    6    7    8  /
Enter value for param: _optimizer_null_aware_antijoin
old   6:    and upper(a.ksppinm) LIKE upper('%&param%')
new   6:    and upper(a.ksppinm) LIKE upper('%_optimizer_null_aware_antijoin%')
NAME                                                 VALUE                    DESCRIPTION
---------------------------------------------------- ------------------------ -----------------------------
_optimizer_null_aware_antijoin                       TRUE                     null-aware antijoin parameter

_optimizer_null_aware_antijoin从11.1.0.6开始引进,默认为true

_optimizer_null_aware_antijoin为true,执行not in

SQL> conn chf/oracle
Connected.
SQL> set autot trace
SQL> set timing on
SQL> set lines 150
SQL> set pages 1000
SQL>  select count(*) from t_xifenfei1 where object_id not in(select object_id from t_xifenfei);
Elapsed: 00:00:00.09
Execution Plan
----------------------------------------------------------
Plan hash value: 4048525918
----------------------------------------------------------------------------------------
| Id  | Operation                | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |             |     1 |    10 |   688   (1)| 00:00:09 |
|   1 |  SORT AGGREGATE          |             |     1 |    10 |            |          |
|*  2 |   HASH JOIN RIGHT ANTI NA|             |  1137 | 11370 |   688   (1)| 00:00:09 |
|   3 |    TABLE ACCESS FULL     | T_XIFENFEI  | 85341 |   416K|   344   (1)| 00:00:05 |
|   4 |    TABLE ACCESS FULL     | T_XIFENFEI1 | 86260 |   421K|   344   (1)| 00:00:05 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"="OBJECT_ID")
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       2472  consistent gets
          0  physical reads
          0  redo size
        527  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

_optimizer_null_aware_antijoin为false,执行not in

SQL> alter session set "_optimizer_null_aware_antijoin"=false;
Session altered.
Elapsed: 00:00:00.00
SQL> select count(*) from t_xifenfei1 where object_id not in(select object_id from t_xifenfei);
Elapsed: 00:02:29.64
Execution Plan
----------------------------------------------------------
Plan hash value: 2503880249
-----------------------------------------------------------------------------------
| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |     1 |     5 |    25M  (1)| 86:20:57 |
|   1 |  SORT AGGREGATE     |             |     1 |     5 |            |          |
|*  2 |   FILTER            |             |       |       |            |          |
|   3 |    TABLE ACCESS FULL| T_XIFENFEI1 | 86260 |   421K|   344   (1)| 00:00:05 |
|*  4 |    TABLE ACCESS FULL| T_XIFENFEI  |     1 |     5 |   344   (1)| 00:00:05 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter( NOT EXISTS (SELECT 0 FROM "T_XIFENFEI" "T_XIFENFEI" WHERE
              LNNVL("OBJECT_ID"<>:B1)))
   4 - filter(LNNVL("OBJECT_ID"<>:B1))
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
   52982891  consistent gets
          0  physical reads
          0  redo size
        527  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

这里很明显,当 _optimizer_null_aware_antijoin为false的时候not in效率非常低(当in里面记录多，使用FILTER效率肯定低下).

_optimizer_null_aware_antijoin为false,执行not exists

SQL> alter session set "_optimizer_null_aware_antijoin"=false;
Session altered.
SQL>select count(*) from t_xifenfei1 b where not exists
 2  (select 1 from t_xifenfei a where a.object_id=b.object_id);
Elapsed: 00:00:00.07
Execution Plan
----------------------------------------------------------
Plan hash value: 2976307246
-------------------------------------------------------------------------------------
| Id  | Operation             | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |     1 |    10 |   688   (1)| 00:00:09 |
|   1 |  SORT AGGREGATE       |             |     1 |    10 |            |          |
|*  2 |   HASH JOIN RIGHT ANTI|             |  1137 | 11370 |   688   (1)| 00:00:09 |
|   3 |    TABLE ACCESS FULL  | T_XIFENFEI  | 85341 |   416K|   344   (1)| 00:00:05 |
|   4 |    TABLE ACCESS FULL  | T_XIFENFEI1 | 86260 |   421K|   344   (1)| 00:00:05 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("A"."OBJECT_ID"="B"."OBJECT_ID")
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2472  consistent gets
          0  physical reads
          0  redo size
        527  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

对于11g的版本可以通过_optimizer_null_aware_antijoin参数开启NULL-aware Anti join特性来提高not in的效率,对于11g以下版本可以通过not exists来提高效率