基础语法：
not in的写法中，内外表都要全表扫描
select count * from test where object_name not in (select object_name from test2);
not exists的写法中，内层表子查询可以走索引
select count * from test2 t2 where not exists(select 1 from test t1 where t1.object_name=t2.object_name );
（object_name上建有有索引）
执行计划：
再来看一下CBO模式下的情况：
SQL> select * from test2
2 where table_name not in
3 ( select object_name from test1 where object_name like 'A%') ;
2950 rows selected.
Elapsed: 00:00:13.93
Execution Plan
----------------------------------------------------------
Plan hash value: 833525739
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1576 | 315K| 6364 (1)| 00:01:17 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| TEST2 | 1577 | 315K| 14 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_OBJECT_NAME | 1 | 25 | 8 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "TEST1" "TEST1" WHERE
"OBJECT_NAME" LIKE 'A%' AND LNNVL("OBJECT_NAME"<>:B1)))
3 - access("OBJECT_NAME" LIKE 'A%')
filter("OBJECT_NAME" LIKE 'A%' AND LNNVL("OBJECT_NAME"<>:B1))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
60806 consistent gets
29 physical reads
1104 redo size
165661 bytes sent via SQL*Net to client
2541 bytes received via SQL*Net from client
198 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2950 rows processed
SQL> select * from test2
2 where not exists
3 ( select 1 from test1 where test1.object_name = test2.table_name and test1.object_name like 'A%') ;
2950 rows selected.
Elapsed: 00:00:00.19
Execution Plan
----------------------------------------------------------
Plan hash value: 349475409
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1538 | 345K| 23 (5)| 00:00:01 |
|* 1 | HASH JOIN RIGHT ANTI| | 1538 | 345K| 23 (5)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_OBJECT_NAME | 1259 | 31475 | 8 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | TEST2 | 1577 | 315K| 14 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("TEST1"."OBJECT_NAME"="TEST2"."TABLE_NAME")
2 - access("TEST1"."OBJECT_NAME" LIKE 'A%')
filter("TEST1"."OBJECT_NAME" LIKE 'A%')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
314 consistent gets
0 physical reads
0 redo size
165661 bytes sent via SQL*Net to client
2541 bytes received via SQL*Net from client
198 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2950 rows processed
在CBO模式下，not in采用的是filter，对外层表做全表扫描，再去filter内层查询结果集。not exists采用内层查询结果集作hash join连接。
从结果明显可以看到，not exists效率比较高。把内层和外层的表对换一下，结果也是相同的，not exists的效率明显高很多。