Une semi-jointure est une jointure entre 2 tables où les lignes de la première table sont retournées s’il existe au moins une correspondance dans la 2ème table.
Les semi-jointures peuvent notamment s’écrire soit à l’aide de la clause EXISTS soit à l’aide de la clause IN.
Voyons ces 2 manières d’écrire les semi-jointures avec un exemple utilisant les tables COUNTRIES et CUSTOMERS du schéma SH (un des schémas DEMO d’Oracle) :
Ces requêtes retournent la liste des pays pour lesquels on a au moins un client qui y réside.
On constate que les 2 requêtes retournent le même nombre de lignes ce qui veut dire que ces 2 manières d’écrire les semi-jointures sont identiques d’un point de vu fonctionnel.
Mais qu’en est-il d’un point de vu performances ?
Sur ce sujet j’ai souvent entendu des développeurs (et même des DBAs) dire que la clause EXISTS était à privilégier par rapport à la clause IN.
Ceci était vrai jusqu’à la version 8i mais ce n’est plus le cas aujourd’hui. En effet, depuis la 9i l’optimiseur d’Oracle lors du parsing transforme « à sa sauce » la requête (que ce soit celle avec la clause IN ou celle avec la clause EXISTS) en « mergeant » la sous-requête. On dit qu’Oracle UNNEST la sous requête. Ce merge permet à Oracle d’utiliser la technique de semi-jointure (NESTED LOOP SEMI ou HASH JOIN SEMI) plus performante que l’opération FILTER.
Voici la preuve que les 2 requêtes conduisent au même plan d’exécution :
Ici le plan consiste à rechercher pour chaque ligne de la table COUNTRIES s’il existe une correspondance dans la table CUSTOMERS via l’index IDX_COUNTRY_ID. L’avantage du NESTED LOOP SEMI par rapport au NESTED LOOP classique (en gros de la semi jointure par rapport à une equi-jointure classique) c’est que dans la semi-jointure la recherche pour un COUNTRY_ID s’arrête dès lors qu’on a trouvé une correspondance dans la table CUSTOMERS permettant à Oracle de passer au COUNTRY_ID suivant. C’est comme si on avait une clause EXIT dans le code d'une boucle imbriquée.
Voyons maintenant ce que donne le plan lorsqu’on empêche l’optimiseur de faire sa transformation. Pour ce faire j'utilise le hint NO_QUERY_TRANSFORMATION (le hint parle de lui-même) :
On voit que la sous-requête n’est plus « unnestée » et que le CBO n’a d’autre choix que d’utiliser l’opérateur FILTER qui est beaucoup moins performant.
Voici également une preuve qu’en 8i les plans et donc les performances étaient bien différents selon qu’on utilisait la clause IN ou la clause EXISTS. Il est possible de passer l’optimiseur en mode 8i en modifiant le paramètre OPTIMIZER_FEATURES_ENABLE :
Maintenant que nous sommes en 11g (et bientôt en 12c) ne dites plus que EXISTS c’est mieux que IN.
A lire aussi:
ANTI-JOINS: NOT IN vs NOT EXISTS
Les semi-jointures peuvent notamment s’écrire soit à l’aide de la clause EXISTS soit à l’aide de la clause IN.
Voyons ces 2 manières d’écrire les semi-jointures avec un exemple utilisant les tables COUNTRIES et CUSTOMERS du schéma SH (un des schémas DEMO d’Oracle) :
SQL> select /* EXISTS_SEMI_JOIN */ country_name from countries co where exists (select 1 from customers cu where co.COUNTRY_ID=cu.COUNTRY_ID); COUNTRY_NAME ---------------------------------------- Italy Brazil Japan United Kingdom Germany United States of America France Canada Spain China Singapore New Zealand Poland Australia Argentina Denmark South Africa Saudi Arabia Turkey 19 rows selected. SQL> select /* IN_SEMI_JOIN */ country_name from countries co where country_id in (select country_id from customers cu ); COUNTRY_NAME ---------------------------------------- Italy Brazil Japan United Kingdom Germany United States of America France Canada Spain China Singapore New Zealand Poland Australia Argentina Denmark South Africa Saudi Arabia Turkey 19 rows selected.
Ces requêtes retournent la liste des pays pour lesquels on a au moins un client qui y réside.
On constate que les 2 requêtes retournent le même nombre de lignes ce qui veut dire que ces 2 manières d’écrire les semi-jointures sont identiques d’un point de vu fonctionnel.
Mais qu’en est-il d’un point de vu performances ?
Sur ce sujet j’ai souvent entendu des développeurs (et même des DBAs) dire que la clause EXISTS était à privilégier par rapport à la clause IN.
Ceci était vrai jusqu’à la version 8i mais ce n’est plus le cas aujourd’hui. En effet, depuis la 9i l’optimiseur d’Oracle lors du parsing transforme « à sa sauce » la requête (que ce soit celle avec la clause IN ou celle avec la clause EXISTS) en « mergeant » la sous-requête. On dit qu’Oracle UNNEST la sous requête. Ce merge permet à Oracle d’utiliser la technique de semi-jointure (NESTED LOOP SEMI ou HASH JOIN SEMI) plus performante que l’opération FILTER.
Voici la preuve que les 2 requêtes conduisent au même plan d’exécution :
SQL> explain plan for
2 select /* EXISTS_SEMI_JOIN */ country_name from countries co where exists (select 1 from customers cu where co.COUNTRY_ID=cu.COUNTRY_ID);
SQL> select * from table(dbms_xplan.display);
Plan hash value: 1942541467
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19 | 380 | 26 (0)| 00:00:01 |
| 1 | NESTED LOOPS SEMI | | 19 | 380 | 26 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| COUNTRIES | 23 | 345 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_COUTRY_ID | 45848 | 223K| 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("CO"."COUNTRY_ID"="CU"."COUNTRY_ID")
SQL> explain plan for
2 select /* IN_SEMI_JOIN */ country_name from countries co where country_id in (select country_id from customers cu );
SQL> select * from table(dbms_xplan.display);
Plan hash value: 1942541467
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19 | 380 | 26 (0)| 00:00:01 |
| 1 | NESTED LOOPS SEMI | | 19 | 380 | 26 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| COUNTRIES | 23 | 345 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_COUTRY_ID | 45848 | 223K| 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("COUNTRY_ID"="COUNTRY_ID")
Ici le plan consiste à rechercher pour chaque ligne de la table COUNTRIES s’il existe une correspondance dans la table CUSTOMERS via l’index IDX_COUNTRY_ID. L’avantage du NESTED LOOP SEMI par rapport au NESTED LOOP classique (en gros de la semi jointure par rapport à une equi-jointure classique) c’est que dans la semi-jointure la recherche pour un COUNTRY_ID s’arrête dès lors qu’on a trouvé une correspondance dans la table CUSTOMERS permettant à Oracle de passer au COUNTRY_ID suivant. C’est comme si on avait une clause EXIT dans le code d'une boucle imbriquée.
Voyons maintenant ce que donne le plan lorsqu’on empêche l’optimiseur de faire sa transformation. Pour ce faire j'utilise le hint NO_QUERY_TRANSFORMATION (le hint parle de lui-même) :
SQL> explain plan for
2 select /*+ NO_QUERY_TRANSFORMATION */ country_name from countries co where country_id in (select country_id from customers cu );
SQL> select * from table(dbms_xplan.display);
Plan hash value: 3262839326
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 15 | 15 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| COUNTRIES | 23 | 345 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_COUTRY_ID | 2 | 10 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT /*+ */ 0 FROM "CUSTOMERS" "CU" WHERE
"COUNTRY_ID"=:B1))
3 - access("COUNTRY_ID"=:B1)
On voit que la sous-requête n’est plus « unnestée » et que le CBO n’a d’autre choix que d’utiliser l’opérateur FILTER qui est beaucoup moins performant.
Voici également une preuve qu’en 8i les plans et donc les performances étaient bien différents selon qu’on utilisait la clause IN ou la clause EXISTS. Il est possible de passer l’optimiseur en mode 8i en modifiant le paramètre OPTIMIZER_FEATURES_ENABLE :
SQL> alter session set optimizer_features_enable='8.0.6';
Session altered.
SQL> explain plan for
2 select /* IN_SEMI_JOIN */ country_name from countries co where country_id in (select country_id from customers cu );
Explained.
SQL> select * from table(dbms_xplan.display);
Plan hash value: 214911130
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19 | 912 | | 228 |
|* 1 | HASH JOIN | | 19 | 912 | | 228 |
| 2 | VIEW | VW_NSO_1 | 19 | 247 | | 226 |
| 3 | SORT UNIQUE | | 19 | 247 | 1112K| 226 |
| 4 | INDEX FAST FULL SCAN| IDX_COUTRY_ID | 55500 | 704K| | 13 |
| 5 | TABLE ACCESS FULL | COUNTRIES | 23 | 805 | | 1 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("COUNTRY_ID"="$nso_col_1")
Note
-----
- cpu costing is off (consider enabling it)
21 rows selected.
SQL> explain plan for
2 select /* EXISTS_SEMI_JOIN */ country_name from countries co where exists (select 1 from customers cu where co.COUNTRY_ID=cu.COUNTRY_ID);
Explained.
SQL> select * from table(dbms_xplan.display);
Plan hash value: 3262839326
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 35 | 1 |
|* 1 | FILTER | | | | |
| 2 | TABLE ACCESS FULL| COUNTRIES | 1 | 35 | 1 |
|* 3 | INDEX RANGE SCAN | IDX_COUTRY_ID | 2921 | 37973 | 7 |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT 0 FROM "CUSTOMERS" "CU" WHERE
"CU"."COUNTRY_ID"=:B1))
3 - access("CU"."COUNTRY_ID"=:B1)
Note
-----
- cpu costing is off (consider enabling it)
21 rows selected.
Maintenant que nous sommes en 11g (et bientôt en 12c) ne dites plus que EXISTS c’est mieux que IN.
A lire aussi:
ANTI-JOINS: NOT IN vs NOT EXISTS
Aucun commentaire:
Enregistrer un commentaire