Deux questions sur les index [Résolu]

[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 :

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 :

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 :

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 :

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 :

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]

Envoyé par mnitu

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)

Envoyé par mnitu

(...)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).

Envoyé par Mohamed.Houri

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]

Envoyé par stof

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.

Envoyé par stof

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.

Envoyé par stof

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?

[skuatamad]

Envoyé par stof

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

Regarde Unindexed Foreign Keys

Envoyé par stof

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?

Si c'est fonctionnel, c'est donc nécessaire (évidemment si cette contrainte d'unicité n'est pas déjà une PK et donc que l'on utilise une PK auto incrémentée via une séquence)

[stof]

Désolé, encore 2 questions :

Quand j'ai demandé s'il faut toujours ajouter un index unique quand fonctionnellement cela correspond à quelque chose.
Est-ce que ça ne rentrerait pas en contradiction avec ce que j'ai lu qui dit

Prix des index : l’écriture est 3 fois plus lente (donc 4 index sur une table, 12 fois plus lent)

Et est-ce que l'ordre des champs est important pour l'index unique {A, B, C, D} si on ne filtre parfois que sur un seul de ces 4 champs?

[Waldar]

Attention vous associez deux concepts qui sont pourtant distincts.

Si la règle fonctionnelle vous impose d'avoir un ensemble de colonnes uniques, ce n'est pas un index qui vous faut mais une contrainte d'unicité.
Ce que fait Oracle derrière pour valider cette dernière, dans une certaine mesure, n'est pas important, même si effectivement il s'appuie sur un index.

[stof]

Euh...un index unique n'est pas un index? Les règles d'ordre des champs n'a plus de rapport avec le fonctionnement d'un index? Un index unique sur {A, B, C, D} alors que des fois on filtre sur B & D ne serait-il pas mieux écrit en {B, D, A, C}?

Si index unique et index n'ont aucun rapport, est-ce qu'il faut comprendre qu'il peut être utile de créer des index EN PLUS de l'index unique?

***********

Sinon en lisant encore d'autres docs, je tombe sur la fusion d'index.
En-dessous de 5 index, il ne semble finalement n'y avoir aucun intérêt à créer un index sur {A, B, C, D, E} puisque si on en crée 5 distincts et qu'on a des clauses sur A, B, C ou A, D, E, ça semble théoriquement être plus performant par fusion des index distincts nécessaires que par un index multiple à l'ordre figé.?

[Mohamed.Houri]

Envoyé par stof

Un index unique sur {A, B, C, D} alors que des fois on filtre sur B & D ne serait-il pas mieux écrit en {B, D, A, C}?

Oui c'est bien ça.

Je récapitule pour vous les principaux points soulevés par les différents intervenants :

1. Ce sont les requêtes qui dictent la composition de vos indexes.
2. Pensez à créer des indexes en mettant, en premier, les colonnes qui se retrouvent le plus dans vos requêtes.

Par exemple, pour couvrir

Code :

1
2
3
4
5
6
7
8
9
10
 
WHERE     a  = :1
AND        b = :2
AND        c = :3;
 
et aussi
 
WHERE
        a = :1
AND   c = :3;

il serait plus intéressant dans ce cas de créer un index sur (a,c,b) et ce, quelle que soit sa nature unique ou pas unique.

Sinon en lisant encore d'autres docs, je tombe sur la fusion d'index.
En-dessous de 5 index, il ne semble finalement n'y avoir aucun intérêt à créer un index sur {A, B, C, D, E} puisque si on en crée 5 distincts et qu'on a des clauses sur A, B, C ou A, D, E, ça semble théoriquement être plus performant par fusion des index distincts nécessaires que par un index multiple à l'ordre figé.?

Avez-vous pris la peine de valider ce que vous avez lu ? La documentation que vous avez lue parle des indexes du type B-tree ou bitmap ? En tout cas, ca n’est pas parce que c’est publié quelque part que c’est vrai. Pensez à créer un modèle qui sert à valider les réponses que l’on vous donne ou celle que vous lisez dans un livre ou sur internet. Dans votre analyse vous oubliez l’accès au tables ; en effet, lorsque toutes les colonnes qui figurent dans la clause where du select n’existent pas dans l’index, il y aura un accès à l’index pour avoir le rowid qui sera transmis à la table qui subira à son tour une opération filtre. Lorsque vous avez avec un index précis {A, B, C, D, E} Il n’y aura pas d’opération filtre sur l’accès à la table. Le nombre de lignes sélectionnées par l’index sera exactement le même que le nombre de lignes sélectionnées par la table. Et si, par-dessus le marché, vous ne sélectionnez que {A, B, C, D, E} alors il n’y aura aucun accès à la table. Ceci met tout au moins un bémol à votre interprétation des indexes distincts au lieu d’un seul index précis.

Je vous conseille vivement (comme l'a déjà fait marius) de lire le site suivant:

http://richardfoote.wordpress.com/

Vous allez trouver toutes les réponses à toutes vos questions.

[Waldar]

Envoyé par stof

Euh...un index unique n'est pas un index ?

Je ne pense pas avoir écrit cela.
Je vous disais juste de ne pas confondre "valider une règle fonctionnelle" qu'on réalise avec une contrainte d'intégrité, et "améliorer les performances d'une ou d'un ensemble de requêtes" qu'on réalise en posant des index.

[stof]

Envoyé par Mohamed.Houri

Avez-vous pris la peine de valider ce que vous avez lu ?

je n'ai pas encore le recul (les connaissances) pour avoir un oeil critique sur ce que je lis.
Pour info (page 13) :
http://www.cocktail.org/gedfs/courri...df?wgtg=_blank

Envoyé par Mohamed.Houri

Je vous conseille vivement (comme l'a déjà fait marius) de lire le site suivant:
http://richardfoote.wordpress.com/

Je dois t'avouer que ce site est filtré du boulot (!!!) donc je n'y ai pas encore été (je viens d'y aller sur mon tel, a priori je ne vois que des sessions de formation probablement payantes, enfin bref)

Envoyé par Waldar

Je vous disais juste de ne pas confondre "valider une règle fonctionnelle" qu'on réalise avec une contrainte d'intégrité, et "améliorer les performances d'une ou d'un ensemble de requêtes" qu'on réalise en posant des index.

Oui, mais moi ce que je voulais dire, c'est que si mon index unique est posé pour des raisons purements fonctionnelles et qu'il ralenti les intégrations en base (puisqu'un index ralenti de 3 à 4 fois par index l'inserttion/mise à jour dans la table), il faut peut-être se poser la question si on choisit contraintes fonctionnelles ou performances.

[Waldar]

Envoyé par stof

Oui, mais moi ce que je voulais dire, c'est que si mon index unique est posé pour des raisons purements fonctionnelles et qu'il ralenti les intégrations en base (puisqu'un index ralenti de 3 à 4 fois par index l'inserttion/mise à jour dans la table), il faut peut-être se poser la question si on choisit contraintes fonctionnelles ou performances.

Si c'est une base de données transactionnelle (OLTP), il ne faut jamais transiger sur les contraintes. Si vous supprimez vos contraintes, c'est la porte ouverte aux données erronées. Si vos données sont fausses, vous allez faire du mal à votre société.

De plus dans les bases OLTP, les données arrivent petit à petit, des millisecondes multipliées par trois ou quatre restent des millisecondes.

Si votre base de données est décisionnelle (OLAP), vous pouvez être plus laxiste dans vos contraintes, puisque vos données sont déjà validées par des bases OLTP positionnées en amont, et qu'en cas de problème il y a toujours un moyen de recharger les données.
Ici les chargements sont ensemblistes et il est très courant, sur les transactions à fortes volumétrie, de désactiver les contraintes, rendre les index inutilisables, charger les données, réactiver les contraintes et reconstruire les index.

[stof]

Je suis dans le cas OLAP donc (je découvre les acronymes), avec des gros fichiers à intégrer d'une part et des données restituées d'autre part (d'où mes recherches pour optimiser la lecture sans ralentir les intégrations).