Primary Key et contrainte Unique ET index Unique : double vérification?

Version imprimable

18/07/2016, 09h23
Ikebukuro

Primary Key et contrainte Unique ET index Unique : double vérification?

Bonjour,

J'ai constaté que quand on crée une contrainte unique ou une PK dans une table, Oracle crée automatiquement un index mais que cet index est de type UNIQUE :
https://docs.oracle.com/cd/B28359_01...indexes003.htm
"Oracle Database enforces a UNIQUE key or PRIMARY KEY integrity constraint on a table by creating a unique index on the unique key or primary key."

Ma question est : est-ce que pendant un INSERT ou un UPDATE, la contrainte d'unicité est testée deux fois : à la fois dans la table via les contraintes d'intégrité mais aussi lors de la mise à jour de l'index?
19/07/2016, 09h37
SQLpro
Un bon SGBDR optimise tous les traitements internes. Le but d'un index est d'accélérer les recherches. En posant une PK, un index UNIQUE est créé. Deux solutions s'offrent à l'optimiseur pour vérifier l'unicité d'une nouvelle entrée :
- bloquer la table en mode exclusif, la parcourir entièrement pour vérifier que la valeur que l'on veut insérer n'existe pas déjà, insérer la nouvelle ligne, débloquer la table.
- rechercher la valeur que l'on veut insérer dans l'index, bloquer la valeur pour l'insertion de la ligne, insérer la nouvelle ligne, débloquer la ligne.
Je te laisse deviner ce que l'optimiseur choisira !

A +
19/07/2016, 11h05
Ikebukuro

Salut SQLpro : je penche pour la deuxième approche, l'utilisation de l'index qui doit être quand même bien plus rapide qu'un Full table scan.

Je ne comprends pas "bloquer la valeur pour l'insertion de la ligne" : Oracle s'assure par un mécanisme interne qu'aucune autre transaction ne va insérer la même valeur?

En tout cas merci pour la réponse, je ne m'étais jamais demandé comment Oracle vérifiait l'unicité d'une colonne avant.
20/07/2016, 23h56
pachot

Bonjour,
Un index (pas forcement unique) est nécessaire pour vérifier la contrainte d'intégrité. C'est pour cela qu'il est créé automatiquement s'il existe pas déjà un index qui commence par les colonnes uniques. Un index unique est un peu plus performant qu'un index non-unique, d'où le choix. Il n'y a pas de double vérification: seulement la déclaration d'une contrainte, et la structure physique qui permet de la vérifier.
Cordialement,
Franck.
21/07/2016, 08h43
Ikebukuro

Je vous remercie pour vos réponses car je ne me souviens pas avoir lu dans un bouquin qu'on utilisait les index pour valider les contraintes d'unicité; j'aurai appris quelque chose de vraiment intéressant :D
21/07/2016, 08h46
SQLpro

Citation:

Envoyé par Ikebukuro

Je vous remercie pour vos réponses car je ne me souviens pas avoir lu dans un bouquin qu'on utilisait les index pour valider les contraintes d'unicité; j'aurai appris quelque chose de vraiment intéressant :D

C'est normal, car vous avez sans doute appris les bases de données d'un point de vue logique. D'un point de vue logique, la notion d'index, tout comme la notion de "storage" n'existe pas. Il faut descendre au niveau physique pour y trouver tout cela et c'est de la responsabilité du DBA, pas du développeur.

A +
21/07/2016, 08h52
SQLpro
Citation:

Envoyé par pachot

Bonjour,
Un index (pas forcement unique) est nécessaire pour vérifier la contrainte d'intégrité. ...

Désolé mais je ne suis pas d'accord avec toi, car aucun index n'est nécessaire pour vérifier la moindre unicité (contrainte d'intégrité dont tu parles)...

C'est d'ailleurs ce que je démontre dans mon post : http://www.developpez.net/forums/d15...n/#post8694027

Sauf que la différence entre pas d'index pour vérifier une clef primaire et un index pour cela étant tellement énorme que tous les SGBDR (et oracle en fait bien sûr partie) posent un index sous-jacent lorsque de la création des contraintes PRIMARY KEY et UNIQUE, ce qui présente le double avantage :
- d'aller immensément plus vite
- de permettre une meilleure concurrence d'accès
A +
21/07/2016, 08h56
SQLpro

Citation:

Envoyé par Ikebukuro

Je ne comprends pas "bloquer la valeur pour l'insertion de la ligne" : Oracle s'assure par un mécanisme interne qu'aucune autre transaction ne va insérer la même valeur?

Oui, car si une autre personne veut insérer la même valeur au même moment, il faut la rejeter. Le but d'un verrou (le fameux mécanisme interne) est d'obliger les accès concurrents à attendre avant de pouvoir faire.
Ainsi, un utilisateur qui voudrait insérer la même valeur se verrait obligé d'attendre jusqu'à ce que la transaction d'insertion qui le fait patienter, soit validée par un COMMIT. Dès lors il peut accéder à cet endroit de la table et constater que la valeur existe déjà et sera bien entendu refoulé par un "viol" de contrainte.

A +
22/07/2016, 16h20
pachot

Citation:

Envoyé par SQLpro

... aucun index n'est nécessaire pour vérifier la moindre unicité ...

Je parle dans le contexte d'Oracle ici. Ce n'est pas nécessairement une question de performance car sinon la même chose serait faite pour une foreign key: lors d'un delete sur une table référencée, il y a soit accès par index, soit verrou (Share pas nécessairement exclusive) + full table scan. Pour une contrainte d'unicité il n'y a pas le choix: ce n'est implémenté que par index.
Cordialement,
Franck.
25/07/2016, 19h26
SQLpro

Citation:

Envoyé par pachot

... Ce n'est pas nécessairement une question de performance car sinon la même chose serait faite pour une foreign key: lors d'un delete sur une table référencée, il y a soit accès par index, soit verrou (Share pas nécessairement exclusive) + full table scan. ...

Là encore la nécessité d'indexer les FOREIGN KEY ou pas ne découle pas de la pose de FK, mais plus simplement du fait qu'il est inutile de poser un index ou il y en a déjà un.
Par essence il est important d'indexer toutes les FK, sans chercher à comprendre. Cela ne fait pas de mal. Cependant certaines FK sont déjà "naturellement" indexées... d’où la position d'une majorité de SGBDR qui n'indexe jamais les FK de manière automatique (Oracle, comme SQL Server par exemple). C'est le cas systématique de l'héritage et dans une moindre mesure de la table d'association (aussi appelée table de jointure) découlant d'une association plusieurs à plusieurs ou n-aire (n>2).
Pour info, un seul SGBD soit disant relationnel, à pris le partit inverse : indexer toutes les FK... Devine lequel... Allez je t'aide :
1) il est vendu par Oracle
2) il est soit-disant libre (ce qui entre nous n'est pas vrai)
3) son nom commence par "My..."

A +
26/07/2016, 11h13
pachot

Bonjour Frédéric,
Là encore je ne parle que pour Oracle. Le fait qu'il est inutile de poser un index ou il y en a déjà un est géré pour les PK. Pour les FK c'est vraiment parce que ce n'est pas nécessaire pour l'implémentation qu'il n'y a pas d'index créé automatiquement. C'est un choix de performance à faire en fonction du volume, de la sélectivité, de la navigation parent->fils, et des opérations de delete sur le parent.
Cordialement,
Franck
26/07/2016, 15h45
SQLpro

Pour info, Power AMC index toutes les FK sauf si elles sont "incluses" (il les signale et à nous de les corriger).

A +