Au fil des années, je tente d’expliquer que les versions modernes d’Oracle arrivent à trouver des plans identiques pour des requêtes sémantiquement identiques… Vaste sujet que la sémantique surtout si vous considérez les valeurs NULL ou les valeurs distinctes ; enfin, pitié : arrêtez de me dire qu’il vaut mieux écrire NOT EXISTS qu’une jointure externe ; ou, comme aujourd’hui que NOT IN est moins rapide que NOT EXISTS.

L’exemple qui suit illustre, en reprenant l’exemple de mon époque Pythian, qu’Oracle est bien capable de prendre le même plan quelque soit la syntaxe :

• NOT IN
• NOT EXISTS
• OUTER JOIN

Evidemment les 3 requêtes ci-dessous ont le même sens, notamment du fait que les valeurs NULL sont interdites dans les 2 tables.
D’abord 2 tables et des statistiques pour l’exemple :

create table t1(id number,
  constraint t1_pk primary key(id));

create table t2(id number not null);

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');

Le premier exemple avec une clause 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  | Cost (%CPU)|
---------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     4 |    46   (5)|
|*  1 |  HASH JOIN ANTI    |       |     4 |    46   (5)|
|   2 |   INDEX FULL SCAN  | T1_PK |   100 |     1   (0)|
|   3 |   TABLE ACCESS FULL| T2    |   100K|    44   (3)|
---------------------------------------------------------

PLAN_TABLE_OUTPUT
---------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("B"."ID"="A"."ID")

Le second exemple utilise une clause NOT IN :

explain plan for
 select id from t1 a
  where id not in
   (select id from t2 b);

select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------
Plan hash value: 1906534000

---------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Cost (%CPU)|
---------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     4 |    46   (5)|
|*  1 |  HASH JOIN ANTI    |       |     4 |    46   (5)|
|   2 |   INDEX FULL SCAN  | T1_PK |   100 |     1   (0)|
|   3 |   TABLE ACCESS FULL| T2    |   100K|    44   (3)|
---------------------------------------------------------


PLAN_TABLE_OUTPUT
---------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("ID"="ID")

Le troisième exemple utilise une jointure :

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  | Cost (%CPU)|
---------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     4 |    46   (5)|
|*  1 |  HASH JOIN ANTI    |       |     4 |    46   (5)|
|   2 |   INDEX FULL SCAN  | T1_PK |   100 |     1   (0)|
|   3 |   TABLE ACCESS FULL| T2    |   100K|    44   (3)|
---------------------------------------------------------


PLAN_TABLE_OUTPUT
---------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("A"."ID"="B"."ID")