Choix de colonnes pour Indexation?

Version imprimable

05/02/2009, 19h35
JauB

Choix de colonnes pour Indexation?

Salut tout le monde,
étant devant une situation de lenteur d'exécution des requêtes SQL, j'ai pensé à indexé mes tables (la plupart sont très très volumineuses)!
Ma question est : dois-je créer des index sur les colonnes que j'ai dans la clause WHERE ou sur les mes champs ID avec les quels je fais mes jointures, sachant que mes colonnes ID ne figurent que rarement dans des clauses WHERE.
Merci d'avance :mrgreen:
05/02/2009, 22h23
Luc Orient

Les deux mon général ...

Il faudrait aussi voir :

= quelles sont les requêtes les plus utilisées

= quelles sont les colonnes dans les clauses WHERE les plus discriminantes
07/02/2009, 09h49
K2R400
Un index parfait est un index qui prend les clauses WHERE puis celles des jointures.

ex :
Code:

1 2 3 4 Select * from employes e, services s where sexe='F' and date_nassaince beetween '01.01.1990' and '31.12.1990' and e.srv = s.srv
L'index parfait pour cette requête est :
- pour le fichier employes --> sexe, date_naissance, srv
- pour le fichier services --> srv
07/02/2009, 10h53
JauB

Citation:

Envoyé par K2R400

Un index parfait est un index qui prend les clauses WHERE puis celles des jointures.

ex :

Select * from employes e, services s
where sexe='F' and date_nassaince beetween '01.01.1990' and '31.12.1990'
where e.srv = s.srv

L'index parfait pour cette requête est :
- pour le fichier employes --> sexe, date_naissance, srv
- pour le fichier services --> srv

Merci beaucoup pour ces explications.
Est ce qu'il y a d'autres conseils :aie: ?
08/02/2009, 15h22
Luc Orient

Citation:

Envoyé par K2R400

...
L'index parfait pour cette requête est :
- pour le fichier employes --> sexe, date_naissance, srv

Attention, la colonne "sexe" n'est pas un très bon choix pour un index du fait de son caractère peu discriminant ( seulement deux valeurs 'F' ou 'H' ). En tout cas et pour DB2 z/OS par exemple, il faut absolument éviter de la placer en première colonne de l'index, ce SGBD étant très sensible à la sélectivité de la première colonne d'un index pour déterminer un chemin d'accès ( on parle de FIRSTKEYCARD ).
08/02/2009, 18h13
JauB

Citation:

Envoyé par Luc Orient

Attention, la colonne "sexe" n'est pas un très bon choix pour un index du fait de son caractère peu discriminant ( seulement deux valeurs 'F' ou 'H' ). En tout cas et pour DB2 z/OS par exemple, il faut absolument éviter de la placer en première colonne de l'index, ce SGBD étant très sensible à la sélectivité de la première colonne d'un index pour déterminer un chemin d'accès ( on parle de FIRSTKEYCARD ).

Qu'est ce que tu proposes comme index pour l'exemple donné par @K2R400?
sachant qu'on parle DB2/AS400 ;)
08/02/2009, 20h00
Luc Orient

Citation:

Envoyé par JauB

Qu'est ce que tu proposes comme index pour l'exemple donné par @K2R400?
sachant qu'on parle DB2/AS400 ;)

== >> pour la table "Employes" je choisis :
date de naissance
pour les autres colonnes, je suis dubitatif ... la colonne "sexe" parce que elle est peu discriminante et la colonne "service" parce qu'elle n'est pas valorisée dans la requête ...au pire et pour lever les doutes je fais des essais dans un environnement de qualification ( volumétrie identique à l'environnement de production )

par contre, il faudra sans doute un autre index sur la colonne "service" car elle est probablement une clé étrangère

== >> pour la table "Services" :
choix identique à celui de K2R400 ... de plus c'est sans doute la clé primaire

Je dois ajouter que je ne connais pas DB2 AS/400 mais DB2 z/OS ...
08/02/2009, 21h05
JauB

Citation:

Envoyé par Luc Orient

== >> pour la table "Employes" je choisis :
date de naissance
pour les autres colonnes, je suis dubitatif ... la colonne "sexe" parce que elle est peu discriminante et la colonne "service" parce qu'elle n'est pas valorisée dans la requête ...au pire et pour lever les doutes je fais des essais dans un environnement de qualification ( volumétrie identique à l'environnement de production )

par contre, il faudra sans doute un autre index sur la colonne "service" car elle est probablement une clé étrangère

== >> pour la table "Services" :
choix identique à celui de K2R400 ... de plus c'est sans doute la clé primaire

Je dois ajouter que je ne connais pas DB2 AS/400 mais DB2 z/OS ...

Oui je vois, je teste et vous tiens au courant :roll:
10/02/2009, 19h16
K2R400

C'est le principe qui compte, la facon de montrer ce qu'est un index parfait.
Clauses WHERE les plus sélectives possibles (égal avant between), puis la clé de jointure.

Et je confirme, sur DB2 400, sexe en premier reste un index parfait !
20/02/2009, 07h24
pdz74

Bonjour,

Je suis assez d'accord avec Luc pour dire qu'un index avec comme première colonne, une colonne avec seulement 2 valeurs discriminantes, n'apporte pas grand chose. En effet, une recherche sur le code sexe ramène 1 ligne sur 2 qui ont des chances d'être dispatchées sur toutes les pages de la table. Donc SELECT * FROM TABLE WHERE CDSEXE = :HV va lire la moitié de l'index qui va le ramener sur la totalité des pages de la table (1 ligne sur 2 mais sur toutes les pages). Dans ce cas, un bon vieux scan de la table avec du prefetch sera beaucoup plus rapide. Exception : SELECT COUNT(*) FROM TABLE WHERE CDSEXE = :HV. DB2 est alors en indexonly et se contente de lire le nombre de cas correspondant, l'index est alors intéressant. Mais, dans la vraie vie, on se contente rarement de faire des count(*).

Le choix des index, ce n'est jamais évident. Quelques règles :
- Un index sur chaque colonne d'une table pour prévoir tous les accès possibles, c'est une hérésie.
- Lorsqu'on accède à une table avec 3 prédicats, un index multi colonnes sur les 3 colonnes concernées est bien plus intéressant que 3 index sur chacune des colonnes.
- Gaffe aux index du style unique where not null. Par exemple, une table de 6 millions de lignes avec une colonne renseignée une fois sur 2. Cela signifie 3 millions de lignes avec la valeur NULL et 3 millions de lignes OK. Si on accède par cette colonne, un index est nécessaire et répondra au besoin sauf si on a le malheur d'écrire WHERE COL IS NULL. Dans ce cas, bof.... Mais le plus gros problème de ces index, c'est en cas de suppression de lignes. En effet, DB2 va invalider la ligne dans l'index et pour cela il dispose de la valeur de l'index, NULL pour le cas qui nous intéresse et du RID. DB2 va alors scanner tous les RIDs pointés par la valeur NULL (y'en a 3 millions) pour trouver le bon. Ca peut durer...
- Ne pas oublier tout l'intéret des accès en indexonly. Donc, quand on a une table avec peu de colonnes, ça vaut souvent le coup de mettre toutes les colonnes dans l'index. L'index est plus gros, mais on n'accède plus aux datas, ce qui est super pour les perfs.
- Gaffe aux index peu discriminants : on en revient au sujet du message. Un scan est souvent préférable au passage par un index peu discriminant.
- Ne pas oublier que trop d'index sur une table, cela génère des I/O lors de toute insertion et suppression (voire update si la colonne modifiée est dans l'index). A prendre en compte lors du choix des index, même s'il est vrai que la plupart des tables sont lues 1000 fois plus souvent qu'elles ne sont mises à jour. Donc, c'est les SELECT qu'il faut pérenniser.
- ...

Et pour compliquer le tout, ne pas oublier que toute règle a ses exceptions, donc ce qui est valable dans un cas ne l'est pas toujours dans un cas qui peut pourtant paraitre plus ou moins similaire !

:salut:
07/05/2009, 12h29
Ferene

Il existe Isérie Navigator
Isérie Navigator, propose l'option Visual Explain, qui permet d'exécuter une requète et de proposer les meilleurs index.

Il existe aussi les Index Vectoriels : (Très rapide)

Exemple de création d'index vectoriel
Surtout utile sur table importante avec de nombreuses occurences identiques.
Code:

1 2 3 4 CREATE ENCODED VECTOR INDEX nomBib.NomIndex ON nombibPF.nomPF ( Colonne ASC ) WITH 0 DISTINCT VALUES ;
Pour ma part, n'étant pas un expert du SQL je prefere coder en RPG FREE
Je vais très vite et le code oblige la reflexion sur les tps de réponse ou la performance.
Amicalement,:mouarf:
09/05/2009, 15h52
fsmrel

Bonjour,

Pour ma part, je ne connais que DB2 for z/OS.

Mais les mots magiques qui suivent valent quel que soit le SGBD, en les adaptant au besoin :

REORG

RUNSTATS

REBIND

EXPLAIN

En exécutant tout cela dans l'ordre on se remet dans les conditions initiales de température et de pression, et on peut savoir si, a priori, les applications se traîneront ou non, juger de l'efficacité des index présents (donc virer les parasites) et de la nécessité d'en créer d'autres (et/ou de remanier les requêtes).

Mais, ne pas oublier que des index trop nombreux pour une table donnée peuvent mettre en échec LE batch de mise à jour trimestriel hyper sensible (celui qui sert à préparer la pêche aux sous) et qu'il faudra sans doute débrancher temporairement ces index, pour ramener la durée de ce batch de 8 heures à 5 minutes, avant de se faire remonter les bretelles par la DSI qui se sera fait elle-même tancer parce que « Si les utilisateurs sont mécontents, c’est la faute à l’informatique, etc. », air connu...

Cette stratégie vaut déjà pour les traitements les plus sensibles.