Pour actualiser un peu ce blog sur lequel je n’ai rien posté depuis 1 an, voici un lien sur une présentation (en anglais) sur l’indexation des clés étrangères (foreign key):
http://prezi.com/uzdd5ttg4cu0/indexing-foreign-keys-in-oracle/

Plutôt que de la traduire, je résume ici les idées principales pour aider à la lecture pour les francophones. Et n’hésitez pas à poser des questions ici.

Comportement d’Oracle lorsqu’il n’y a pas d’index sur la clé étrangère

L’idée qu’il faut toujours indexer les clés étrangères vient des anciennes versions où on se retrouvait avec un verrou table (TM-Share) lorsqu’on on modifiait une table.
Sous Oracle 7, c’est la table parent qui est vérouillée lorsqu’on insère un enregistrement dans la table fille. Puis à partir de la 8i c’est l’inverse: la table fille est vérouillée lorsqu’on supprime un enregistrement de la table parent.

Mais cela a été vite optimisé en 9i: ce verrou est relâché dès que la ligne parent est supprimée. Le verrou ne reste pas jusqu’à la fin de la transaction. C’est la raison pour laquelle on peut lire parfois qu’il n’y a plus de problème de verrou lorsque la clé étrangère n’est pas indexée. C’est faux. C’est juste que le verrou dure moins longtemps.

Mais il ne faut pas oublier que si la clé étrangère n’est pas indexée, alors la suppression d’une ligne parent devra faire un full scan de la table fille. Parce qu’il faudra vérifier que rien ne référence le parent qu’on veut supprimer. Et si la table fille est grosse, ça peut durer longemps. Et donc le verrou va quand même durer longtemps. Et pendant ce temps, toute modification sur la table sera bloquée, même si elle n’a rien à voir avec le parent qu’on est en train de supprimer.

Sur les version courantes d’Oracle il est donc nécessaire d’avoir un index sur la clé étrangère lorsque on a les 2 conditions suivantes:

1. On peut supprimer (delete) une ligne de la table référencée (parent). C’est aussi le cas si l’on modifie (update) la colonne référencée. Mais on ne devrait pas modifier une clé normalement. On doit aussi penser aux cas des delete cascade, au cas de merge, etc.
2. Et si la table fille est grande et/ou a beaucoup d’activité de modification dessus. Si ce n’est pas le cas, le verrou table n’aura pas une grande conséquence. Mais si c’est le cas, on peut bloquer toute l’application pendant un long moment.

En quoi un index peut aider ?

La première chose, c’est que l’index sur la clé étrangère permettra de vérifier rapidement qu’il n’y a pas d’enregistrements qui référenceraient la ligne qu’on supprime, sans avoir à faire un full table scan. Avant tout, on crée les index pour des raisons de performance d’accès.

La deuxième chose, c’est qu’en plus, lorsque Oracle va lire cet index, il va pouvoir vérifier qu’il n’y a pas d’insert en cours, même non encore commité. Parce que un insert va toujours dans un block bien précis dans un index.

S’il n’y a pas d’index, mais seulement la table pour vérifier, alors un insert peut aller n’importe où et il faut donc verrouiller toute la table pour empêcher cela. S’il y a un index, il suffit de verrouiller le bloc d’index correspondant à la valeur du parent pour la durée de l’opération de delete.

Quel index est possible ?

Si l’on crée systématiquement un index sur toutes les clés étrangères, on va se retrouver avec de nombreux index superflus. Parce ce que ce qu’il suffit d’avoir c’est un index qui commence par les colonnes indexées.
Je rappelle l’idée: si l’index permet de retrouver directement une (potentielle) ligne fille, alors il peut aussi servir à éviter de verrouiller toute la table.

Exemple:

Sur la table suivante:

table CHILD (a,b,c) ... references PARENT(a,b)

Les index suivants permettent d’éviter les verrous table lors d’un delete sur la table PARENT:

Par contre, ceux-là ne peuvent pas être utilisés pour ce but:

il est donc important de ne pas indexer aveuglément, mais de choisir les index qui sont utiles pour les performances des accés aux données. Ensuite, on vérifie qu’ils permettent aussi d’éviter les cas de verrou table s’ils peuvent avoir lieu.

Comment vérifier quels verrous sont posés ?

On a vu que le verrou est relâché dès que l’opération de suppression est finie. il ne reste pas jusqu’à la fin de la transaction. il est donc parfois difficile de le voir en test avec peu d’utilisateurs concurrents. On peut par contre utiliser l’event 10704 pour vérifier exactement quels verrous sont posés. La présentation montre un exemple avec son interprétation.

Conclusion et conseils

Lorsque une table parent peut avoir des delete dessus, même si c’est rare, même si c’est indirect (delete cascade) alors les tables filles peuvent se retrouver verrouillées s’il n’y a pas un index qui commence par les colonnes de la clé étrangère.
Si La table fille est grande et/ou si il y a beaucoup de modifications dessus, ce verrou peut durer et peut bloquer toute modification.
Dans ce cas, il faut indexer les clés étrangère. Mais pas aveuglément: on doit réfléchir à l’ordre des colonnes, à utiliser un index qui sert aussi à autre chose, etc.

Par contre, les clés étrangères vers des tables de référence statiques n’ont pas besoin d’index. Sauf bien sûr pour des raisons d’accès si on a besoin pour une jointure entre les deux table et que la sélectivité est assez forte pour bénéficier d’un index..

En test, on peut savoir exactement si une opération est susceptible de verrouiller la table fille avec l’event 10704 . C’est important car il y a quelques cas particuliers (des bugs par exemple). au moindre doute, il faut donc tester avec votre version d’Oracle, sur votre modèle de données.

En production, il faut réagir si on voit des wait events ‘enq: TM‘ avec des verrous en mode 4 (Share) ou 5 (SSX). Même s’ils sont anodins maintenant, ils seront bloquants un jour lorsqu’il y aura plus de données ou plus d’activité.

Enfin, il ne faut pas voir la nécessité d’indexer certaines clés étrangères comme quelque chose de contraignant. Dans la plupart des cas l’index en question est nécessaire pour des raisons de performance, pour que le delete n’ait pas à faire un full scan de la table fille. Et le comportement d’Oracle est alors plutôt performant: aucun verrou n’est posé. Et cela depuis les plus anciennes version d’Oracle.

Un dernier point: si vous pensez que le mieux est de ne pas déclarer les contraintes d’intégrité référentielles en base de données, mais de les gérer au niveau de l’application, alors vous aurez des problèmes dès que vous aurez plusieurs utilisateurs concurrents. Soit vous risquez d’avoir une inconsistance de données, soit vous devrez verrouiller toute la table jusqu’à la fin de la transaction. Car les mécanismes interne d’Oracle que l’on vient de voir (relâcher le verrou avant la fin de la transaction, ou utiliser le bloc d’index pour voir les insert non commités) et qui sont très performants ne sont pas accessibles par l’application…