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. (more…)