oracle not exists 很慢,Oracle not exists的等价写法

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 - access("T"."OBJECT_ID"="T1"."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