Discussion :

[SQLpro]

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.

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/

je vais essayer cela

L'ordre est TOUT A FAIT primordial (mais pas dans tous les cas), si vous faites :
1) des théta jointures
2) du MATCH partial
Sinon l'index ne sera pas pris en compte.

Je donne souvent en cours cet exemple dans mes cours :

CREATE TABLE T_TEMPS
(AN SMALLINT NOT NULL,
MOIS SMALLINT NOT NULL,
JOUR SMALLINT NOT NULL,
CONSTRAINT PK PRIMARY KEY (AN, MOIS, JOUR))

--> puis table de faits historique avec FK en MATCH PARTIAL....

A +

[SQLpro]

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?

Oui, mais....

Là encore vous auriez dû lire mes articles

partez du principe que tout index a un coût, notamment pour les mises à jour... Chaque index étant alimenté de manière synchrone à chaque INSERT, UPDATE, DELETE.

Donc, un index sera efficace et intéressant si :

• Il y a beaucoup de requêtes qui vont utiliser cet index, soit en recherche, soit en lecture
• Le ratio de performance est important, entre la version sans index et la version indexée.

Cela veut dire que pour une petite table (quelques milliers de lignes), les index sont peu intéressants, alors que lorsque l'on commence à tailler dans le million de lignes, ils sont tellement efficace que s'en passer tuerai l'exploitation de la base !

N'ayez donc pas peur d'indexer, si chaque index que vous posez prouve une bonne efficacité.

Et n'oubliez pas que la plupart des ordres de mise à jour (UPDATE et DELETE en particulier, mais même l'INSERT dans beaucoup de cas) commence par une lecture positionnelle qu'un index peut grandement améliorer !!!

A +

[Mohamed.Houri]

L'ordre est TOUT A FAIT primordial (mais pas dans tous les cas), si vous faites :
1) des théta jointures
2) du MATCH partial
Sinon l'index ne sera pas pris en compte.

Pourriez vous donner un exemple ORACLE bien détaillé qui montre (Edit : reformulation du texte en supprimant la référence à plus formateur etc..)

1. que l'ordre des colonnes de l'index couvrant la FK doit être exactement le même que celui défini dans la contrainte d'intégrité sinon on sera confronté à un problème de lock (deadlocks) en cas de suppression de la table mère

2. que l'ordre des colonnes de l'index couvrant la FK doit être exactement le même que celui défini dans la contrainte d'intégrité sinon cet index sera moins performant dans le cas des jointures que vous avez exposées.

Je répète que toutes mes interventions concerne Oracle et que l'ordre des colonnes d'un index B-Tree importe peu si la requête qu'il est sensé couvrir:

(a) invoque toutes les colonnes de cet index
(b) toutes les colonnes de cet index sont invoquées via une égalité dans les clauses where
(c) et,si l'index contient plus de colonnes que ce que la requête invoque, il doit commencer par les colonnes invoquées dans la requête mais dans n'importe quel ordre

Comme une contrainte d'intégrité(FK) est sujette à contenir plus d'une ligne par clé FK, il est plus judicieux de placer la colonne la moins répétée (celle ayant le plus petit nombre de valeurs distinctes) en tête d'index afin de pouvoir la compresser efficacement.

[pachot]

Je réponds brièvement...désolé:

1°/

Oui, exact.

2°/

pour le trunc, aulieu de trunc(DATE)=x faire date>=x and date<x+1
{A, DATE} et C probablement inutiles pour ce besoin, oui, car {A, DATE, C, D, E} peut être utilisé aussi.


3°/

L'accès via des index distincts sera beaucoup beaucoup moins optimal qu'un index {B, C, D, E}
Sauf dans le cas d'index bitmap... mais c'est une autre histoire.

4°/

#12 oui, contrainte et index. Mais l'index implicite est souvent le bon.

#13 oui index utile pour selectionner peu de données. Mais sans prédicats, mieux vaut un full scan.

[stof]

Là encore vous auriez dû lire mes articles

Ah non, c'est trop injuste, je les ai lu désormais (au passage j'ai noté plein de trucs à étudier, comme la notion d'index filtré qui peut énormément m'intéresser EDIT : ça doit pas exister en Oracle parce que les syntaxes que j'essaie ne marchent pas!)

je vais reformuler une question que j'ai déjà posée mais j'ai du mal à trouver la réponse dans les derniers messages.

Si un index sur une colonne qui possède une PK est "perdue" en plein milieu d'un index composite (qu'il soit unique ou pas, je ne crois pas que ça change quelque chose), la création d'un index seul sur cette colonne peut-il améliorer les performances d'une jointure avec sa table parent?
{A, B, C, D, E, F}
--> les jointures de la colonne E sont-elles efficaces avec ça ou est-ce que si on a un index E isolé en plus on gagne?

Même question pour une clé primaire {A, B} qui crée l'index implicite {A, B} --> les jointures sur A ou B peuvent-elles êtres améliorées par des index sur A + sur B.

Après c'est tout

[Rams7s]

Il aurait été largement plus édifiant et plus formateur pour moi et certainement pour les autres que vous donniez un exemple ORACLE bien détaillé qui montre
[...]

l'ordre des colonnes d'un index B-Tree importe peu si la requête qu'il est sensé couvrir:

(a) invoque toutes les colonnes de cet index
(b) toutes les colonnes de cet index sont invoquées via une égalité dans les clauses where

Ca aurait ete formateur de lire attentivement ce qu'il y avait de note par SQLPro.
Votre a correspond au 2 de sa citation que vous utilisez.
Et votre b, a son 1.

[Mohamed.Houri]

Ca aurait ete formateur de lire attentivement ce qu'il y avait de note par SQLPro.
Votre a correspond au 2 de sa citation que vous utilisez.
Et votre b, a son 1.

(a) invoque toutes les colonnes de cet index --> mon (a)
2) du MATCH partial ---> son (2)

(b) toutes les colonnes de cet index sont invoquées via une égalité dans les clauses where ---> mon (b)
1) des théta jointures ---> son (1)

Quelqu'ait été la réponse de SQLPRO, je repète le coeur du message que je veux faire passer

1. afin de se prémunir des problèmes de deadlocks lorsque nous supprimons des données de la table mère, il faut indexer la FK de la table fille. Il n'est pas nécessaire pour cela que l'ordre des colonnes dans cet index soit exactement le même que celui défini dans la contrainte.

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

Si vous n'etes pas d'accord venez avec un contre exemple.

2. concernant la performance des requêtes sur les jointures, ce n'est pas en quelques phrases que nous allons prévoir tous les cas des indexes performants. Ce sont justement nos requêtes (et nos clause wheres) qui dictent la nature de nos indexes et non l'inverse.

[stof]

Ouais, pour conclure je suis toujours complètement paumé : à chaque fois que je crois comprendre mieux la théorie, je fais des tests et bing, dans les choux.

Le dernier en date où j'ai 3 index distincts A, B, C
J'ai cru bon de faire {A, B, C} puisque parfois on cherche par ces 3 critères mais dans d'autres cas si je fais une jointure sur B, je perd en performance (cost 1069 contre 102... une broutille quoi!).

C'est un sacré sac de noeuds parce qu'on va pas recenser pour chaque table toutes les requêtes et les tester une par une avec toutes les combinaisons d'index possibles...

Bon, fin du sujet.
Merci à tous.
je laisse ouvert pour quelques dernières réactions et puis c'est bon pour moi (façon de parler...)

[pachot]

Salut,

Même question pour une clé primaire {A, B} qui crée l'index implicite {A, B} --> les jointures sur A ou B peuvent-elles êtres améliorées par des index sur A + sur B.

Oui, il peut être nécessaire d'ajouter un index sur B parce que l'index sur {A, B} ne servira pas à grand chose pour un WHERE B=xxx
Sauf quelques rares cas. Par exemple, s'il y a très peu de valeurs distinctes de A alors Oracle pourrait faire un INDEX SKIP SCAN en utilisant {A, B}

Pour mieux comprendre:
- un index, c'est une structure triée
- Un index composite, c'est comme si les colonnes étaient concaténées
Donc un prédicat sur une colonne sans avoir le début ne sert pas à grand chose.

On va prendre une analogie et voir un index comme un annuaire - pages blanches.
- Il y a un annuaire par département.
- Dans chacun il y a une section pour chaque ville
- Et là dedans, c'est trié par ordre alphabétique du nom.
- et il y a un numéro de téléphone en face de chacun.

1er cas)

Je cherche Monsieur Oracle à Avignon dans le Vaucluse.
On suppose qu'il n'y en a qu'un parce que c'est pas courant comme nom à Avignon...
-> je prends le bottin du Vaucluse
-> je cherche La section Avignon
-> je cherche le O ... puis le R ... etc

Et je tombe assez vite sur le numéro de téléphone. J'ai probablement parcouru 3 ou 4 pages pour trouver ça
C'est un INDEX UNIQUE SCAN

2ème cas)

Je cherche tous les abonnés téléphoniques d'Avignon.
-> je prends le bottin du Vaucluse
-> je cherche La section Avignon
-> et je lis du début jusqu'à la fin

J'ai probablement lu une 50aine de pages. C'est un INDEX RANGE SCAN.
Même si je n'avais que le début, la structure triée m'a permis de trouver ça de manière plus performante qu'un FULL SCAN (qui correspondrait à la lecture de toutes les pages de tous les annuaires)

3ème cas)
Je cherche tous les Monsieur Oracle en France.
Là il me faudrait lire tous les annuaires, aller voir toutes les villes, et y chercher O..R...A...
S'il y a peu de départements, et peu de villes, ça reste possible. C'est un INDEX SKIP SCAN.
Sinon, aucune utilité d'avoir une structure triée. Autant faire un FULL SCAN.

Pour ce type de recherche, on pourrait imaginer un annuaire avec tous les abonnés de France par ordre alphabétique.
Là on pourrait aller chercher O..R..A.. et trouver tous les abonnés nommés Oracle rapidement.

tout ça pour répondre à la question: oui, on peut être amené à créer un autre index si la colonne est indexée dans un index existant, mais pas en première position.

Voilà, j'espère que ça éclaircit l'utilité des index. Et je vais continuer sur cette analogie, parce que souvent on ne trouve pas toutes les infos qu'on veut dans l'index et on est obligé d'aller voir d'autres colonnes dans la table. On imagine qu'on veuille l'age de la personne en plus de son numéro de téléphone. Alors il faut appeler la personne pour lui demander son age.
Dans le cas 1 ça n'est pas beaucoup plus coûteux: on a un numéro à appeler. Rapide.
Dans le cas 2 autant c'était assez rapide d'avoir la liste des numéros de téléphone, ce serait interminable d'appeler tous les habitants d'Avignon.
C'est le coût caché de l'accès par index: c'est rapide pour filtrer des données. Mais s'il y en a beaucoup à aller voir ensuite, ça peut être très lourd.

C'est un sacré sac de noeuds parce qu'on va pas recenser pour chaque table toutes les requêtes et les tester une par une avec toutes les combinaisons d'index possibles...

Non. Par contre connaître pour chaque table les différents axes par lesquels on va les interroger, oui. C'est utile et c'est pas forcément très lourd.

Cordialement,
Franck.

[IndianaAngus]

J'adore ton analogie avec les annuaires... C'est très clair et précis. Je vais me la mettre de côté

Cela veut dire que pour une petite table (quelques milliers de lignes), les index sont peu intéressants, alors que lorsque l'on commence à tailler dans le million de lignes, ils sont tellement efficace que s'en passer tuerai l'exploitation de la base !

Désolé, mais je peux te montrer des tas d'exemples ou une base avec seulement quelques milliers de lignes (voir moins) sont très dépendant des indexs! Ou alors je n'ai pas compris ton message.

[stof]

Juste pour dire que je trouve aussi l'explication ci-dessus très claire