[INDEX] - ordre des colonnes d'un index composite

[PpPool]

Bonjour,

RDBMS 9i, 10g, 11g

J'ai eut une discussion ce matin avec un consultant Oracle sur les index composite (b*tree uniquement) et l'ordre des colonnes, qui me laisse dubitatif.

jusqu'à présent, je déterminai l'ordre des colonnes par une estimation pour chaque colonne de la sélectivité
Ma régle simple : ordre des colonnes => de la plus sélective à la moins sélective

ce matin, nous avons recréé un index sur 3 colonnes avec plusieurs ordres différents

Résultat : l'index que j'avais créé n'est pas celui qui a le clustering factor le plus faible

Ma question : quelles régles utilisez vous à ce niveau ? Faut-il étudier chaque combinaison de colonne et déterminer le clustering factor ? -> pour certains index composites sur 15 colonnes que nous avons çà risque d'être chaud !!!

Merci de votre retour d'expériences à ce niveau

[Mohamed.Houri]

Envoyé par PpPool

Bonjour,

RDBMS 9i, 10g, 11g

J'ai eut une discussion ce matin avec un consultant Oracle sur les index composite (b*tree uniquement) et l'ordre des colonnes, qui me laisse dubitatif.

jusqu'à présent, je déterminai l'ordre des colonnes par une estimation pour chaque colonne de la sélectivité
Ma régle simple : ordre des colonnes => de la plus sélective à la moins sélective

ce matin, nous avons recréé un index sur 3 colonnes avec plusieurs ordres différents

Résultat : l'index que j'avais créé n'est pas celui qui a le clustering factor le plus faible

Ma question : quelles régles utilisez vous à ce niveau ? Faut-il étudier chaque combinaison de colonne et déterminer le clustering factor ? -> pour certains index composites sur 15 colonnes que nous avons çà risque d'être chaud !!!

Merci de votre retour d'expériences à ce niveau

Ce n'est pas le clustering factor qui décide de l'ordre des colonnes dans l'index. Ce sont vos requêtes qui le dictent. Par exemple, dans le cas où nous voudrions couvrir une requête du type

Code :

1
2
3
4
5
6
7
8
9
10
 
  SELECT *
    FROM table_t t
 WHERE
     t.a = :1
 AND
     t.b = :2
 AND
     t.c = :3
;

alors dans ce cas un index du type

Code :

1
2
 
 CREATE INDEX ind_t ON table_t (a, b, c);

couvrira bien notre requête

Mieux encore, les indexes suivants feront également l'affaire

Code :

1
2
3
4
 
 CREATE INDEX ind_t ON table_t (b, c,a);
 CREATE INDEX ind_t ON table_t (c, b,a);
 CREATE INDEX ind_t ON table_t (b, a,c);

Bref, n'importe quel index contenant les colonnes a,b, c dans n'importe quel ordre fera l'affaire.

Mais, il y a toujours un mais quelque part, lorsque nous avons ce genre de choix, il est judicieux de placer la colonne la plus répétée (la moins sélective) en premier. Savez vous pourquoi? et bien c'est pour pouvoir compresser efficacement l'index. La compression d'un index est une option très performante mais malheureusement moins utilisée

Voilà pourquoi, il est important de suivre le processus suivant lors de la création d'un index

(a) définir l'index de telle sorte que les colonnes de l'index soient dictées par la requête. Faire très attention à la première colonne de l'index elle doit absolument faire partie de la where clause et elle doit plutot courvir une égalité et non un range (> ou < par exemple)

(b) placer les colonnes les moins selectives en premiers (mais ceci doit se faire en tenant compte des remarques précédentes)

Une autre remarque, admettons qu'en plus de la requête précédente, nous avons une autre requête à couvir qui est définie comme suit

Code :

1
2
3
4
5
 
 SELECT *
    FROM table_t t
 WHERE
       t.c = :3

Quel index allons nous créer?

Il est donc clair que nous allons créer un seul index pour couvrir les deux requêtes

Code :

1
2
 
CREATE INDEX ind_t2 ON table_t (c, b, a);

ou bien

Code :

1
2
 
   CREATE INDEX ind_t2 ON table_t (c, a, b);

La colonne c doit absolument être en premier. Il nous reste le choix de placer a ou b en deuxième position; et pour cela, nous utiliserons la colonne la moins sélective en deuxième position

Bien à vous

Mohamed Houri

[Mohamed.Houri]

Bonjour,

En complément, je dirai aussi ceci

Le "clustering factor" est un nombre singulier qui représente le degré de distribution aléatoire des données dans une table. C'est un nombre qui indique la répartition des données dans une table par rapport à l'index

Ceci dit, comme une table ne peut être ordonnée que d'une seule manière, on pourrait donc veuiller à créér un index de telle sorte que la première colonne de cet index soit celle qui représente le meilleur ordre dans la table. Mais dans ce cas, il faudrait d'abord prendre en considération la présence de ladite colonne dans la requête. Et enfin, sachez que nous ne pourrions pas créér des indexes avec des colonnes différentes et ayant tous un bon clustering factor.

Bien à vous

Mohamed Houri