Les jointures

Oracle et les jointures

Les jointures sont omniprésentes dans les applications. Comme le volume de données qu’Oracle doit traiter s’amplifie rapidement, les performances obtenues peuvent être vite mises à genoux. Une application écrite avec des jointures « pourries » dans le code peut bien se comporter au début d’un projet ou dans la phase de test/intégration et s’avérer catastrophique en production après quelques mois.

Si l’on décide d’un type spécifique de jointure performant a un moment donnée de la vie d’un projet, c’est possible que les tables intervenant dans la jointure vivent avec des poids différents qui avec le temps nécessiteront peut-être un autre type de jointure.

Pour décider de quel type de jointure utiliser, il faut s’armer d’un bagage de connaissances nécessaires à la compréhension de leur utilisation :

1) Quelle version d’Oracle est utilisée ? Il ne faut pas négliger qu’avec les versions récentes, Oracle optimise les méthodes existantes et en apporte d’autres.

2) Quelle table est le pivot ? (DRIVING TABLE).

3) Quelles sont les jointures possibles ?

4) Quels sont les index disponibles ? Quelle est leur sélectivité respective ?

5) Les astuces (HINTS)

Avant Oracle 7, les jointures étaient de trois catégories : NESTED LOOPS, SORT-MERGE et CLUSTER.

Oracle 7.3 introduit le HASH JOIN, 8i le INDEX JOIN.

Voici une liste de types de jointures et leur cadre d’utilisation. Inutile de remarquer que la forme du code joue sur l’emploi d’un ou autre type avant Oracle 7, et qu’a partir de Oracle 7 c’est l’optimiseur statistique qui s’en charge, mais qu’on peut le « tromper »

Avec des HINTS.

NESTED LOOPS

J'imagine la situation suivante pour parler de ça: Vous allez au supermarché avec une liste d'achats.

Vous achetez le premier article après avoir cherché dans tout le magasin. Vous le mettez dans le caddy. La liste est le DRIVING TABLE !
Vous réitérez cette action pour chaque objet sur la liste. Bien sur que si vous savez dès le début ou se trouvent les objets, ça ira mieux (d'ou les index sur la deuxième table (le supermarché)).
Si votre époux (un autre processus) prend les articles, les paye à la caisse, les dépose dans la voiture et revient pour les suivants, vous avez une idée des performances des NESTED LOOPS: les résultats commencent à arriver avant que l'action soit terminée.

En bref, cette méthode est très efficace si la table pivot (la liste de courses) (DRIVING TABLE) est de petite taille ET si vous avez un bon index très sélectif sur la deuxième table.

Techniquement, lors d’une jointure de ce type, Oracle lit le premier enregistrement de la première table et cherche dans la deuxième ce qui lui convient et ainsi de suite.

Si vous n’utilisez pas l’optimiseur statistique, gare à la modification non proportionnelle des tables ! Vous ne prendrez jamais chaque objet du magasin et regarder s’il figure sur votre liste de courses ;-)

SORT-MERGE

J'imagine la situation suivante pour parler de ça: Deux démarcheurs téléphoniques des sociétés différentes qui sont amis comparent leurs listes de personnes démarchées dans une même région pour trouver des clients communs.

Chacun trie sa liste.
Ils comparent les listes triées (C’est le principe de MERGE).

Techniquement, lors d’une jointure SORT-MERGE, Oracle trie les enregistrement des tables intervenantes (la clé est constituée des colonnes de jointure). Les lignes communes constituent le résultat.

Ce type de jointure est utilisé avec succès dans le cas de l’égalité des critères, de la non sélectivité des critères ou des volumes importantes de données dans les deux tables. C’est une méthode gourmande en mémoire et entrées/sorties.

CLUSTER JOIN

Supposons que vous avez des billes rouges (table A) et vertes (table B) dans des paniers. Dans chaque panier, indifféremment de la couleur, les billes ont le même poids (différent peut être du poids dans les autres paniers).

Pour trouver toutes les billes d’un même poids (critère de jointure), il suffit de regarder les paniers un par un.

Techniquement, si les tables font partie d’un cluster, s’il s’agit d’une equi-jointure des clés du cluster, Oracle lira pour chaque ligne d’une table et trouvera les lignes de la deuxième avec un index CLUSTER.

Ce type de jointure est très performant, vu que les lignes avec la même clé se trouvent dans le même bloc (panier). Il est relativement peu utilisé par rapport aux autres types.

HASH JOIN

Oracle crée une table de hachage avec les enregistrements de la deuxième table. Chaque valeur ainsi constituée, est comparée avec les valeurs HASH de la premiere table : une version de NESTED LOOPS.

Ce type de jointure devient intéressant quand il n’y a pas d’index sur les deux tables. Le match HASH se fait plus rapidement que l’analyse d’un index BTREE. Il est utilisable uniquement pour les equi-jointures. (>=7.3)

INDEX JOIN

Oracle 8i introduit ce type de jointure : Si les tables intervenantes sont indexées, si les colonnes indexées participent à la jointure, Oracle utilisera un HASH JOIN sur les index respectifs. C’est une jointure très rapidement réalisée. Souvent, pour permettre à Oracle de l’utiliser, nous indexerons plus des colonnes qu’auparavant.

Ce n’est pas tout. Ceci fait partie d’un livre de Tuning Oracle (oui, oui) qui verra le jour un jour ;-). Profitons-en ! Radu Caulea, 1999