Clé Primaire en Char[6] ou Integer ?

[ilellouc]

Bonjour,

Je ne sais pas exactement si c'est au bon endroit que je poste ça, mais il faut bien se lancer.

Les tables de ma base de donnée ont des cléfs primaires qui sont des Char[6] ou Char[8] alors que ceux-ci sont des valeurs numériques.
J'ai lu dans le tuto de SQLPro qu'il était préférable d'avoir des clefs primaires en Integer.

Ma question : Avez vous une idée de l'ordre de grandeur du gain en temps de réponse de la base si l'on passe de Char[] à Integer ?

(Je suis débutant en SQL/Oracle/BDD ^^ )

merci
Ivan

[Luc Orient]

Pour moi, le choix d'une clé primaire est une problématique de modélisation. Si vous avez fait le choix d'une clé primaire numérique (un numéro auto-incrémenté par exemple), alors oui vous pouvez choisir de la décrire en INTEGER.

Dans le cas contraire, une description en CHAR est tout à fait acceptable.

[CinePhil]

Si tu as lu SQLPro, tu as dû lire aussi ses arguments en faveur d'une clé de type entier.
Un entier, c'est 4 octets, un CHAR(6), c'est 6 octets. Une clé primaire de type entier prend moins de place et la recherche dans les index est plus rapide.

Chez-moi, inutile de se torturer l'esprit trop longtemps : une clé primaire est de type entier et auto-incrémentée. Sauf éventuellement si c'est une table de référence ne comprenant que quelques lignes et avec une clé plus courte que 4 octets mais à condition que le contenu de cette clé ne puisse jamais être modifié. Un code susceptible de changer est une mauvaise clé primaire car la clé primaire doit être invariable pour éviter les erreurs de mise à jour des clés étrangères qui en découlent.

[Luc Orient]

Envoyé par CinePhil

Si tu as lu SQLPro, tu as dû lire aussi ses arguments en faveur d'une clé de type entier.
Un entier, c'est 4 octets, un CHAR(6), c'est 6 octets. Une clé primaire de type entier prend moins de place et la recherche dans les index est plus rapide ...

Eternel débat ...

Je ne suis pas tellement convaincu par ses arguments. Le CHAR(6) prend plus de place certes, mais quid d'un CHAR(4) ? Quand à la rapidité, il donne des exemples sur SQL-Server, donc sur Windows / Intel, moi je suis sur DB2 z/OS ( le mainframe ) et là ses explications ne sont plus valables.

Enfin, argument ultime, si une clé "naturelle" est présente dans la table et qu'on fait quand même le choix de ne pas l'utiliser comme clé primaire, alors il faudra quand même créer un index sur cette colonne, car elle a de fortes chances d'être un critère de recherche. Au final, on aura deux index au lieu d'un et le gain en performances (à l'insertion bien sûr) et en place devient totalement illusoire.

[Jester]

Il est noté que ce sont des valeurs numériques donc un char(4), ça va pas loin (9999 valeurs).

Cela dit, ça ne change pas forcément grand chose.

[MaitrePylos]

Une clé primaire doit-être unique, si dans sa modélisation il a repéré un champ unique de type char, alors pourquoi ajouter une colonne en int?

[SQLpro]

Envoyé par Luc Orient

Je ne suis pas tellement convaincu par ses arguments. Le CHAR(6) prend plus de place certes, mais quid d'un CHAR(4) ? Quand à la rapidité, il donne des exemples sur SQL-Server, donc sur Windows / Intel, moi je suis sur DB2 z/OS ( le mainframe ) et là ses explications ne sont plus valables.

Étant donné que les SGBDR fonctionnent tous de la même manière à de subtiles différences près (MySQL excepté) et que l'OS n'a aucune importance dans le cas d'un SGBDR puisqu'il possède le sien propre, la comparaison est valable et reproductible sous Oracle, DB2, PostGreSQL ou Sybase.

A +

[CinePhil]

Hier, je rends visite à mes anciens collègues de l'INRA et celui qui m'a succédé dans l'étude de la base de données sur les bovins me demande mon avis sur le fait qu'une recherche d'un bovin sur son NUMNAT, de type VARCHAR(12) parmi 65 millions de lignes soit instantané et que la même recherche sur une autre base de contrôle laitier avec le même type de colonne prenne 7 secondes alors que la table ne compte "que" 5 millions de lignes.

Table des bovins : clé primaire anonyme de type entier, NUMNAT indexé.
Table du contrôle laitier : clé primaire triple sur deux colonnes VARCHAR (dont le NUMNAT) et une colonne en SMALLINT. Pas d'index séparé sur le NUMNAT.

Je lui ai recommandé d'arranger ça !

[Luc Orient]

Envoyé par CinePhil

... mon avis sur le fait qu'une recherche d'un bovin sur son NUMNAT, de type VARCHAR(12) ...

Table des bovins : clé primaire anonyme de type entier, NUMNAT indexé.

Je ne comprends pas ... Cette clé primaire est VARCHAR(12) ou INTEGER ?

Par ailleurs, des clé primaires avec du VARCHAR, je trouve ça plutôt étrange ...

[CinePhil]

La table des bovins a une clé anonyme de type entier + un identifiant officiel appelé NUMNAT de type VARCHAR(12) et indexé séparément de la clé primaire.

La table du contrôle laitier a une clé primaire triple composée de deux colonnes en VARCHAR, dont le NUMNAT + une colonne en SMALLINT. La table du contrôle laitier n'est pas optimisée du fait de la clé primaire avec des colonnes en VARCHAR et du fait que le NUMNAT n'a pas d'index séparé et n'est pas la première colonne de la clé primaire.

Enfin bref... Ce n'est plus mon problème.

[Luc Orient]

Envoyé par CinePhil

... et du fait que le NUMNAT n'a pas d'index séparé et n'est pas la première colonne de la clé primaire.

Pour moi, le problème se situe bien là ... ça n'a aucun rapport avec le fait que l'autre table possède ou pas une clé primaire en INTEGER ...

[zolive]

si on a une table de 8 millions d'enregistrements.
un enregistrement contient entre autre un champ char64, si on sait par le métier que ce champ (chaine) ne peut avoir qu'une centaine de valeur différentes.
Table A
idA (long Pk) , chaine (char64) , chaine 2 (char256)

On a intérêt à créer une autre table qui recense cette centaine de valeur non ?
Table de reference
idChaine(char64 Pk)

Cette table a même intérêt à avoir une clef primaire différente de la chaine et dire a la base que la idChaine est unique :
Table de reference modifiée:
idRef (int Pk), idChaine(char 64 unique)

Au final la table A se trouve modifié ainsi :
idA (long Pk) , idRef (int Fk) , chaine 2 (char256)

Je me trompe si je dis que la Table A gagne 58 octets par enregistrement ?
au final comme on a 8 Millions d'enregistrement le gain est substantiel non ?

La table de référence ne contenant qu'une centaine de valeur elle risque fort d'être monté entièrement en mémoire et ne pas grever les requêtes.


Olivier
Ps je ne suis pas du tout expert en BDD donc si je dis une grosse betise ne m'en voulez pas

[CinePhil]

On a intérêt à créer une autre table qui recense cette centaine de valeur non ?

Oh que oui !

Table de reference modifiée:
idRef (int Pk), idChaine(char 64 unique)

Oui !

Au final la table A se trouve modifié ainsi :
idA (long Pk) , idRef (int Fk) , chaine 2 (char256)

Oui !

Un petit détail tout de même :

si on a une table de 8 millions d'enregistrements.

Un id de type INTEGER, codé sur 4 octets, est suffisant puisque la plage de valeur va de -2 147 483 648 à 2 147 483 647. Et si l'entier est non signé, vous avez de la place pour plus de 4 milliards d'identifiants !

Table A
idA (long Pk) , chaine (char64) , chaine 2 (char256)
...
Au final la table A se trouve modifié ainsi :
idA (long Pk) , idRef (int Fk) , chaine 2 (char256)
...
Je me trompe si je dis que la Table A gagne 58 octets par enregistrement ?

On passe d'un CHAR(64), soit 64 octets, à un INT, soit 4 octets. 64 - 4 = 60 octets gagnés si je ne m'abuse.
Et avec un id de type INTEGER, on gagne encore 4 octets puisque je pense que LONG doit être sodé sur 8 octets non ?

au final comme on a 8 Millions d'enregistrement le gain est substantiel non ?

8 millions de lignes (et pas enregistrements) * 64 octets potentiels = 512 millions d'octets.
La table ferait alors (4 + 4 + 256) x 8 000 000 = 2 112 000 000 octets, en gros 2Go au lieu de 2,5 Go auparavant.
Ce n'est pas négligeable. D'autant plus que l'index sera également plus petit.

La table de référence ne contenant qu'une centaine de valeur elle risque fort d'être monté entièrement en mémoire et ne pas grever les requêtes.

Il y a des chances oui. Surtout si elle est souvent utilisée, elle y restera.

Ps je ne suis pas du tout expert en BDD donc si je dis une grosse betise ne m'en voulez pas

Ca me semble pas mal pour un "non expert" ! Ce que je ne prétends pas être encore d'ailleurs !