Oracle: Is OUTER JOIN Better Than NOT EXISTS?

I’ve been told that using NOT EXISTS in (Oracle) SQL is a bad idea, and that a way to overcome this problem is to collect the non-matching rows with an OUTER JOIN. So I decided to check if it is true.

Establishing the Test Case

In order to start, here is my test case:

create table t1(id number,    constraint t1_pk primary key(id));    create table t2(id number);  begin   for i in 1..100 loop     insert into t1 values(i);   end loop;   commit; end;  begin   for i in 1..100000 loop     insert into t2       values(mod(i,97));   end loop;   commit; end; /  create index t2_idx on t2(id);  exec dbms_stats.gather_table_stats(USER,'T1');  exec dbms_stats.gather_table_stats(USER,'T2'); 

Analyzing Execution Plans: NOT EXISTS vs. OUTER JOIN

First, I checked what I’d been told, i.e. that the OUTER JOIN is more efficient than the NOT EXISTS. In order to do that, I wrote a simple SELECT and displayed the plan for both syntaxes (my database is 11.1.0.6 on Linux 32-bits). As I assumed, it’s not the case. In fact, both orders took the same plan.

Execution Plan with NOT EXISTS

explain plan for   select id from t1 a    where not exists     (select 1 from t2 b where b.id=a.id);  select * from table(dbms_xplan.display);  PLAN_TABLE_OUTPUT ----------------------------------------------------------------- Plan hash value: 1906534000  ----------------------------------------------------------------- | Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| ----------------------------------------------------------------- |   0 | SELECT STATEMENT   |       |    96 |   480 |    46   (5)| |* 1 |  HASH JOIN ANTI    |       |    96 |   480 |    46   (5)| |   2 |   INDEX FULL SCAN  | T1_PK |   100 |   300 |     1   (0)| |   3 |   TABLE ACCESS FULL| T2    |   100K|   195K|    44   (3)| -----------------------------------------------------------------  Predicate Information (identified by operation id): ---------------------------------------------------    1 - access("B"."ID"="A"."ID") 

Execution Plan with OUTER JOIN

explain plan for   select a.id from t1 a, t2 b    where a.id=b.id(+)      and b.id is null;  select * from table(dbms_xplan.display);  PLAN_TABLE_OUTPUT ----------------------------------------------------------------- Plan hash value: 1906534000  ----------------------------------------------------------------- | Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| ----------------------------------------------------------------- |   0 | SELECT STATEMENT   |       |     3 |    15 |    46   (5)| |* 1 |  HASH JOIN ANTI    |       |     3 |    15 |    46   (5)| |   2 |   INDEX FULL SCAN  | T1_PK |   100 |   300 |     1   (0)| |   3 |   TABLE ACCESS FULL| T2    |   100K|   195K|    44   (3)| ----------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    1 - access("A"."ID"="B"."ID") 

The Verdict: Identical Optimization

I know — the real way to check that both queries are equivalent is to trace the plan generation with a 10053 event. (I cannot explain this, so I’ll leave that to you.)

However, the original query I’ve been told to rewrite was not a SELECT, but the DELETE below:

delete from t1 a    where not exists     (select 1 from t2 b where b.id=a.id); 

I haven’t yet found how to rewrite it in a way that makes it more efficient with an OUTER JOIN. I’ll be happy if someone can help me, at least to find the syntax, if not to enhance response time.

Oracle Database Consulting Services

Ready to optimize your Oracle Database for the future?