Il n’est pas conseillé d’utiliser des hints pour forcer l’optimiseur. Il est préférable de laisser le CBO trouver le bon plan d’exécution en lui donnant de bonnes statistiques.
Mais quelquefois, cette tâche est longue et complexe alors qu’on connaît d’emblée le plan d’exécution optimal.
L’autre cas où j’utilise souvent des hints pour forcer un plan, c’est pour vérifier un plan que je pense être meilleur: en le forçant, je vois d’abord s’il est possible, et ensuite s’il est optimal. Il ne reste plus alors qu’à voir comment arriver plus proprement à ce plan…

Si l’on doit forcer la méthode de jointure, il faut par contre faire attention à définir complètement le plan, et cela nécessite plusieurs hints. Sinon, on risque de provoquer un plan ni voulu ni optimal.

La première chose, c’est de définir l’ordre dans lequel vont se faire les jointures, avec LEADING. Il y aura toutes les tables dans LEADING.
Puis, pour chacune de ces tables, sauf la première il y aura l’un des hints suivants:

• USE_NL(alias) pour faire un Nested Loop join: pour chaque ligne du résultat précédent, on va aller voir la table précisée dans le hint (alias)
  l’accés à alias se fera autant de fois qu’il y a de ligne venant de la table précédente.
• USE_HASH(alias) pour faire un Hash Join. Il faut alors rajouter un autre hint pour préciser quelle est la source qui va être hachée:
  • USE_HASH(alias) NO_SWAP_JOIN_INPUTS(alias)

  Le résultat précédent va être haché, puis on va lire alias et pour chaque ligne aller voir la table de hachage

  • USE_HASH(alias) SWAP_JOIN_INPUTS(alias)

  C’est la nouvelle table qu’on va voir (alias) qui va être hachée, et interrogée pour chaque ligne du résultat précédent

• USE_MERGE(alias) pour faire un Sort Merge join: le résultat précédent est trié (si ce n’est pas déjà dans le bon ordre), alias est lu et trié (dans tous les cas) puis la jointure est faite en parcourant les deux. Le résultat suit la même tri.
• USE_MERGE_CARTESIAN(alias)

pour faire une jointure où la plupart des lignes de alias vont satisfaire la jointure pour la plupart des lignes du résultat précédent.
Dans ce cas, pas de tri, pas de hachage, mais simplement un buffer (qui peut aller en tempfile) pour ne pas avoir à accéder plusieurs fois à alias (comme le ferait un Nested Loop par exemple)

Lorsque je parle du résultat précédent, c’est celui qui vient des jointures des tables de gauche dans le hint LEADING.

Dans la demo, en faisant varier les cardinalités de 3 tables, on voit les risque d’avoir d’autres plans si on ne précise pas tous les alias nécessaires.

Pourquoi SWAP_JOIN_INPUTS ? pour faire passer la table hachée au dessus du plan d’exécution, car c’est la première opération fille d’un HASH JOIN dans le plan d’exécution (parce qu’il faut avoir lu cette table avant de parcourir l’autre). Avec NESTED LOOPS, c’est l’inverse: la table qui est parcourue se trouve en premier. Avec LEADING on liste l’ordre des tables du plan d’exécution. Avec SWAP_JOIN_INPUTS pour Hash Join seulement, on change l’ordre.