Discussion :

[samirCA007]

hello ,

j'ai une question sur vos recommandation pour l'indexation des colonnes a faible cardinalité

j'ai une table avec un colonne de type int qui reçoit seulement 3 valeur connue soit 0,1,2

Est-ce que l'indexation de cette colonne est pertinente?

merci pour vos retour d'expérience

[tatayo]

Bonjour,
En fait le nombre de valeurs possibles n'est qu'un des éléments à prendre en compte.
Quelle est la "distribution" de ces valeurs ? Est-ce qu'il y a (à peu près) autant de lignes pour chaque valeur, ou est-ce que l'une d'entre elles est plus présente que les deux autres ?
Est-ce que des requêtes sont filtrées sur cette colonne ?
Est-ce qu'il y a beaucoup de lignes dans cette table ?

Si par exemple la table contient beaucoup de lignes, que la valeur 3 est très peu présente, et qu'elle est la plus recherchée, alors un index à tout son sens.
Autre exemple, toujours avec beaucoup de lignes, une valeur 3 toujours très "recherchée", mais qu'elle est dans la grande majorité des lignes, un index ne servira pas à grand chose.

Cela étant dit, j'ai pour habitude d'indexer systématiquement toutes les clés étrangères, pour "faciliter" les contrôles des contraintes d'intégrité. Mais peut-être que ce n'est pas forcément une bonne idée.

Tatayo.

[escartefigue]

Bonjour

Je ne suis pas d'accord avec ce qui précède : s'il n'y a que 3 valeurs pour une colonne, l'index n'a aucun intérêt sur le plan des recherches quelle que soit la distribution des valeurs. En général, si un index correspond à plus de 10% de l'effectif, il n'est pas suffisamment discriminant pour être éligible comme chemin d'accès, l'optimiseur considèrera qu'il est plus rentable de parcourir séquentiellement les data plutôt que de faire des allers retours entre data et index.
Mais, il peut avoir un intérêt en tant qu'index couvrant, ce qui est tout autre chose.

Indexer systématiquement les clefs étrangères n'est pas une bonne pratique, il ne faut créer les index que là où ils sont nécessaires.
Exemple : une table des personnes pourra avoir pour FK l'identifiant du pays de naissance et l'identifiant du pays de domiciliation fiscale. Pour autant, on ne fait pas forcément des recherches sur ces critères, et si l'on en fait, encore faut il s'assurer que ces valeurs sont discriminantes. Là encore, si seulement quelques valeurs sont utilisées, créer des index plombera les perfs, l'espace disque et les servitudes (réorgs, build, sauvegardes, restaurations...).

[SQLpro]

Bonjour

Je ne suis pas d'accord avec ce qui précède : s'il n'y a que 3 valeurs pour une colonne, l'index n'a aucun intérêt sur le plan des recherches quelle que soit la distribution des valeurs. En général, si un index correspond à plus de 10% de l'effectif, il n'est pas suffisamment discriminant pour être éligible comme chemin d'accès, l'optimiseur considèrera qu'il est plus rentable de parcourir séquentiellement les data plutôt que de faire des allers retours entre data et index.

Pas d'accord... Tout dépend de la distribution des valeurs et d'une éventuelle clause WHERE.
Un index étant plus petit qu'une table, un scan d'index sera plus rapide que la lecture de la table et occupera moins de place en cache quelque soit les valeurs cherchées.
Si la distribution est très erratique, cela vaut le coup de créer des index filtrés sur les valeurs faiblement présentes. Un petit exemple est un index sur le groupe sanguin. Les groupes O+ et A+ étant représenté à 10% et plus, les ignorés au profit de tous les autres...

A +

[escartefigue]

Le fonctionnement habituel est que les valeurs extrêmes des statistiques (la plus petite et la plus grande) sont éliminées par l'optimiseur car considérées comme atypiques. L'optimiseur se concentre sur le "gras" de la courbe de Gauss
À moins que ce principe soit inhibé quand le nombre total de valeurs est très faible, dans ce cas d'espèce avec 3 valeurs, il n'en reste plus qu'une, donc tout index est sans le moindre intérêt.

[Waldar]

Indexer les valeurs trop présentes n'a en effet aucun intérêt, d'où la proposition de SQLPro d'utiliser un index filtré pour n'indexer que ces valeurs, ce qui est tout à fait valide :

1. garantie de n'utiliser l'index que pour ces valeurs faiblement représentées
2. index très léger

Je ne suis pas sûr pour SQL-Server, mais avec d'autres bases de données les valeurs atypiques sont au contraire parfaitement identifiées par l'optimiseur dans les histogrammes.

[SQLpro]

Le fonctionnement habituel est que les valeurs extrêmes des statistiques (la plus petite et la plus grande) sont éliminées par l'optimiseur car considérées comme atypiques.....

NON... Aucun valeur n'est éliminés des statistiques... Toutes les valeurs sont considérées. Ce qui permet justement de faire un plan en utilisant ou pas l'index en fonction de la cardinalité estimé !

En ce qui concerne les valeurs les moins fréquentes, ce sont justement les plus utilisées par les optimiseurs...

A +