Oraclenotexists equivalence statement

Oraclenotexists equivalence statement

Not exists can be changed to left join + is null. It can be seen that the execution plan is the same before and after rewriting, and the resource consumption is the same, which means it is equivalent.

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) ---------- 11SQL> select count (1) from test t, test1 t1 where t. object_id = t1. Object_id (+) and t1.object _ id is null; COUNT (1) ---------- 11SQL> select * from test t where not exists (select 1 from test1 t1 where t1.object _ id = t. object_id) minusselect t. * from test t, test1 t1 where t. object_id = t1.object _ id (+) and t1.object _ id is null; no SQL> select t is selected. * from test t, test1 t1 where t. object_id = t1.object _ id (+) and t1.object _ id is nullminusselect * from test t where not exists (select 1 f Rom test1 t1 where t1.object _ id = t. object_id); select t. * from test t where not exists (select 1 from test1 t1 where t1.object _ id = t. object_id); 11 rows have been selected. Execution Plan -------------------------------------------------------- Plan hash value: 2726816538 Bytes | Id | Operation | Name | Rows | Bytes | TempSpc | Cost (% CPU) | Time | period | 0 | select statement | 72877 | 15 M | 1109 (1) | 00:00:16 | * 1 | HAS H 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 | identified Predicate Information (identified by operation id ): --------------------------------------------------- 1-access ("T1". "OBJECT_ID" = "T ". "OBJECT_ID") Note ------ dynamic sampling used for this statement (level = 2) statistics 7 recursive calls0 db block gets1142 consistent gets0 physical reads0 redo bytes sent via SQL * Net to client337 bytes encoded ed via SQL * Net from client2 SQL * Net roundtrips to/from client0 sorts (memory) 0 sorts (disk) 11 rows processedSQL> sel Ect t. * from test t, test1 t1 where t. object_id = t1.object _ id (+) 2 and t1.object _ id is null; 11 rows have been selected. Execution Plan -------------------------------------------------------- Plan hash value: 2726816538 Bytes | Id | Operation | Name | Rows | Bytes | TempSpc | Cost (% CPU) | Time | minute | 0 | select statement | 72877 | 15 M | 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 | identified Predicate Information (identified by operation id ): ------------------------------------------------- 1-access ("T ". "OBJECT_ID" = "T1 ". "OBJECT_ID") Note ------ dynamic sampling used for this statement (level = 2) statistics 7 recursive calls0 db block gets1142 consistent gets0 physical reads0 redo bytes sent via SQL * Net to client337 bytes encoded ed via SQL * Net from client2 SQL * Net roundtrips to/from client0 sorts (memory) 0 sorts (disk) 11 rows processed