Faut-il remplacer les jointures par des EXISTS lorsque c’est possible ?
Une sous-requête EXISTS n’a pas besoin de ramener toutes les lignes d’une jointure: dès qu’il y a une correspondance pour une valeur de la jointure, on peut passer à la suivante. C’est un Semi Hash Join, qui est plus rapide qu’un Hash Join. Très souvent le CBO va faire cette réécriture lors des transformations de la phase d’optimisation.
En en 12c on a une transformation automatique de plus lorsque les lignes de la jointure seront au final dédoublonnées avec un DISTINCT.
Par exemple, la requête suivante:
select distinct status_label from TEST_STATUS join TEST using(status_id) where flag='Y';
sera transformée en:
select status_label from TEST_STATUS where exists( select null from TEST where flag='Y' and TEST.status_id=TEST_STATUS.status_id );
vu qu’on ne renvoit que les colonnes de TEST_STATUS, un EXISTS (ou IN) est suffisant. Et on obtient le plan d’exécution suivant:
------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 7 | | | | | 1 | HASH UNIQUE | | 1 | 10 | 10 |00:00:00.01 | 7 | 1214K| 1214K| 1105K (0)| |* 2 | HASH JOIN SEMI | | 1 | 10 | 10 |00:00:00.01 | 7 | 1185K| 1185K| 1142K (0)| | 3 | TABLE ACCESS FULL| TEST_STATUS | 1 | 10 | 10 |00:00:00.01 | 3 | | | | |* 4 | TABLE ACCESS FULL| TEST | 1 | 50000 | 10 |00:00:00.01 | 4 | | | | ------------------------------------------------------------------------------------------------------------------------
Ici on voit le gros avantage dans la colonne A-Rows: le Full Table Scan de la table TEST qui fait 100000 lignes, s’est arrêté dès qu’on a trouvé les 10 correspondances avec la table TEST_STATUS. Pas besoin de continuer: le prédicat EXISTS est vérifié.
C’est le HASH JOIN SEMI qui fait ça alors qu’un HASH JOIN aurait lu 100000 lignes et renvoyé 50000 lignes jusqu’au DISTINCT.
Voici le plan en désactivant les transformations:
------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.09 | 214 | | | | | 1 | HASH UNIQUE | | 1 | 10 | 10 |00:00:00.09 | 214 | 1214K| 1214K| 1099K (0)| |* 2 | HASH JOIN | | 1 | 50000 | 100K|00:00:00.07 | 214 | 1185K| 1185K| 1129K (0)| | 3 | TABLE ACCESS FULL| TEST_STATUS | 1 | 10 | 10 |00:00:00.01 | 3 | | | | |* 4 | TABLE ACCESS FULL| TEST | 1 | 50000 | 100K|00:00:00.01 | 211 | | | | ------------------------------------------------------------------------------------------------------------------------
A noter que en 11.2 il y avait déjà une transformation utile dans ce cas, Distinct Placement qui fait descendre le DISTINCT sous la jointure, mais qui nécessite quand même de lire toute la table.
Demo, hints, plans d’exécution et traces 10053 complétes dans la demo
Alors pourquoi faire ça plutôt que de réécrire la requête ? Le gros intérêt de ces transformations, c’est lorsqu’on on fait une requête sur une vue dans laquelle il y a la jointure. La vue va souvent chercher des colonnes dont on a pas besoin. Les transformations permettent alors d’éliminer les opérations inutiles.