DB2 z/OS Index et performances

Version imprimable

29/04/2009, 18h28
keskidi

DB2 z/OS Index et performances

Bonjour,

petites questions sur les index et leurs fonctionnements.

J'ai une table avec une cinquantaine de colonnes, et 3 colonnes en primary key.
La répartition par clé est clé1 : 2 valeurs distinctes, clé2 : 300000 valeurs distinctes, clé3 : 60000 valeurs distinctes et en tout 1500000 rangs.
J'organise mon index clé2 asc , clé3 asc , clé1 asc.
A priori pour un accès clé1 = a and clé2 = b and clé3 = c, c'est le meilleur index possible (d'après les recommandations firstkeycard/fullkeycard doit être le plus grand possible).

- Sachant que je ne peux avoir que des accès avec clé1 = a and clé2 = b puis clé3 = c ou clé3 >= c ou clé3 <= c, ne faudrait il pas plutôt faire un index clé2, clé1, clé3 ?

- Sachant que mon applicatif affiche toujours à mes occurrences suivant des curseurs order by clé1, clé2, clé3 optimize for 1 row, avec un where sur les 3 clés ; avoir un index clé2, clé3, clé1 est il finalement plus pénalisant que dans l'ordre clé1, clé2, clé3 pour obtenir la prochaine clé ?

- Faut il créer un index descending pour la pagination arrière ? Je vois partout ajouter REVERSE SCAN pour l'index, mais à priori, ça n'existe pas sur DB2 z/OS 9 .
29/04/2009, 20h30
Luc Orient

Citation:

Envoyé par keskidi

...
- Sachant que je ne peux avoir que des accès avec clé1 = a and clé2 = b puis clé3 = c ou clé3 >= c ou clé3 <= c, ne faudrait il pas plutôt faire un index clé2, clé1, clé3 ?

- Sachant que mon applicatif affiche toujours à mes occurrences suivant des curseurs order by clé1, clé2, clé3 optimize for 1 row, avec un where sur les 3 clés ; avoir un index clé2, clé3, clé1 est il finalement plus pénalisant que dans l'ordre clé1, clé2, clé3 pour obtenir la prochaine clé ?

Pour moi et en supposant qu'il s'agit d'une interrogation TP ( CICS, IMS/TM autre ? ) c'est l'ORDER BY qui doit déterminer l'ordre des colonnes dans l'INDEX et d'ailleurs en cohérence avec 'OPTIMIZE'. Il s'agit bien ici d'éviter la matérialisation de la table résultante surtout si une partie seulement de ce résultat est ramenée sur le poste de l'utilisateur. Est-ce le cas d'ailleurs ?
29/04/2009, 21h00
keskidi

Citation:

Envoyé par Luc Orient

Pour moi et en supposant qu'il s'agit d'une interrogation TP ( CICS, IMS/TM autre ? ) c'est l'ORDER BY qui doit déterminer l'ordre des colonnes dans l'INDEX et d'ailleurs en cohérence avec 'OPTIMIZE'. Il s'agit bien ici d'éviter la matérialisation de la table résultante surtout si une partie seulement de ce résultat est ramenée sur le poste de l'utilisateur. Est-ce le cas d'ailleurs ?

Oui, tout à fait, c'est pour du CICS.

Et donc ? Il est préférable d'utiliser un index clé1, clé2, clé3 ? Même si c'est une catastrophe au niveau performance pour un accès direct à un rang connu ?
29/04/2009, 22h51
Luc Orient

Citation:

Envoyé par keskidi

... Et donc ? Il est préférable d'utiliser un index clé1, clé2, clé3 ? Même si c'est une catastrophe au niveau performance pour un accès direct à un rang connu ?

Même si clé1 est peu discriminant, je vois mal DB2 choisir un autre chemin d'accès que l'index sur une requête avec un prédicat clé1 = ... AND clé2 = AND clé3 = ... et là l'accès est très rapide.

De toutes façons, c'est comme tout, l'idéal pour trancher c'est de faire différentes simulations ( EXPLAIN voire traces ) sur une table avec une volumétrie proche de celle de production et là on verra bien ...

Enfin, dernier point, définir cet index comme CLUSTER améliorera encore les choses pour l'accès paginé en TP puisque les lignes consécutives à retourner ont de grandes chances d'être dans la même page sur une table pas trop désorganisée bien sûr ...
30/04/2009, 02h03
keskidi

Citation:

Envoyé par Luc Orient

Même si clé1 est peu discriminant, je vois mal DB2 choisir un autre chemin d'accès que l'index sur une requête avec un prédicat clé1 = ... AND clé2 = AND clé3 = ... et là l'accès est très rapide.

De toutes façons, c'est comme tout, l'idéal pour trancher c'est de faire différentes simulations ( EXPLAIN voire traces ) sur une table avec une volumétrie proche de celle de production et là on verra bien ...

Enfin, dernier point, définir cet index comme CLUSTER améliorera encore les choses pour l'accès paginé en TP puisque les lignes consécutives à retourner ont de grandes chances d'être dans la même page sur une table pas trop désorganisée bien sûr ...

Alors, j'ai du mal à comprendre cette discussion dans laquelle un certain Luc Orient spécifie que mettre une cle1 peu discriminante est très mauvais.

Je comprend bien pour les tests, mais si quelqu'un avait déjà fait plusieurs tests du même genre, ça serait quand même plus simple, je trouve plein de documentation dans lesquelles il est également spécifié qu'avoir un index avec une FIRSTKEYCARD à 2 et une FULLKEYCARD à 1500000, c'est une connerie au niveau performance, et qu'il vaut mieux dans le cas présent avoir 300000 en FIRSTKEYCARD, d'autant que je le répéte dans 100% des cas les select seront sur clé1 = a and clé2 = b (et clé3 avec accès variables =, >, >=, <=, <), c'est pourquoi je pensais qu'un index clé2, clé1, clé3 était optimal, malgré le order by clé1, clé2, clé3.

D'autre part, en ce qui concerne l'index descending, faut il le créer ? ou est-ce implicite dans DB2 for z/OS 9 ?
01/05/2009, 19h45
Luc Orient

Citation:

Envoyé par keskidi

Alors, j'ai du mal à comprendre cette discussion dans laquelle un certain Luc Orient spécifie que mettre une cle1 peu discriminante est très mauvais.

Justement, relisez précisément ce que j'ai indiqué. Le problème de la colonne peu discriminante en première position est principalement un problème de détermination du chemin d'accès. En effet, en standard , DB2 n'a des informations sur le nombre de valeurs distinctes des colonnes d'un index multi-colonnes que pour la première colonne (FIRSTKEYCARDF) et pour la totalité des colonnes (FULLKEYCARDF). Il lui manque des informations pour les autres combinaisons possibles. Si dans votre cas, la recherche avec le prédicat d'égalité porte sur la totalité des colonnes de la clé c'est bien l'information portée par FULLKEYCARDF qui devrait être prise en compte.

Et pour compliquer tout cela, sachez que depuis la V5, il est possible d'obtenir des informations complémentaires sur ces combinaisons intermédiaires avec l'option facultative KEYCARD de l'utilitaire RUNSTATS de collecte des informations statistiques. Mais là, ça dépend de la politique choisie et appliquée sur votre site en cette matière.

Encore une fois, l'inconvénient d'avoir une première colonne non discriminante pour un index multi-colonnes, c'est surtout de risquer de disqualifier cet index lorsque l'optimiseur de DB2 cherchera à déterminer le chemin d'accès d'une requête donnée.

Dans votre cas, et en fonction des éléments que vous avez indiqués ce risque m'apparaît faible voire nul, et surtout, à mettre en balance avec le gain espéré sur l'interrogation paginée en TP.

Enfin et pour terminer, la réflexion théorique sur la meilleure façon de déterminer un index doit toujours s'accompagner d'une simulation (là les modalités peuvent varier) destinée à confimer ou infirmer les hypothèses de départ et on a parfois de drôles de surprises en ce domaine. Je vous encourage donc vivement à vous mettre en rapport avec un DBA sur votre site, et si ce dernier n'est pas totalement débordé, il devrait pouvoir vous aider à réaliser ces simulations.
02/05/2009, 22h15
keskidi

Ok, merci, on va donc tester de l'autre sens pour voir.
16/05/2009, 14h42
pdz74

Avec quelques semaines de retard (longue absence...), je me permets d'ajouter une réponse d'autant plus que j'avais participé à la discussion citée en référence.

DB2 ne connait pas que le fullkeycardf et le firstkeycardf. Si les runstats ont la bonne option, DB2 connait également les valeurs intermédiaires comme il est précise dans les différents échanges. Mais surtout, ne pas oublier qu'il existe une colonne COLCARDF dans la SYSCOLUMNS, que cette colonne donne toutes les infos nécessaires à DB2 et que l'optimiseur en tient compte.

En conclusion, si tous les accès sont réalisés avec un prédicat d'égalité sur C1 et C2 et un prédicat aléatoire sur C3 et si les tris se font toujours dans l'ordre C1, C2, C3, alors le bon index est automatiquement C1, C2, C3. Il permet d'accéder à la table en matchcols 3, dans par l'arborescence complète de l'index et il permet de résoudre le tri donc de ne renvoyer que les lignes nécessaires sans être obligé de lire toutes les autres avec un fetch first n row only.

Seul souci de cet index : pour une requête avec un simple prédicat sur C1, si DB2 décide de passer par l'index, ce sera très moyen, puisque cela renverra la moitié des lignes de la table. Autant faire un scan. Mais, si on est certain d'avoir toujours C1 et C2 renseigné, alors pas de souci, DB2 choisira toujours cet index et ce sera le bon.

Bonne utilisation.