Discussion :

[stof]

Bonjour,

Je viens de me documenter sur les index.

Il me reste 2 questions :
1)Est-il (parfois) utile de créer des index sur les PK d'une table (ou une partie des champs si c'est une PK composite)

2)Quelle différence entre créer 4 index sur les champs A, B, C, D ou un index sur {A, B, C, D}.

Merci.

[mnitu]

Le PK implique toujours l’index. Il est rarement utile d’indexer une partie des zones d’une clé primaire composite.
Si vous avez des requêtes qui filtre que sur la colonne A ou que sur la colonne B etc. vous avez peut être besoin d’un index sur A et un autre sur B, etc. L’index composite A, B, C, D est bon pour les requêtes qui utilisent des filtres sur l’ensemble des zones : A, B, C et D ou sur une partie de cet ensemble : A, B, C ou A, B ou A seul. Dans certaines cases l’index peut être utilisé quand A manque, ex : filtre sur B, C, D
En conclusion, vos indexes sont déterminés par vos requêtes.

Sur divers aspects concernant les indexes, voir aussi Richard Foote's blog

[Mohamed.Houri]

La première colonne d'un index est cruciale. Par exemple pour couvrir précisément une requête du type:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
 where
      A = :1
 and
     B  = : 2

Vous pouvez créer indifféremment un index sur (A,B) ou (B,A). Si on veut aller plus loin, je dirai qu'il vaudrait mieux mettre dans ce cas la colonne la moins répétitive (celle avec le plus petit nombre de valeur distinctes) en pôle position. L'index ainsi crée pourra éventuellement être très efficacement compressé réduisant sa taille et ainsi plus facilement logé dans le "data buffer cache" afin de limiter ses lectures physiques.
Par contre, si en plus de votre requête précédente, vous avez une autre requête du type:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
 where
      B  = :1

Alors, il vaudrait mieux dans ce cas créer le premier index sur (B,A) et non sur (A,B). Vous pourriez ainsi économiser la création d'un index supplémentaire sur (B).
Quant à la différence entre créer quatre indexes sur les champs A, B, C, D ou un index sur {A, B, C, D}, je dirai que l'index sur {A, B, C, D} couvrira les deux requêtes suivantes:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
 
 where
       A  = :1
 and B  = :2
 and C  = :3
 and D  = :3
 
et
 where
       A  = :1

Alors que les 4 autres indexes couvriront chacun un seul type de requête qui sont respectivement :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
 
 where
       A  = :1;
 
 where
       B  = :1;
 
 where
       C  = :1;
 
et
 where
       D  = :1;

Vous voyez maintenant l'importance de la première colonne d'un index?
Une autre remarque importante, admettons que vous avez une requête du type :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
 where
       A  >= :1
 and B = 2;

Alors dans ce cas pensez bien à créer l'index sur (B,A) et ce quelque soit la cardinalité de A. En effet, il faut que la première colonne soit celle qui s'applique à l'égalité.

Et voici ci-dessous un cas réél où la création d'indexes appropriés a permis d'améliorer la performance d'une requête

http://hourim.wordpress.com/2011/12/...-explain-plan/

[stof]

Le PK implique toujours l’index. Il est rarement utile d’indexer une partie des zones d’une clé primaire composite.

OK; c'est bizarre qu'Oracle nous laisse créer un index qui fait double emploie alors (ou c'est plutôt le "rarement" qui m'échappe)

(...)En conclusion, vos indexes sont déterminés par vos requêtes.

Je note donc que je dois repérer les critères dans l'ensemble de mes requêtes pour déterminer comment exprimer mes index (colonnes groupées si j'utilise régulièrement plusieurs colonnes en même temps, séparés si j'utilise tantôt l'une ou l'autre).

La première colonne d'un index est cruciale.(...)

Oui, ça je l'ai lu juste avant de poser ma question. J'étudierai aussi ça.

Merci à vous deux.

[stof]

J'ai 3 autres questions
1°/ Si je fais un tri selon une colonne (sans clause where), est-ce TRES utile de créer un index?


2°/ Table : ApkFk, BpkFk, C, D, E
Comme leur nom l'indique, j'ai une clé primaire composite {ApkFk, BpkFk } dont chaque champ fait référence à une table externe.

Je vais un moment faire une requête sur cette table (jointure les 2 autres) où je filtre par critère sur
-C
-D
ainsi que sur des propriétés de mes tables jointes.

Que dois-je ajouter comme index : {C, D}?

3°/J'ai un index unique sur {A, B, C, D, E}
La majeure partie du temps je recherche par {A, B, C, D} sauf dans certains cas où je recherche sur {A, C, D} + critère sur un champ de la table jointe par B
--> dois-je remplacer l'index unique par {A, C, D, B, E} (inverser l'ordre)?

[Waldar]

1°/ Si je fais un tri selon une colonne (sans clause where), est-ce TRES utile de créer un index?

Ça dépend de ce que vous mettez dans votre SELECT.
Si votre SELECT est couvert par un éventuel index, oui ça peut être intéressant.
Si votre SELECT est plus du type "select *", alors ça n'aura aucun impact.

2°/ Table : ApkFk, BpkFk, C, D, E
Comme leur nom l'indique, j'ai une clé primaire composite {ApkFk, BpkFk } dont chaque champ fait référence à une table externe.

Si A et B forment la PK, vous avez déjà un index sur (A,B).
Un index supplémentaire sur B est envisageable pour valider simplement la Fk.

Il peut être extrêmement contre-performant de trop multiplier les index : ils ont un coût de création, un coût de stockage, un coût dans les update / delete, un coût lors des rafraîchissement statistiques.

Rappelez-vous que vous n'utiliserez qu'un seul index de type B*Tree pour accéder à une table.
L'index (A,B) est utile pour Oracle afin de valider la contrainte de clef primaire, il n'est pas nécessairement utile à vos requêtes.

3°/J'ai un index unique sur {A, B, C, D, E}
La majeure partie du temps je recherche par {A, B, C, D} sauf dans certains cas où je recherche sur {A, C, D} + critère sur un champ de la table jointe par B
--> dois-je remplacer l'index unique par {A, C, D, B, E} (inverser l'ordre)?

À priori oui, intervertir vos colonnes pourrait rendre votre index utile à plus de requête, mais comme disait mnitu ça dépend de vos données.
Si votre triplet (A,C,D) est très peu sélectif, Oracle pourra tout-à-fait décider ne de pas utiliser l'index.

Vous pouvez vous en assurer via les plans d'exécution.

[stof]

Merci.

Le point 2 n'est pas encore très clair pour moi :
-

Un index supplémentaire sur B est envisageable pour valider simplement la Fk.

-index sur {C, D} est une bonne idée ou pas (j'imagine que c'est dur de donner une réponse définitive car ça dépend si je lis plus ou si j'écris plus dans la table, mais admettons que je la lise beaucoup, ces 2 critères qui complètent ceux des PK doivent-ils être ajoutés?

Dernière question :
Si fonctionnellement il y a toutes les raisons d'ajouter un index unique sur plusieurs colonnes, doit-on toujours le rajouter ou est-ce qu'il faut aussi se poser la question?