If the column in one of the tables is nullable, and in the other table is not nullable, then Oracle uses the same execution plan regardless of whether you use NOT EXISTS or a join:
Code:
SQL > create table cuk1(id number, 2 constraint cuk1_pk primary key(id)); Table created. SQL > create table cuk2(id number); Table created. SQL > begin 2 for i in 1..100 loop 3 insert into cuk1 values(i); 4 end loop; 5 commit; 6 end; 7 / PL/SQL procedure successfully completed. SQL > begin 2 for i in 1..100000 loop 3 insert into cuk2 4 values(mod(i,97)); 5 end loop; 6 commit; 7 end; 8 / PL/SQL procedure successfully completed. SQL > create index cuk2_idx on cuk2(id); Index created. SQL > exec dbms_stats.gather_table_stats(USER,'CUK1'); PL/SQL procedure successfully completed. SQL > exec dbms_stats.gather_table_stats(USER,'CUK2'); PL/SQL procedure successfully completed. SQL > -- Not exists check..... SQL > explain plan for 2 select id from cuk1 a 3 where not exists 4 (select 1 from cuk2 b where b.id=a.id); Explained. SQL > select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 1819530608 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 4 | 24 | 50 (4)| 00:00:01 | |* 1 | HASH JOIN ANTI | | 4 | 24 | 50 (4)| 00:00:01 | | 2 | INDEX FULL SCAN | CUK1_PK | 100 | 300 | 1 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| CUK2 | 100K| 292K| 48 (3)| 00:00:01 | ------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("B"."ID"="A"."ID") 15 rows selected. SQL > -- Outer join check SQL > explain plan for 2 select a.id 3 from cuk1 a 4 left outer join cuk2 b on (a.id = b.id) 5 where b.id is null; Explained. SQL > select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 1819530608 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 4 | 24 | 50 (4)| 00:00:01 | |* 1 | HASH JOIN ANTI | | 4 | 24 | 50 (4)| 00:00:01 | | 2 | INDEX FULL SCAN | CUK1_PK | 100 | 300 | 1 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| CUK2 | 100K| 292K| 48 (3)| 00:00:01 | ------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("A"."ID"="B"."ID") 15 rows selected. SQL > drop table cuk1; Table dropped. SQL > drop table cuk2; Table dropped.
Comment