Oracle: Is OUTER JOIN Better Than NOT EXISTS?
Posted by Grégory Guillou on Jan 7, 2008
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.
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');
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. Read the rest of this entry . . .
