Oracle +, NULL ANTI-, .
create table ttt1 as select mod(rownum,10) id from dual connect by level <= 50000;
insert into ttt1 select 10 from dual;
create table ttt2 as select mod(rownum,10) id from dual connect by level <= 50000;
select ttt1.id
from ttt1
left join ttt2
on ttt1.id = ttt2.id
where ttt2.id is null;
select * from ttt1 where id not in (select id from ttt2);
Final query after transformations:******* UNPARSED QUERY IS *******
10053, ( "=" , ANTI)
SELECT "TTT1"."ID" "ID" FROM "TTT2" "TTT2","TTT1" "TTT1" WHERE "TTT1"."ID"="TTT2"."ID"
| Id | Operation | Name |
| 0 | SELECT STATEMENT | |
| 1 | HASH JOIN ANTI | |
| 2 | TABLE ACCESS FULL| TTT1 |
| 3 | TABLE ACCESS FULL| TTT2 |
, , ,
select
ttt1.id
from ttt1, ttt2
where ttt1.id = ttt2.id(+) and ttt2.id is null;
| Id | Operation | Name |
| 0 | SELECT STATEMENT | |
| 1 | FILTER | |
| 2 | HASH JOIN OUTER | |
| 3 | TABLE ACCESS FULL| TTT1 |
| 4 | TABLE ACCESS FULL| TTT2 |
.
ANSI , .
select
ttt1.id
from ttt1
left join ttt2
on ttt1.id = ttt2.id
where ttt2.id is null;
select * from table(dbms_xplan.display_cursor(format => 'BASIC'));
| Id | Operation | Name |
| 0 | SELECT STATEMENT | |
| 1 | VIEW | |
| 2 | FILTER | |
| 3 | MERGE JOIN OUTER | |
| 4 | TABLE ACCESS FULL | TTT1 |
| 5 | BUFFER SORT | |
| 6 | VIEW | VW_LAT_2131DCCF |
| 7 | TABLE ACCESS FULL| TTT2 |
, , Oracle ANTI-, , . "- + rule", CBO , .
PS. SEMI , +, CBO.