Performance Int Vs VARCHAR Grosse table [Résolu]

[w3blogfr]

Bonjour à tous,

j'ai une table "mesure" d'environ 20 million de lignes.

Et sur chaque mesure, j'ai un status qui peut être PASS, FAIL, ABORT ou rien.

Actuellement, le status est stocké dans un VARCHAR de 5 avec un index B-tree. Mais je pense pouvoir améliorer les performances.

J'ai ajouté un colonne à côté de type Number avec respectivement Pass=1, Fail=2, Abort=3, rien=0. J'ai vu que l'index avec null n'était pas très performant

J'ai fait quelques tests, et sur la plupart de mes requêtes avec un EXPLAIN, j'obtient un meilleur coût avec le numérique, ce qui semble à priori logique.

Y'avait-il un moyen de faire autrement avec le VARCHAR? J'avais pensé utilisé un Bitmap, mais la table a beaucoup d'insertion.

[Z3phur]

Bonjour,

oui avec le varchar2 tu pouvais tout simplement mettre "NULL" dans ta colonne à la place de rien.

[Waldar]

Quelle est la distribution de vos données ?
Parce que vingt millions de lignes pour quatre valeurs, il y a surtout de grandes chances que l'index soit inutile.

[w3blogfr]

La table contient des mesures de fréquence, et la colonne status indique si la fréquence calculé correspond à la norme lors du test.

Voici un exemple de distribution des mesures par status (j'ai pris la base de test)

STATUS COUNT(*)
------------------------
ABORT 10 599
FAIL 241 741
PASS 11 212 779
VIDE 306


Pourquoi sur Internet, je trouve que que NUMBER est similaire à VARCHAR d'un point de vue performance http://asktom.oracle.com/pls/apex/f?...98700346402356

Mon EXPLAIN m'indique pourtant bien un coût inférieur compris en 20 et 30%?

En fait, c'est une application développée il y a plus de 2 ans, la base a gonflé. Je cherche du coup à optimiser certaines requêtes ou à améliorer mon modèle de donnée.


Dans le même genre d'idée pour les clé primaire, j'ai une table de numéro de Série (Chaine sur 20 caractère).
Par habitude avec MySQL, j'ai créé une clé primaire NUMBER avec une séquence auto incrémente, et je fais mes jointures sur l'id au lieu de numéro de série.

Comment décider si l'utilisation d'un id qui ne veut rien dire fonctionnellement est mieux qu'un VARCHAR (Numéro de Série de mon produit) en clé primaire? Si j'utilisais mon Numéro de Série en clé unique, je pourrais par moment éviter les jointure. Pour info dans ce cas, l'index peut être rarement utilisé car j'utilise le like %search%.

[mnitu]

Le coût de l’Explain Plan n’est qu’une estimation plus ou moins fiable ; il dépend forcement des autres informations disponibles (statistiques à jours, etc.). Dans certaines cases la requête peut s’exécuter en réalité en prenant un tout autre chemin.
Pour optimiser vous vous prenez mal. Si vous avez un problème de performance identifiez d’abord le traitement concerné. Faite une analyse de ce qui se passe, collectez donc les informations nécessaire à la compréhension du problème. Vous avez besoin de savoir où intervenir et qu’est-ce que vous pouvez gagner à chaque solution.
Sinon les changements de type : remplacer chaîne de caractères par numérique, etc. sont une pure perte du temps.

[w3blogfr]

Voici ma démarche qui ma amener à me poser cette question

J'avais quelques requêtes assez longues, j'ai donc fait un explain sur ces différentes requêtes et j'ai constaté entre autre que la plupart des requêtes devaient faire un full scan pour récupérer les lignes correspondant au critère status PASS.

Cette colonne de type VARCHAR2(5) ne contenait pas d'index, ce qui était la première erreur. Les performances ont été significativement amélioré dès que j'ai indexé la colonne.

Mais, pour aller plus loin, et prévoir le fait qu'elle va grossir encore. Je me demande si compte tenu du fait que la colonne ne prend que 4 valeurs (RIEN, PASS, FAIL ou ABORT), un numérique (0,1,2 ou 3) ne serait pas plus indiqué.



Pour le cas du numéro de série, c'est plus une conception d'ordre générale, je n'ai pas de problème de performance pour le moment. Toutefois, je constate que je fais régulièrement des jointures sur la table Numéro de Série en utilisant son id technique (clé primaire) du numéro de série. Alors que si j'utilisai le numéro de série (fonctionnel) en tant que tel comme clé primaire, je n'aurais pas à faire cette jointure.

[Waldar]

Donc effectivement, la valeur PASS n'a pas grand intérêt à être indexée.
Vos requêtes portent essentiellement sur les autre valeurs ?

Un index bitmap fera l'affaire, ce n'est pas le nombre de lignes de la table qui compte, c'est le nombre de valeurs distinctes de la colonne. Ici elle est faible, l'index sera bon. Néanmoins les bitmap ont aussi leurs limitations.

Ce que dis le lien c'est que les nombres Oracle sont stockés comme les chaînes de caractères.

Néanmoins, stocker '0', '1', '2', '3' prend moins de place que 'ABORT', 'FAIL', 'PASS' ou null.

Pour votre autre question c'est un autre débat, clef naturelle ou clef de substitution :
http://asktom.oracle.com/pls/apex/f?...40000346704229

[Waldar]

Envoyé par ttropardy

j'ai constaté entre autre que la plupart des requêtes devaient faire un full scan pour récupérer les lignes correspondant au critère status PASS.

Cette colonne de type VARCHAR2(5) ne contenait pas d'index, ce qui était la première erreur. Les performances ont été significativement amélioré dès que j'ai indexé la colonne.

Non sur les critères PASS, Oracle n'utilisera pas votre index et continuera les full scan.

[w3blogfr]

J'ai vu qu'un index bitmap était très pertinent pour un nombre de valeur distinct très faible, ce qui correspond bien à mon besoin avec mes 4 statuts.

Mais qu'il avait l'inconvénient de devoir être reconstruit à chaque insertion. Dans mon cas, j'ai des insertions régulière.

Après avoir mis l'index (type B-Tree), le EXPLAIN m'a indiquer qu'il utilisait mon index que je venais de créer. Et la requête en elle-même a perdu quelques secondes à l’exécution.

Pourquoi ne serait-il pas utiliser sur une requête avec le critère WHERE status='PASS'

[Waldar]

Parce pour la baleur PASS, Oracle doit lire 11212779 fois l'index puis lire 11212779 fois la table, qio est plus lent que de lire 11465425 fois la table.

Au contraire pour la valeur ABORT, lire 10599 fois l'index puis 10599 fois la table sera beaucoup plus rapide que de lire 11465425 fois la table.

Si vos statistiques sont à jour, l'optimiseur Oracle en tiendra compte.

En environnement OLTP, effectivement vous pouvez oublier les bitmap.

[w3blogfr]

Donc, pour résumer

Je suis mieux pour le moment de laisser mon statut en varchar2(5)

Vu que les tests m'ont montré que la requête avec l'index semble quand même plus rapide, je pense que je vais garder l'index.

Si un bitmap est compatible avec un grand nombre d'insertion, ça me parait effectivement le plus adapté à mon cas

Merci beaucoup.

[skuatamad]

Si une recherche sur statut is null est fréquente, il faut "indéxer les null", pour éviter un full scan.
Donc créer un index sur (status,0) par exemple ou même peut être créer un Function Based Index du type :

Code :

CREATE INDEX idx_statut_null ON t (case when statut IS NULL then 1 end);

Mais ça implique que modifier la clause where des requêtes en :

Code :

WHERE (case when statut IS NULL then 1 end) = 1

L'avantage de l'index sur case est d'avoir un index tout petit (300 lignes au lieu de 20M) donc encore plus performant pour la recherche sur les nulls.

La manip la plus simple est de créer l'index sur (statut,0), pas de modification de code et livraison en prod avec un minimum d'indispo (comparé à la création d'une nouvelle colonne)

[mnitu]

Comme Waldar vous l'a indiqué l'index sur la colonne en question ne présent d'intérêt que pour certains requêtes. Donc ce sont vos requêtes qui indique si l'index est pertinent ou pas.

Si vous avez ciblé le traitement qui doit être optimisé vous devez analyser ce traitement dans son ensemble et non pas requête par requête.
Traitement optimisé != Somme des requêtes optimisées
c'est à dire que vous pouvez avoir N requêtes optimales dans un traitement sous-optimal.

A propos vos requêtes utilise-t-elles des variables de liaison ou pas? Si c'est le cas vous pouvez encore avoir des mauvaise surprises avec votre index. Avez-vous faite une trace sql du traitement ou pas ?

[pachot]

Bonjour

J'ai fait quelques tests, et sur la plupart de mes requêtes avec un EXPLAIN, j'obtient un meilleur coût avec le numérique, ce qui semble à priori logique.

Il n'y a pas de raison pour que le traitement d'un number soit plus rapide que celui d'un varchar.
Mais évidemment, 'PASS' fait 4 octets (voire plus suivant le characterset) alors que 1 ne prends que 2 octets. Ce serait 'P' ce serait différent.

En voyant les requêtes avec leur plan d'exécution, ce serait probablement facile à expliquer.

Cordialement,
Franck.

[Ahmed AANGOUR]

Comme l'a fait remarqué Franck, sans plans d'exécution et j'ajouterai même sans statistiques d'exécutions il est difficile de donner un avis.

Par rapport à ce qu'a dit Waldar j'ajouterai qu'avoir des stats à jour ne suffit pas: vu que la colonne est skewed il faudrait calculer des histogrammes pour cette colonne afin que le CBO puisse estimer la bonne cardinalité.

cdmt,
Ahmed

[w3blogfr]

Sur les 3 requêtes qui utilisent le statut, je constate une amélioration des performances avec un index bitmap, la base n'est plus susceptible d'évoluer en terme d'espace, donc cette solution me convient.

Juste pour savoir, quel est la requête pour remettre à jour les statistiques?

[Z3phur]

Bonjour,

http://www.oracle-base.com/articles/...Statistics.php