Oracle not exists equivalent statement, oracleexists
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)
----------
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;
Unselected row
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
);
Unselected row
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 rows have been selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2726816538
Bytes --------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | TempSpc | Cost (% CPU) | Time |
Bytes --------------------------------------------------------------------------------------
| 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 |
Bytes --------------------------------------------------------------------------------------
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 cballs
0 db block gets
1142 consistent gets
0 physical reads
0 redo size
1577 bytes sent via SQL * Net to client
337 bytes encoded ed 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 rows have been selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2726816538
Bytes --------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | TempSpc | Cost (% CPU) | Time |
Bytes --------------------------------------------------------------------------------------
| 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 |
Bytes --------------------------------------------------------------------------------------
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 cballs
0 db block gets
1142 consistent gets
0 physical reads
0 redo size
1577 bytes sent via SQL * Net to client
337 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
11 rows processed
