Questions index / PK [Résolu]

[stof]

Oracle (important car dans mes recherches, les gens parlent d'index, etc mais on ne sait pas toujours si ça s'applique à tous):

1)création d'une PK implique index implicite (qui n'apparaît pas dans le script de la table)
/
même question pour un unique constraint (un index unique EN PLUS a-t-il un intérêt, et d'ailleurs inversement la création d'un index unique empêche-t-il la duplication des champs "uniques"...comme une unique constraint) ?
-->ici, on trouve une chose et son contraire suivant la source où on a vu l'info!

2)Un index sur une colonne d'une table qui possède une FK sur une autre table joue-t-il dans la performance des jointures faites entre ces 2 tables?

3)Réunit les questions 1&2
L'ordre des champs dans une PK composite est-il important (j'imagine que la réponse est conditionnée par ma question1)
--> exemple {A, B} mieux que {B, A} quand on recherche/joint plus souvent par A}
---> et si PK = {A, B} et qu'on cherche aussi bien par A seul que B seul, doit-on ajouter un index sur B uniquement

4)A partir de quelle taille un index est-il utile?

5)Faut que j'arrête de lire des trucs, j'ai lu qu'un index {A, B, C} sera moins performant qu'un index {A, B} si on ne cherche que sur A et B
--> si on cherche sur {A, B, C} et {A, B}, doit-on créer les 2 index?

[SQLpro]

Envoyé par stof

Oracle (important car dans mes recherches, les gens parlent d'index, etc mais on ne sait pas toujours si ça s'applique à tous):

1)création d'une PK implique index implicite (qui n'apparaît pas dans le script de la table)

FAUX... aucun SGBDR Oracle compris ne pose d'index à la création des contraintes de clefs étrangères, car, suivant le modèle de données, cet index peut déjà préalablement exister (cas notamment des entités de spécialisation, aussi appelées "héritages")
A lire : http://sqlpro.developpez.com/cours/quoi-indexer/#LIV

Envoyé par stof

même question pour un unique constraint (un index unique EN PLUS a-t-il un intérêt, et d'ailleurs inversement la création d'un index unique empêche-t-il la duplication des champs "uniques"...comme une unique constraint) ?

Une contrainte d'unicité, comme une contrainte de clef primaire génère un index UNIQUE. Il n'est donc pas nécessaire, même si cela est permis, de rajouter un index qui sera redondant.

Un index UNIQUE est l'équivalent d'une contrainte d'unicité.
MAIS...

• la contrainte est un objet logique (pas besoin en théorie d'un index pour vérifier l'unicité)
• l'index est un objet physique (il sert accélérer certaines recherches et notamment s'il existe déjà une valeur identique - fort utile pour le cas de vérification d'unicité...)

Envoyé par stof

2)Un index sur une colonne d'une table qui possède une FK sur une autre table joue-t-il dans la performance des jointures faites entre ces 2 tables?

Vous ne pouvez pas greffer une FK sur autre chose que :

• une clef primaire (déjà naturellement indexée)
• une contrainte d'unicité (déjà naturellement indexée)

Donc du côte de la table de référence il y aura toujours un index...
Pour la table fille, en indexant en sus les colonnes de la FK dans la même composition que la clef de référence, alors vos jointures auront un coût tout à fait négligeable...
Dans mes cours je m'amuse à faire calculer à mes élèves le cout d'une jointure de 30 tables de 100 millions de lignes distincte dont la jointure se fait sur un entier... Vous verrez qu'il est tout à fait négligeable, si les deux côtés de la jointure sont pourvus d'index (exactement 180 pages à lire). En revanche, sans index d'un seul côté, on passe à 4 500 000 pages ! Autrement dit sans index d'un seul côté, la requête est 25 000 fois plus longue... Une paille !

Envoyé par stof

3)Réunit les questions 1&2
L'ordre des champs dans une PK composite est-il important (j'imagine que la réponse est conditionnée par ma question1)
--> exemple {A, B} mieux que {B, A} quand on recherche/joint plus souvent par A}
---> et si PK = {A, B} et qu'on cherche aussi bien par A seul que B seul, doit-on ajouter un index sur B uniquement

4)A partir de quelle taille un index est-il utile?

5)Faut que j'arrête de lire des trucs, j'ai lu qu'un index {A, B, C} sera moins performant qu'un index {A, B} si on ne cherche que sur A et B
--> si on cherche sur {A, B, C} et {A, B}, doit-on créer les 2 index?

C'est pourquoi je parlais de composition...
Si votre clef primaire est Col1 ASC, Col2 DESC, Col3 ASC et que l'index de votre FK est Col1 ASC, Col2 ASC, Col3 ASC alors il n'est pas possible d'utiliser les index pour résoudre avec vélocité la jointure.
La composition doit porter :
1) sur les mêmes colonnes et en même nombre
2) sur la même position ordinale
3) sur le même sens de tri

Pour ce qui est des recherches... l'index {A, B} est dit inclus dans l'index {A, B, C} (vectorisation des informations) on peut donc s'en séparer, mais parfois, notamment si C est une donnée très longue, ces deux index peuvent avoir chacun leurs avantages. En revanche, l'index {B, A} n'est pas inclus dans l'index {A, B, C}...

Enfin, plus un index est long, moins il est efficace. En effet (et sauf exception) un index sur une colonne de 250 caractères a peu de chance de servir à quoi que ce soit, car pour trouver une ligne à partir d'une valeur dans une colonne d'une table, il faut une série de caractères identiques. Plus la saisie est longue et plus l'utilisateur à des chances de se tromper ou de saisir une autre version du littéral, ce qui ne servira donc à rien.
Exemples :

• RATP => peu d'erreur possible
• Régie Autonome des Transports Parisiens => déjà plus d’erreur possible
• Association Nationale & Internationale pour la Cause Animale => erreur très fréquente sur l'esperluette !

Lisez les articles que j'ai écrit sur ces sujets, notamment :
1) Tout sur l'index (1ere partie)
2) Indexer avec SQL... oui mais quoi ?
3) Est-il bon de ne pas avoir d'index ni de clef dans une table ?
4) Indexation... une étude par l'exemple

Ne lisez par n'importe quoi... Les sites les plus populaires ne sont pas toujours les mieux renseignés...

A +

[pachot]

Bonjour,

création d'une PK implique index implicite (qui n'apparaît pas dans le script de la table)

PK implique unicité. Et le seul moyen d'implémenter la vérification de l'unicité de manière performante est d'avoir un index sur les colonnes - ou qui commence par ces colonnes. soit un index comme cela exite déjà, soit il est créé implicitement.

même question pour un unique constraint

Oui, puisque c'est l'unicité qui joue même dans le cas de la PK.

Oui, un index unique suffit pour empêcher les doublons. Mais c'est une solution plutôt physique: on ne peut pas stocker des doublons, donc on les empêche.
Si la modélisation définit une unicité, alors il est logique de créer une contrainte. C'est plus clair pour celui qui lit le modèle de données. elle peut être référencée par une foreign key. Puis l'implémentation de la contrainte peut créer un index unique sur ces colonnes, ou s'appuyer sur un index existant (pas forcément unique, pas forcément dans le même ordre, pouvant avoir d'autre colonnes en plus)

L'ordre des champs dans une PK composite est-il important

Dans la PK non. Dans l'index oui. Donc si on s'appuie sur la création implicite de l'indexe, alors oui.

exemple {A, B} mieux que {B, A} quand on recherche/joint plus souvent par A}

Oui. Un index sur {A, B} peut optimiser la recherche A=xxx.

et si PK = {A, B} et qu'on cherche aussi bien par A seul que B seul, doit-on ajouter un index sur B uniquement

Oui. En se souvenant qu'un index n'est utile que pour une recherche très sélective. Si le critère sur B ramène la moitié de la table, l'index n'est pas utile pour cette recherche.

A partir de quelle taille un index est-il utile?

Ce n'est pas la taille de la table qui compte mais la sélectivité de l'index. Un index est utile s'il permet de n'aller voir que quelques enregistrements de la table. Ou plutôt, de n'aller voir que quelques blocs de la table (et là le 2ème critère est le clustering factor ... mais on va un peu trop loin)

j'ai lu qu'un index {A, B, C} sera moins performant qu'un index {A, B} si on ne cherche que sur A et B

Probablement pas de différence, sauf si la taille de C est très grosse, et que la hauteur de l'index est plus grande à cause de cela. Par contre, 2 index au lieu d'un ça peut doubler les temps de réponse des inserts ou delete, des update des colonnes indexées, des opérations de maintenance, etc. Donc à éviter s'il n'y a pas de bonnes raisons.

Et attention:
- une contrainte d'intégrité ne sert pas seulement à empêcher les doublons. C'est la déclaration en base de donnée des cardinalités et l'optimiseur utilise ses informations précieuses lorsqu'il établit un plan d'exécution
- un index ne sert pas seulement à optimiser un accès. Avoir les foreign key comme préfixe d'un index permet d'éviter qu'un delete (entre autres) de la table parent ne vérouille toute la table fille

Cordialement,
Franck.

[pachot]

Bonjour SQLpro,

Envoyé par SQLpro

Dans mes cours je m'amuse à faire calculer à mes élèves le cout d'une jointure de 30 tables de 100 millions de lignes distincte dont la jointure se fait sur un entier... Vous verrez qu'il est tout à fait négligeable, si les deux côtés de la jointure sont pourvus d'index (exactement 180 pages à lire). En revanche, sans index d'un seul côté, on passe à 4 500 000 pages !

Je précise quand même que ce calcul suppose que les jointures ramènent un nombre très faible d'enregistrement. 180 pour lire 30 indexes, c'est qu'on ne va chercher que une ou 2 lignes dans chaque table.
S'il s'agissait de ramener 1000 lignes de chaque table, l'accès par index serait une catastrophe.

Cordialement,
Franck.

[Waldar]

1000 lignes sur 100 millions, je pense que l'index est encore largement efficace !

[Mohamed.Houri]

C'est très bien de prendre le temps et toute la pédagogie nécessaire pour expliquer des concepts Oracle comme que vous venez de le faire.

Si votre clef primaire est Col1 ASC, Col2 DESC, Col3 ASC et que l'index de votre FK est Col1 ASC, Col2 ASC, Col3 ASC alors il n'est pas possible d'utiliser les index pour résoudre avec vélocité la jointure.
La composition doit porter :
2) sur la même position ordinale

L'indexation de la FK n'est pas uniquement utile pour des raisons de performance. Je dirai même qu'elle est avant tout primordiale dans un environnement OTLP afin d'éviter des locks et des deadlocks lorsque des delete/update/merge sont faits sur les tables mères (parent table). Quant à la définition de cette FK je suis d'accord avec le fait que toutes les colonnes de cette FK doivent figurer dans l'indexe qui la couvre. Par contre, il n'est vraiment pas nécessaire que l'ordre des colonnes dans l'indexe doive coïncider parfaitement avec celui défini dans la contrainte FK. Il suffit que l'index commence par la composition des colonnes de la FK mais dans n'importe quel ordre ; ceci permettra de couvrir la menace des locks et deadlocks. Pour la performance, comme la jointure se fait sur toutes les colonnes de la FK, l’index de la FK, sera éventuellement utilisé par le CBO (Optimisateur d’Oracle) pourvu que cet index commence par les colonnes de la FK et dans n’importe quel ordre.

J'ai résumé l'explication ci-dessous dans le simple article suivant:

http://hourim.wordpress.com/2011/02/...-foreign-keys/

3) sur le même sens de tri

je vais essayer cela

[pachot]

Bonjour Mohamed,

Envoyé par Mohamed.Houri

3) sur le même sens de tri
je vais essayer cela

Je ne vois pas pourquoi l'ordre changerait quelque chose pour la jointure.
Ce qu'il faut, c'est que l'index puisse ramener le minimum de lignes correspondant au prédicat. C'est pour cela qu'il est important qu'il couvre toutes les colonnes.
Mais son sens de tri ne concerne que les tris, pas les accès.

Sauf peut-être pour un SORT MERGE JOIN qui ne pourrait pas utiliser l'index pour pour trier...

Mais c'est bien de tester... peut-être l'occasion d'ouvrir un bug de plus sur les function Based index puisque c'est comme ça qu' Oracle implémente les colonnes descending.

Cordialement,
Franck.

[stof]

Tout d'abord merci à tous; c'est incroyable de voir toutes ces personnes qui prennent le temps d'aider les autres!

Envoyé par SQLpro

Pour la table fille, en indexant en sus les colonnes de la FK dans la même composition que la clef de référence, (...)
Dans mes cours je m'amuse à faire calculer à mes élèves (...)

J'ai eu un peu de mal à saisir l'exemple; s'agit-il d'un exemple ou on a TAB_A (qui a certains champs dont un entier), TAB_B (qui a certains champs dont un entier)
et dont on fait une jointure dans une requête entre ces 2 colonnes d'entier (qui ne sont indexées ni par PK ni par FK)?
Si oui, je comprend que ça fonctionne mal, sinon j'ai mal saisi la subtilité de l'exemple.

Envoyé par SQLpro

Si votre clef primaire est Col1 ASC, Col2 DESC, Col3 ASC et que l'index de votre FK est Col1 ASC, Col2 ASC, Col3 ASC alors il n'est pas possible d'utiliser les index pour résoudre avec vélocité la jointure.
La composition doit porter :
1) sur les mêmes colonnes et en même nombre
2) sur la même position ordinale
3) sur le même sens de tri

Je n'ai pas d'ordre de tri sur les clef primaires composites quand je regarde le script de ma table (contrairement aux index).
J'ai donc lu que si rien n'est précisé, c'est "asc" par défaut.
Je suis un peu perdu mais dans cet exemple si on ne crée que la PK, on va avoir un index implicite (Col1 ASC, Col2 DESC, Col3 ASC) si j'en crois ce que me dit pachot ("PK implique unicité. (...) soit un index comme cela existe déjà, soit il est créé implicitement") et ce que vous dites c'est qu'on ne pourra pas créer un index sur partie ou totalité de ces champs?
Pourtant j'ai dans ma base des exemples avec PK = {A, B} et en plus un index sur A --> il sert à rien?

Envoyé par SQLpro

Lisez les articles que j'ai écrit sur ces sujets, notamment :
1) Tout sur l'index (1ere partie)
2) Indexer avec SQL... oui mais quoi ?
3) Est-il bon de ne pas avoir d'index ni de clef dans une table ?
4) Indexation... une étude par l'exemple

Alors ça j'y manquerai pas; j'étais pas encore tombé ici mais je lirai tout ça.


----------------

Envoyé par pachot

L'ordre des champs dans une PK composite est-il important?
-->Dans la PK non. Dans l'index oui. Donc si on s'appuie sur la création implicite de l'indexe, alors oui.
et si PK = {A, B} et qu'on cherche aussi bien par A seul que B seul, doit-on ajouter un index sur B uniquement
-->Oui.

Ah, avec en plus la partie de l'explication de Mohamed.Houri, alors je comprend que
PK {A, B} génère implicitement l'index {A, B} et que si on cherche plus souvent par B, il faudrait ajouter l'index explicite {B, A} alors que si on cherche aussi bien par B ou par A il ne restera qu'à ajouter un index sur {B}
Et au passage, admettons que j'ai une PK sur {A, B} et 2 index : un sur A et un sur B (parce que les personnes qui ont généré la base l'ont fait comme ça).
Est-il intéressant de nettoyer l'index inutile sur A (ou ça ne change rien)?

Envoyé par pachot

A partir de quelle taille un index est-il utile?
-->Ce n'est pas la taille de la table qui compte mais la sélectivité de l'index.

Bravo d'avoir compris ma question mal posée (on ne comprenait pas qu'effectivement je parlais de la taille de la table).
Intéressant la sélectivité : dans le cas d'une table de 10.000 lignes avec un flag 'V' ou 'F' dans une colonne (disons 50% / 50% par exemple), on n'a donc pas d'intérêt à y mettre un index dessus?

Envoyé par pachot

un index ne sert pas seulement à optimiser un accès.
Avoir les foreign key comme préfixe d'un index permet d'éviter qu'un delete (entre autres) de la table parent ne vérouille toute la table fille

Je sens qu'il y a un concept important là, mais mes neurones n'ont pas réussi à le décoder... je crois que c'est la partie "Avoir les foreign key comme préfixe d'un index" qui m'échappe.

[Mohamed.Houri]

Si votre clef primaire est Col1 ASC, Col2 DESC, Col3 ASC et que l'index de votre FK est Col1 ASC, Col2 ASC, Col3 ASC alors il n'est pas possible d'utiliser les index pour résoudre avec vélocité la jointure.

Je pense que vous voulez dire si votre index de la clé primaire est col1 asc, col2 desc, col3 desc.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
mhouri > CREATE TABLE test (col1 number, col2 number, col3 number, x varchar2(10));
 
TABLE created.
 
mhouri > CREATE UNIQUE INDEX ind_test ON test (col1 ASC, col2 DESC, col3 DESC);
 
INDEX created.
 
mhouri >ALTER TABLE test ADD constraint test_pk PRIMARY KEY (col1,col2,col3) USING INDEX ind_test;
ALTER TABLE test ADD constraint test_pk PRIMARY KEY (col1,col2,col3) USING INDEX ind_test
*
ERROR at line 1:
ORA-14196: Specified INDEX cannot be used TO enforce the constraint.

Je ne peux donc pas créer une PK sur la base de ce que vous avez proposé; la question du tri sur l'index de la PK ne se pose donc même pas.

Quant au tri (col1 desc, col 2 asc, col3 desc ) des colonnes dans l'index sensé couvrir la FK, je dirai que dès que ce genre de tri est inclus dans l'index ce dernier devient un "Function Based Index". Sachant que ce type d'indexe(et les bitmap index aussi) ne nous protège pas de la menace des locks et des deadlocks, il faut donc créer un index du type B-tree commençant par les colonnes de la PK dans n'importe quel ordre (position)

[pachot]

Bonjour,

PK {A, B} génère implicitement l'index {A, B}

Oui exact.

et que si on cherche plus souvent par B, il faudrait ajouter l'index explicite {B, A}

au lieu d'avoir 2 index, il peut y avoir la PK {A, B} qui utilise explicitement un index déjà créé sur {B, A}. Ou simplement créer la PK {A, B} et laisser l'index implicite.

alors que si on cherche aussi bien par B ou par A il ne restera qu'à ajouter un index sur {B}

Oui. Mais il faut tenir compte de la sélectivité, des prédicats avec des inégalités, ...

Et au passage, admettons que j'ai une PK sur {A, B} et 2 index : un sur A et un sur B (parce que les personnes qui ont généré la base l'ont fait comme ça).
Est-il intéressant de nettoyer l'index inutile sur A (ou ça ne change rien)?

Oui, il est probablement inutile. Sauf si B est trés gros, mais ce n'est probablement pas le cas pour un identifiant.

Intéressant la sélectivité : dans le cas d'une table de 10.000 lignes avec un flag 'V' ou 'F' dans une colonne (disons 50% / 50% par exemple), on n'a donc pas d'intérêt à y mettre un index dessus?

Exact. L'accès par index ferait 5000 lectures (une pour chaque entrée d'index) alors qu'un full scan pourrait lire toute la table en quelques lectures seulement (on peut souvent lire 1Mo en un seul I/O).

je crois que c'est la partie "Avoir les foreign key comme préfixe d'un index" qui m'échappe.

imagine une table avec des colonnes A,B,C,D où A est une foreign key sur une table de référence. Si on supprime un enregistrement de la table de référence il faut empêcher que d'autres utilisateurs insèrent un enregistrement fils qui le référence. Sans index, il faut vérifier et vérouiller toute la table. Avec un index sur A , ou un index sur A,B , ou un index sur A,B,C alors Oracle va utiliser la structure d'index pour vérifier et vérouiller uniquement sur la valeur de A qui est supprimée de la table maître.

Là, l'ordre est important. Un index B,A ne peut pas être utilisé pour cela.

Cordialement,
Franck.

[stof]

Envoyé par pachot

imagine une table avec des colonnes A,B,C,D où A est une foreign key sur une table de référence. Si on supprime un enregistrement de la table de référence il faut empêcher que d'autres utilisateurs insèrent un enregistrement fils qui le référence. Sans index, il faut vérifier et verrouiller toute la table. Avec un index sur A , ou un index sur A,B , ou un index sur A,B,C alors Oracle va utiliser la structure d'index pour vérifier et verrouiller uniquement sur la valeur de A qui est supprimée de la table maître.

Si on supprime un enregistrement de la table de référence, ça va simplement répondre "integrity constraint (FK_A) violated" avec ou sans index, non? Je n'ai pas compris l'histoire du verrouillage (ou alors tu parles d'un delete cascade?)

[stof]

Envoyé par SQLpro

2)Un index sur une colonne d'une table qui possède une FK sur une autre table joue-t-il dans la performance des jointures faites entre ces 2 tables?
Vous ne pouvez pas greffer une FK sur autre chose que :

une clef primaire (déjà naturellement indexée)
une contrainte d'unicité (déjà naturellement indexée)

J'ai une table TOTO (TOTOIDT, TITIIDT, VALUE1)
J'ai une table TITI (TITIIDT, TUTUIDT, VALUE2)
-->je veux savoir si un index sur le champ TITIIDT de ma table TOTO va améliorer les performances de ma jointure
Ma requête :

Code :

1
2
3
SELECT * FROM TOTO t1
JOIN TITI t2 ON t2.TITIIDT = t1.TITIIDT 
WHERE lr.TUTUIDT= 1500

Avec index sur TOTO.TITIIDT

Code :

1
2
3
4
5
6
7
8
9
10
----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |   100 |  4800 |    71   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID  | TOTO         |     3 |    63 |     3   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                |              |   100 |  4800 |    71   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| TITI         |    32 |   864 |     4   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | TUTUTITI_FK  |    32 |       |     3   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN           | TITITOTO_FK   |     3 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Sans index sur TOTO.TITIIDT

Code :

1
2
3
4
5
6
7
8
9
---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |   100 |  4800 |   241   (5)| 00:00:03 |
|*  1 |  HASH JOIN                   |              |   100 |  4800 |   241   (5)| 00:00:03 |
|   2 |   TABLE ACCESS BY INDEX ROWID| TITI         |    32 |   864 |     4   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | TUTUTITI_FK  |    32 |       |     3   (0)| 00:00:01 |
|   4 |   TABLE ACCESS FULL          | TOTO         |   238K|  4887K|   233   (4)| 00:00:03 |
---------------------------------------------------------------------------------------------

Donc il semble que non seulement on peut très bien ajouter un index sur une FK qui ne soit ni une PK ni une contrainte d'unicité et que les performances s'en trouvent améliorées.

Par contre absolument 0 différence sur la simple requête (idem mais sans filtre sur un champ de la table TITI):

Code :

1
2
SELECT * FROM TOTO t1
JOIN TITI t2 ON t2.TITIIDT = t1.TITIIDT

Code :

1
2
3
4
5
6
7
8
9
 
-------------------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |   238K|    10M|       |  3502   (3)| 00:00:43 |
|*  1 |  HASH JOIN         |        |   238K|    10M|  7688K|  3502   (3)| 00:00:43 |
|   2 |   TABLE ACCESS FULL| TOTO   |   238K|  4887K|       |   233   (4)| 00:00:03 |
|   3 |   TABLE ACCESS FULL| TITI   |   967K|    24M|       |  1080   (4)| 00:00:13 |
-------------------------------------------------------------------------------------

[stof]

Envoyé par SQLpro

Un index UNIQUE est l'équivalent d'une contrainte d'unicité.
MAIS...

• la contrainte est un objet logique (pas besoin en théorie d'un index pour vérifier l'unicité)
• l'index est un objet physique (il sert accélérer certaines recherches et notamment s'il existe déjà une valeur identique - fort utile pour le cas de vérification d'unicité...)

-->donc je crois que je vais ajouter les 2
-un pour l'aspect logique (modélisation)
-un pour les performances de recherche

[Mohamed.Houri]

Envoyé par stof

Si on supprime un enregistrement de la table de référence, ça va simplement répondre "integrity constraint (FK_A) violated" avec ou sans index, non? Je n'ai pas compris l'histoire du verrouillage (ou alors tu parles d'un delete cascade?)

Il ne faut toujours pas perdre de vue que vous ne serez pas tout seul à utiliser la base de données et donc pas tout seul à manipuler la table de référence

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
 
mhouri.world> CREATE TABLE p AS SELECT * FROM all_users;
 
TABLE created.
 
mhouri.world> SELECT count(1) FROM p;
 
  COUNT(1)                                                                      
----------                                                                      
        32                                                                      
 
mhouri.world> ALTER TABLE p ADD constraint p_pk_c PRIMARY KEY (user_id);
 
TABLE altered.
 
mhouri.world> CREATE TABLE c (user_id REFERENCES p, DATA varchar2(10));
 
TABLE created.
 
mhouri.world> SELECT min(user_id), max(user_id) FROM p;
 
MIN(USER_ID) MAX(USER_ID)                                                       
------------ ------------                                                       
           0           74                                                       
 
mhouri.world> INSERT INTO c(user_id,DATA) VALUES (0,'test');
 
1 row created.
 
mhouri.world> declare
  2   pragma autonomous_transaction;
  3   begin
  4   DELETE FROM p WHERE user_id = 74;
  5  commit;
  6   end;
  7  /
declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting FOR resource 
ORA-06512: at line 4

"l'autonomous transaction" est ici pour simuler deux sessions (deux transactions différentes pour être plus précis). Comme il n'y a pas d'index sur la FK de la table fille, il se produit ce que vous voyez en haut.

Créons maintenant un index B-tree (très important B-tree) et voyons ce qu'il advient de notre delete

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
mhouri.world> rollback;
 
Rollback complete.
 
mhouri.world> CREATE INDEX child_fk_i ON c(user_id);
 
INDEX created.
 
mhouri.world> declare
  2   pragma autonomous_transaction;
  3   begin
  4   DELETE FROM p WHERE user_id = 74;
  5  commit;
  6   end;
  7  /
 
PL/SQL procedure successfully completed.
 
mhouri.world> spool off

Plus de deadlock!!!

J'espère que c'est clair.

[pachot]

Envoyé par stof

Si on supprime un enregistrement de la table de référence, ça va simplement répondre "integrity constraint (FK_A) violated" avec ou sans index, non? Je n'ai pas compris l'histoire du verrouillage (ou alors tu parles d'un delete cascade?)

Oui, ça va répondre "integrity constraint (FK_A) violated" s'il y a un enregistrement fils qu'on peut voir, c'est à dire qui est commité.
Mais s'il y a un autre utilisateur qui veut ajouter un enregistrement fils, mais qui n'a pas encore terminé sa transaction, alors on ne le verra pas. Et lui ne verra pas notre delete en cours non plus - il croira que l'enregistrement père est toujours là. c'est le principe de l'isolation des transactions.

C'est pour cela qu'il faut poser un verrou:
- le premier pose un verrou et fait son opération
- le deuxième essaie de poser un verrou et attend
- si le premier commit, alors le deuxième reçoit une erreur de violation de contrainte
- si au contraire il rollback, alors le premier peut continuer

[stof]

Envoyé par Mohamed.Houri

Créons maintenant un index B-tree (très important B-tree) (..)

C'est un index classique B-tree non ?

Envoyé par pachot

Mais s'il y a un autre utilisateur qui veut ajouter un enregistrement fils, mais qui n'a pas encore terminé sa transaction, alors on ne le verra pas. Et lui ne verra pas notre delete en cours non plus ...

Ma base est en mode Row Lock; est-ce que cette explication sur l'utilité des index sur les FK du coup ne me concerne plus du tout?

[pachot]

Envoyé par stof

Ma base est en mode Row Lock; est-ce que cette explication sur l'utilité des index sur les FK du coup ne me concerne plus du tout?

Oracle fait toujours des verrous lignes. Ce n'est pas le problème ici.
Il faut poser un verrou pour empêcher des modifications concurrentes incompatible et c'est l'index sur la FK qui offre la structure qui évite de vérouiller toute la table.

[stof]

Envoyé par pachot

Il faut poser un verrou pour empêcher des modifications concurrentes incompatible et c'est l'index sur la FK qui offre la structure qui évite de verrouiller toute la table.

1°/Mais du coup faudrait-il généraliser la création d'index sur les FK ou les mettre quand on suppose qu'on a une bonne chance d'essayer de supprimer un enregistrement de la table parent?

2°/J'ai un cas où j'ai un index unique sur {A, DATE, C, D, E}
Souvent je cherche par {A, trunc(DATE)}, d'autres fois par {A, trunc(DATE), C}.
Actuellement j'ai les index {A, DATE} et C en plus de l'index unique; utile ou pas (sachant que C a une FK)?
(pour les trunc, je sais que ça ne passe pas par l'index donc faut que je vois si on peut limiter ça...vu que c'est une date sans heure)

3°/Soit 4 colonnes {A, B, C, D, E, F}
Il m'arrive de chercher en filtrant par {B, C, D, E} dans certaines requêtes d'une part, mais d'autre part comme B, C, D et E ont des FK vers d'autres tables et qu'on fait des jointures dessus, je me demande si dans ce cas il vaut mieux créer 4 index distincts plutôt que {B, C, D, E}

4°/Enfin, pouvez-vous me confirmer que ce que j'ai dis dans les post #12 et #13 est exact?


merci.

[mnitu]

Envoyé par Mohamed.Houri

..."l'autonomous transaction" est ici pour simuler deux sessions (deux transactions différentes pour être plus précis). Comme il n'y a pas d'index sur la FK de la table fille, il se produit ce que vous voyez en haut.
....

Refaite votre exemple avec deux vrai sessions et vous allez constater un inter-blocage c’est sur mais, pas un deadlock !

[stof]

Bon, promis mon dernier post (ci-dessus #18) sera le dernier et après je ferme le sujet

Enfin bon un petit dernier avant de partir :

5)J'ai un index unique sur {A, B, C, D, E}
J'ai choisi cet ordre pour les critères de tri de fréquence décroissante...mais je me pose la question sur D qui est utilisée fréquemment pour faire des jointures avec une autre table.
Pffff, pas facile... un index EN PLUS sur D?