Question sur des index

Version imprimable

03/02/2012, 13h11
StringBuilder

Question sur des index

Bonjour à tous,

J'ai une table : EVE dont voici une partie de la structure :
CODSOC
ACHVTE
TYPEVE
NUMEVE
CODETA
DATMOD
DATLIV
DATEXP
REFEXT
etc.

Il n'y a pas de "contrainte" (primary key) définie dessus.

En revanche, j'ai notamment ces quatre index :

EVE_IDX1 (UNIQUE) (CODSOC, ACHVTE, TYPEVE, NUMEVE)
W_EVE_IDX3 (NON UNIQUE) (CODSOC, ACHVTE, TYPEVE, NUMEVE, CODETA, DATLIV)
W_EVE_IDX4 (NON UNIQUE) (CODSOC, ACHVTE, DATMOD, TYPEVE, NUMEVE)
W_EVE_IDX5 (NON UNIQUE) (CODSOC, ACHVTE, TYPEVE, NUMEVE, CODETA, DATEXP)

Et là, je ne comprends pas.

Le premier est un index fourni par l'éditeur du modèle des données.

Les trois autres ont été rajoutés par un DBA en interne, pour améliorer les performances de requêtes.

Pour W_EVE_IDX4, je comprends que dans le cas où j'ai un filtre seulement sur CODSOC, ACHVTE, DATMOD, il puisse être utile. Je ne comprends en revanche pas pourquoi NUMEVE fait partie de l'index.

Mais pour W_EVE_IDX3 et W_EVE_IDX5, quelle est leur utilité ? Ils contiennent en intégralité l'index unique W_EVE_IDX1. Donc si fait un filtre sur CODSOC, ACHVTE, TYPEVE, NUMEVE, on est déjà au niveau ligne. A quoi ça sert d'ajouter d'autres champs ?

Étonnamment, ces index sont bel et bien utilisés par Oracle (10gR1).

Quelqu'un peu m'expliquer ?
03/02/2012, 13h52
mnitu

En général avoir des index redondants est une erreur. Mais d’après ce que vous dites ces indexes ont été ajoutés par des DBA probablement en désespoir de cause.
Le fait d’ajouter de colonnes dans un index permet à la requête de trouver la réponse dans l’index directement ce qui évite un accès à la table et par voie de conséquence réduisant ainsi le nombre de lectures logiques.
03/02/2012, 14h31
pinocchio

Je suis d'accord avec mnitu sur le "en désespoir de cause".
Par contre en regant ce message, si j'ai bien comprise l'analyse, on peut en conclure que dans ces index, il y a de nombreux doublons inutiles.
A priori, les index peuvent devenir

EVE_IDX1 (UNIQUE) (CODSOC, ACHVTE, TYPEVE, NUMEVE)
W_EVE_IDX3 (NON UNIQUE) (CODETA, DATLIV)
W_EVE_IDX4 (NON UNIQUE) (DATMOD, TYPEVE, NUMEVE)
W_EVE_IDX5 (NON UNIQUE) (CODETA, DATEXP)

Et à priori, j'aurai même tendance à dire que CODETA de W_EVE_IDX5 est en trop car en doublon avec W_EVE_IDX3

Cordialement
03/02/2012, 16h58
mnitu

Citation:

Envoyé par pinocchio

...
A priori, les index peuvent devenir

EVE_IDX1 (UNIQUE) (CODSOC, ACHVTE, TYPEVE, NUMEVE)
W_EVE_IDX3 (NON UNIQUE) (CODETA, DATLIV)
W_EVE_IDX4 (NON UNIQUE) (DATMOD, TYPEVE, NUMEVE)
W_EVE_IDX5 (NON UNIQUE) (CODETA, DATEXP)

Et à priori, j'aurai même tendance à dire que CODETA de W_EVE_IDX5 est en trop car en doublon avec W_EVE_IDX3
...

Sans les requêtes et autres informations on pourrait rien dire en fait.
03/02/2012, 18h02
StringBuilder

Citation:

Envoyé par mnitu

Sans les requêtes et autres informations on pourrait rien dire en fait.

Je confirme ;)

Les index proposés par Pinocchio sont inutilisables dans notre modèle, puisque la table EVE est systématiquement lue avec au moins les trois champs :

CODSOC, ACHVTE, TYPEVE, qui permette de savoir respectivement dans quelle société on se trouve, s'il s'agit d'un flux d'achat ou de vente, et du type d'événement (commande, facture, etc.)

Il sont donc systématiquement présents dans toutes les requêtes (ou alors leurs pendants CODSOC_O, ACHVTO, TYPEVO et CODSOC_S, ACHVTS, TYPEVS qui sont respectivement les liens vers l'événement d'origine et source d'un événement.

En revanche, ce qui m'étonne, c'est que pour l'index W_EVE_IDX5, lorsqu'il est utilisé, NUMEVE n'est jamais renseigné (aucun intérêt de rechercher un ensemble d'événements par leur date d'expédition si on connait déjà leurs numéros...)

Mais bon, de toute façon on migre la base dans moins de deux mois, et j'espère que l'éditeur va remettre son nez là dedans.

La question était afin d'essayer de comprendre ce que ça apportait. Visiblement, vous avez une réaction proche de la mienne. Après, je suis comme vous (mise à part que je maîtrise le modèle des données), je ne connais pas l'historique qui a poussé untel ou untel à créer tel index.
03/02/2012, 18h59
pacmann

Salut !

Les index 3 et 5 ont été ajoutés comme le dit Mnitu pour "couvrir" les données sur simple accès index, en évitant l'accès table.

Et ça peut être tout à fait justifié car sur les tables de ce genre il y a souvent plein de colonnes, et les lignes sont donc très lourdes...

On est souvent contraint à de très gros range scans, car la table est monstrueuse, et l'appli est designée pour traiter par CODSOC.

Par contre, je pense qu'un seul index aurait pu remplacer les 3 et 5 en y mettant les deux dates (ce qui n'alourdit pas trop violemment les indexes)... mais le choix de ne pas les ajouter dans l'index fourni de base (le 1) se comprend.

Après, je suppose que c'est typiquement le genre d'index qu'on doit gagner à compresser, et le genre de table qu'on doit gagner à partitionner sur CODSOC...
06/02/2012, 09h26
pinocchio

Citation:

Envoyé par StringBuilder

Les index proposés par Pinocchio sont inutilisables dans notre modèle, puisque la table EVE est systématiquement lue avec au moins les trois champs :

Ok, mais dans ce cas-là, lors de la requête, il ne peut pas être utilisé la combinaison de EVE_IDX1 et du second qui conviendrait correctement?
06/02/2012, 12h27
pachot

Bonjour,

Citation:

Envoyé par StringBuilder

Étonnamment, ces index sont bel et bien utilisés par Oracle (10gR1).

A mon avis, c'est par là qu'il faut commencer.
Voir quelles requêtes les utilisent
Voir si sans l'index en question la requête a des performances similaires.

Mais je suis assez d'accord avec ton analyse:

Citation:

Pour W_EVE_IDX4, je comprends que dans le cas où j'ai un filtre seulement sur CODSOC, ACHVTE, DATMOD, il puisse être utile. Je ne comprends en revanche pas pourquoi NUMEVE fait partie de l'index.

Si le filtre sur CODSOC, ACHVTE, DATMOD ramène beaucoup d'entrées, dont les enregistrements sont dispersés dans toute la table, alors il se peut que ce soit intéressant d'avoir NUMEVE sans devoir aller voir la table.

Citation:

Mais pour W_EVE_IDX3 et W_EVE_IDX5, quelle est leur utilité ? Ils contiennent en intégralité l'index unique W_EVE_IDX1

Oui, L'accès à la table ne devrait pas être très coûteux vu que c'est un index unique.

Et si la personne qui a rajouté ces index avait vraiment réfléchi, ils les aurait créé uniques, non ?

Parfois, des index sont conseillés pour tuner une requête, par des outils de tuning automatiques... Et le fait de les numéroter IDX1 IDX2 ... me donne parfois l'impression qu'on ne sait pas trop pourquoi on rajoute un index. Et une fois que c'est fait, on n'ose plus en enlever parce qu'il y a toujours un risque.

Cordialement,
Franck.