Arjuna Aircraft Ident.: F-MBSD | Au taff y'a un intervenant qui se la joue "I'm the great master of SQL" et malgré son très bon niveau, il a fini par me demander comment faire un truc, qui en effet est loin d'être évident.
Donc je vais pour l'expliquer ici, car c'est une optimisation courrant pour éviter les tables de jointures quand le nombre de lignes de jointures est fixe.
On s'en sert généralement pour apposer un flag (true/false) a une information dépendant d'une liste finie d'éléments.
Par exemple dans le cas d'une flag "afficher" qui dépendrait d'un pays. La liste des pays est une donnée qui évolue très peut, et peut donc être appliquée à ce système.
Un calendier des jours fériés aussi.
Le but du jeu, c'est de stocker dans une colonne unique toutes ces informations à l'aide d'un masque binaire. Ainsi, avec un INT(32) on peut stocker 32 flags, ce qui économie 32 lignes dans une table de jointure.
Je vais prendre l'exemple d'un calendrier.
Mettons une table calendrier.
NOMCAL varchar(30)
ANNEE smallint
JANVIER int
FEVRIER int
MARS int
AVRIL int
MAI int
JUIN int
JUILLET int
AOUT int
SEPTEMBRE int
OCTOBRE int
NOVEMBRE int
DECEMBRE int |
Chaque colonne portant le nom d'un mois contient une série de bits contenant au jour. Donc un nombre sur 32 bits (4 octets) suffit à stocker les informations pour un mois entier.
Chaque jour férié aura un 0 et les jours ouvrables aurons un 1
Par exemple, janvier de cette annee :
1000001100000110000011000001101
Notez qu'il faut lire de droite à gauche puisqu'il s'agit d'une représentation binaire.
Ce qui donne une fois converti en INT :
1099105805
Pour retrouver le status d'un jour dans le mois, il suffit de lire le bit à l'index numjour-1, ce qui se fait avec cette formule :
masque ou 2^(numjour-1)
Avec Oracle ce sera donc :
mod(trunc(lemois / power(2, to_char(ladate, 'DD') - 1), 0), 2) |
Maintenant, on n'a plus qu'à trouver le bon mois dans la table en fonction de la date. On utilise simplement un décode :
mod(trunc(decode(to_char(sysdate, 'MM'), '01', janvier, '02', fevrier, '03', mars, '04', avril, '05', mai, '06', juin, '07', juillet, '08', aout, '09', septembre, '10', octobre, '11', novembre, '12', decembre) / power(2, to_char(ladate, 'DD') - 1), 0), 2) |
Enfin, on fait notre select :
select mod(trunc(decode(to_char(ladate, 'MM'), '01', janvier, '02', fevrier, '03', mars, '04', avril, '05', mai, '06', juin, '07', juillet, '08', aout, '09', septembre, '10', octobre, '11', novembre, '12', decembre) / power(2, to_char(ladate, 'DD') - 1), 0), 2)
from calendrier
where nomcal = 'FERIE'
and annee = to_char(ladate, 'YYYY') |
Vous n'avez plus qu'à remplacer "ladate" par votre date.
Cette requête retournera 1 pour un jour férié et 0 pour un jour ouvré.
Pour remplir la table, il suffit d'utiliser cette même technique.
Niveau performances, ça n'en a pas l'air, mais c'est extrêment efficace.
En effet, dans cette exmeple, déjà, on joue avec des nombres 32 bits, donc traîtés en un seul cycle avec n'importe quel système.
Ensuite, on travaille avec des puissances de 2. Tout programme correctement écrit va savoir faire des décallages plutôt que des puissances / divisions effectives.
Et surtout, en temps normal, pour stocker une année complète, on aurais certainement fait :
-> Soit 365 lignes avec identifiant + donnees (généralement, au moins 5 octets)
-> Soit x lignes avec identifiant + donnees (si on ne stocke que les jours remarquables) (généralement, au moins 5 octets)
Dans le premier cas, on traîte rapidement un nombre important de données pour rien.
Dans le second, on traîte un peu moins de données, mais avec une jointure externe si on ne veux pas perdre les infos des jours non remarquables.
Avec le système que j'ai indiqué, on n'a qu'une seule ligne de 48 octets d'info, donc beaucoup moins, et les traîtements, tout aussi rapides.
En revanche, ce système a une grosse limitation : il est extrêment difficile de travailler avec des intervalles de dates, et a ce moment les performances pourraient devenir très faibles.
Ceci-dit, si on traîte les infos en dehors de la base (via programme) on obtiendra toujours de très bonnes perfs, a condition de traîter une seule ligne à la fois.
Comme j'ai dit, ce système est donc très limité de ce point de vue. Pour le reste, il est réputé meilleur, et compatible avec mon nombre de classes existantes traîtant déjà les informations de cette façon en mémoire (ce système offre un gain fantastique d'occupation mémoire et de cycles processeurs pour la relecture, et est donc courrament utilisé dans nombre de programmes) |
