not exists可以改为left join + is null，可以看到改写前后执行计划一样，消耗资源一样，说明完全等价。
SQL> drop table test purge;
SQL> drop table test1 purge;
SQL> create table test as select * from dba_objects;
SQL> create table test1 as select * from dba_objects;
SQL> delete from test1 where rownum < 10;
SQL> commit;
SQL> select count(1) from test t where not exists(
? ? select 1 from test1 t1 where t1.object_id=t.object_id
? ? );
? COUNT(1)
----------
? ? ? ? 11
SQL> select count(1) from test t,test1 t1 where t.object_id=t1.object_id(+)
? ? and t1.object_id is null;
? COUNT(1)
----------
? ? ? ? 11
SQL> select * from test t where not exists(
? ? select 1 from test1 t1 where t1.object_id=t.object_id
? ? )
? ? minus
? ? select t.* from test t,test1 t1 where t.object_id=t1.object_id(+)
? ? and t1.object_id is null;
未选定行
SQL> select t.* from test t,test1 t1 where t.object_id=t1.object_id(+)
? ? and t1.object_id is null
? ? minus
? ? select * from test t where not exists(
? ? select 1 from test1 t1 where t1.object_id=t.object_id
? ? );
未选定行
SQL> set autotrace traceonly
SQL> select t.* from test t where not exists(
? ? select 1 from test1 t1 where t1.object_id=t.object_id
? ? );
已选择11行。
执行计划
----------------------------------------------------------
Plan hash value: 2726816538
--------------------------------------------------------------------------------------
| Id? | Operation? ? ? ? ? ? | Name? | Rows? | Bytes |TempSpc| Cost (%CPU)| Time? ? |
--------------------------------------------------------------------------------------
|? 0 | SELECT STATEMENT? ? |? ? ? | 72877 |? ? 15M|? ? ? |? 1109? (1)| 00:00:16 |
|*? 1 |? HASH JOIN RIGHT ANTI|? ? ? | 72877 |? ? 15M|? 1520K|? 1109? (1)| 00:00:16 |
|? 2 |? TABLE ACCESS FULL? | TEST1 | 61874 |? 785K|? ? ? |? 196? (1)| 00:00:03 |
|? 3 |? TABLE ACCESS FULL? | TEST? | 72877 |? ? 14M|? ? ? |? 197? (2)| 00:00:03 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
? 1 - access("T1"."OBJECT_ID"="T"."OBJECT_ID")
Note
-----
? - dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
? ? ? ? ? 7? recursive calls
? ? ? ? ? 0? db block gets
? ? ? 1142? consistent gets
? ? ? ? ? 0? physical reads
? ? ? ? ? 0? redo size
? ? ? 1577? bytes sent via SQL*Net to client
? ? ? ? 337? bytes received via SQL*Net from client
? ? ? ? ? 2? SQL*Net roundtrips to/from client
? ? ? ? ? 0? sorts (memory)
? ? ? ? ? 0? sorts (disk)
? ? ? ? 11? rows processed
SQL> select t.* from test t,test1 t1 where t.object_id=t1.object_id(+)
? 2? and t1.object_id is null;
已选择11行。
执行计划
----------------------------------------------------------
Plan hash value: 2726816538
--------------------------------------------------------------------------------------
| Id? | Operation? ? ? ? ? ? | Name? | Rows? | Bytes |TempSpc| Cost (%CPU)| Time? ? |
--------------------------------------------------------------------------------------
|? 0 | SELECT STATEMENT? ? |? ? ? | 72877 |? ? 15M|? ? ? |? 1109? (1)| 00:00:16 |
|*? 1 |? HASH JOIN RIGHT ANTI|? ? ? | 72877 |? ? 15M|? 1520K|? 1109? (1)| 00:00:16 |
|? 2 |? TABLE ACCESS FULL? | TEST1 | 61874 |? 785K|? ? ? |? 196? (1)| 00:00:03 |
|? 3 |? TABLE ACCESS FULL? | TEST? | 72877 |? ? 14M|? ? ? |? 197? (2)| 00:00:03 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
? 1 - acces