Discussion :

[fsmrel]

Bonsoir,

J'ai évacué les informations complémentaires dans les entités annexes. Ainsi, tous les Null ont disparu.

Well done ! Voilà une application bien partie. Vous tiendrez-nous au courant ?

Quel problème concret sur quel SGBD ?

Sont concernés tous les SGBD qui sont dotés d’un optimiseur capables de transformer les expressions pour une meilleure efficacité. Par exemple, la transformation suivante est souhaitable :

(A WHERE p1) WHERE p2

=>

A WHERE p1 AND p2

C’est le composant RDS (Relational Data System) auquel incombe la tâche de rendre plus efficaces les requêtes. Le 1er RDS pour SQL fut au cœur de SYSTEM R, le prototype de SGBDR d’IBM, présenté en 1976 [1] et du reste consciencieusement pompé plus tard par Lawrence Ellison pour ORACLE (voyez les références [2] et [3]). Je vous invite à lire le chapitre 18 (Optimization) de l’ouvrage de référence des bases de données relationnelles [4], dans lequel C.J. Date met l’accent sur le rôle des lois telles que la distributivité (pour transformer par exemple une jointure suivie par une restriction en une restriction suivie par une jointure), la commutativité, l’associativité, l’idempotence, l’absorption, etc. Tous les SGBD dotés d’un optimiseur digne de ce nom sont concernés. Le problème avec NULL, c’est que les lois peuvent être mises en échec :

Une équivalence telle que r JOIN r ≡ r qui est au cœur de ces lois ne vaut plus ; A = B et B= C n’implique plus que A = C, etc. Revoyez le rôle joué en l’espèce par la logique trivaluée.

Dans ces conditions, un optimiseur ne doit plus utiliser les lois en aveugle mais se débrouiller...


Je cite Jeff Ullman cf. [5] page 173) :

« Another area in which problems remain is the extension of the relational model to handle "Null values," that is, entries in tuples that represent unknown, irrelevant, or inconsistent values. Attempts too deal with nulls have been made by Codd [1975], Lacroix and Pirotte [1976], Zaniolo [1977], Lipsky [1981], Vassilou [1979, 1980] and Maier [1980]. However, problems arise when we try to define algebraic operations. For example, when taking an equijoin, should two nulls be regarded as equal? No answer is wholly satisfactory. »

Dans ce sens, je note que lorsque deux variables relationnelles R1 et R2 (tables en SQL) sont du même type (leurs attributs sont deux à deux du même type, donc « UNION compatibles »), la jointure naturelle R1 JOIN R2 dégénère en R1 INTERSECT R2 (cf. [4], page 186). Je ne sais pas comment se passent les choses avec votre SGBD, mais, par exemple avec SQL Server 2005, selon que l’on utilise la jointure ou bien l’intersection, horresco referens ! les résultats sont différents dès que le bonhomme Null se manifeste...

Exemple (simple) :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE R1 (A  INT) ;
CREATE TABLE R2 (A  INT) ;
 
INSERT INTO R1 (A) VALUES (NULL) ;
INSERT INTO R2 (A) VALUES (NULL) ;
 
SELECT A FROM R1
INTERSECT 
SELECT A FROM R2 ;
 
SELECT R1.A FROM R1 JOIN R2 ON R1.A = R2.A ;

Dans un cas, le résultat contient une ligne, aucune ligne dans l’autre cas. Qu’est-ce qu’on fait ? (@seabs, ceci ne vous concerne pas )


[1] Astrahan, M. M. et al. System R: Relational Approach to Database Management (IBM Research Laboratory, June 1976)
http://knight.cis.temple.edu/~vasili...s/system-R.pdf

[2] Serge Miranda. Comprendre et concevoir les bases de données relationnelles (éditests, 1988). Page 110, on lit ceci :

« ORACLE est un fils "illégitime" de System R ».

[3] The 1995 SQL Reunion: People, Projects, and Politics (Edited by Paul McJones, digital Systems Research Center)
http://www.mcjones.org/System_R/SQL_...95/sqlr95.html

[4] C. J. Date. An Introduction to Database Systems, 8th edition. (Pearson: Addison-Wesley (International Edition), 2004).

[5] J.D. Ullman. Principles of DATABASE SYSTEMS. Second Edition. (Computer Science Press. 1982).

[Richard_35]

Bonjour à tous,

Magistral !... comme d'habitude, Fsmrel. Si, un jour, tu organises une conférence, préviens la communauté, STP.

Mais, quand même, je tempérerais l'aventure du "bonhomme null", car tous les excès sont mauvais, dans un sens comme dans l'autre. OK, sur le fait qu'il faut éviter le "bonhomme null" si le champ concerné est susceptible d'être une clé étrangère.

En revanche, si celui-ci est un simple attribut, je ne vois pas l'intérêt de créer une table uniquement pour gérer le fait que celui-ci peut être null. L'exemple extrême est le cas des champs "Observations" : créer une table des observations pour prévoir le cas d'observations non saisie ne me paraît pas judicieux. En outre, si l'on veut obtenir la liste des observations non saisies, il faudra faire une liaison pour tester... la valeur "null"... Autant la tester dans la table elle-même, donc.

[pachot]

Bonjour,

Dans ces conditions, un optimiseur ne doit plus utiliser les lois en aveugle mais se débrouiller...

Oui, bien sûr. Mais quel optimiseur de SGBD applique des règles à l'aveugle ? Il y a des règle de transformations qui garantissent l'equivalence du résultat. Et un choix du plan d'accès à partir d'une estimation statistique des coûts.

a jointure naturelle R1 JOIN R2 dégénère en R1 INTERSECT R2

Si un optimiseur fait cette transformation, alors c'est un bug de l'optimiseur en question. Car les 2 ne sont pas equivalents.
Et pas seulement à cause des NULL. Intersect va tout dédoubloner. Pas la jointure.
Et d'ailleurs pourquoi chercherait-il à faire cette transformation ? Intersect va probablement être plus couteux.


Dans l'exemple que tu proposes avec CHEQUE -> OPERATION_COMPTABLE je vois les problèmes suivants:
- insérer une opération avec un numéro de chèque va insèrer 2 lignes et mettre à jour 3 indexes (2 PK + 1 contrainte d'unicité sur NoCheque)
- chercher l'opération correspondant à un numéro de chèque va demander: aller voir l'index sur CHEQUE.NoCheque, aller voir l'enregistrement correspondant de CHEQUE, aller voir l'index sur OPERATIION_COMPTABLE.OperId, aller voir l'enregistrement correspondant de OPERATION_COMPTABLE.
- rien que sortir un listing des numéros de chèques du dernier mois va être long: pour chaque OPERATION_COMPTABLE qui correspond, il faudra aller voir l'enregistrement correspondant dans CHEQUE. Pour chacun, c'est au moins 2 I/O (accès index CHEQUE.OperId + accès table CHEQUE). C'est couteux et ca n'est pas scalable du tout.
- et si on veut partitionner OPERATION_COMPTABLE par mois, pour gérer l'archivage et les purges. Comment fait-on avec CHEQUE ?
- comment empêcher de rentrer un chèque pour un crédit ? comment empêcher de se retrouver avec un chèque pour un débit en espèce ? A chaque mise à jour d'une table, il faudra aller voir dans l'autre et vérouiller l'enregistrement.
- et puisqu'on parle de l'optimiseur, comment dire à l'optimiseur qu'il n'y a des chèques que pour les débits ? On ne peut pas faire une contrainte CHECK entre 2 tables. C'est pourtant une information importante.


Bref, tout est 2 fois plus coûteux. Et 2 fois plus couteux, c'est doubler les temps de réponse.
On se retrouve aussi à stocker plusieurs fois les OperId, et les index dessus. Ca prends plus de place. Ca prends plus de temps. Ca va décupler le temps de n'importe quelle opération de maintenance là dessus.

On parle bien d'un modèle physique ? Il faut qu'il soit performant, évolutif, maintenable, etc.

Avec le numéro de chèque dans la table opération:
- toujours 1 seul enregistrement à accéder, à vérouiller
- seulement 2 index à mettre à jour en cas de modification
- possibilité de partitionner
- possibilité de rajouter des contraintes d'intégrité. Pour vérifier l'intégrité et pour que l'optimiseur sache que s'il y a un prédicat de type operation=credit alors il n'y a pas de numéro de chèque
Le numéro de chèque peut être NULL, et dans ce cas, il ne prends pas de place ni dans la table ni dans l'index (il n'y a pas d'entrée d'index pour les NULL)
Sinon, il est diretement dans l'opération sans devoir aller voir ailleurs.



Cela dit, je ne crois pas qu'on puisse construire un modèle physique de données correct seulement avec des règles mathématiques.

Pour modéliser un besoin métier, il faut partir des concepts métiers et de leur cycle de vie.

Est-ce que dans le système le CHEQUE a son propre cycle de vie ou bien est-il toujours lié à l'OPERATION_COMPTABLE ?

Si le numéro de chèque n'est qu'une information de plus pour les débits, qu'on le saisit en même temps que l'opération, et qu'on l'archive ou le purge avec l'opération, alors tout ira probablement dans la même table. il me semble que c'est le case ici, mais c'est à valider avec le métier.

Par contre, on peut s'imaginer qu'il soit rajouté après avoir saisi l'opération ( 2 transactions: saisie de l'opération et attribution numéro de chèque ).
Ou qu'il n'ait pas le même cycle d'archivage, du genre garder dans le système 10 and d'opération comptables, mais ne garder les numéros de chèque que pendant 2 ans
. Alors dans ces cas on pourrait être amené à avoir 2 tables.

Et ensuite, dans le cas des 2 tables, on se poserait la question de savoir si le chèque n'est qu'une information de l'opération, et on aura alors CHEQUE qui référence OPERATION ). Ou alors, peut-être qu'on on gère des chèques indépendament de l'opération: on les saisit tous dès qu'on reçoit le chéquier, et on les référence ensuite lorsqu'on fait une opération de paiement par chèque. Et là on aura alors l'intégrité référentielle dans l'autre sess: OPERATION qui référence CHEQUE ...et qui peut-etre null bien sûr suivant le type d'opération.

En UML, cette analyse métier va se retrouver dans les Use Cases (pour le cycle de vie) et dans les notions d'aggrégation/composition et de navigabilité du diagramme de classe de ce(s) Use Case(s).

Voilà les questions que je me pose pour modéliser correctement un besoin métier. Pas de formes normales, pas de tabou des NULL, pas de règles aveugles, pas de mythes qui viennent des technologies aujourd'hui obsolètes,...
On voit souvent des systèmes en production qui souffrent de trop de tables et trop d'index. Des process qui doivent aller chercher des infos éparpillées dans toute la base. Et du coup, les équipes d'exploitation pensent qu'il faut dénormaliser et introduit des redondances pour arriver à quelque chose de performant et scalable.

Je n'ai jamais vraiment compris d'où vient ce tabou sur les NULL. Sauf pour de Foreign Key comme le précise Richard. Lorsque la FK est composée de plusieurs colonnes dont certaines sont sont NULL, la contrainte ne sert pas à grand chose. Et pour les NOT IN où là aussi ça n'a pas trop de sens.

Cordialement,
Franck.

[fsmrel]

Bonsoir,

créer une table des observations pour prévoir le cas d'observations non saisie ne me paraît pas judicieux

Pour quelle raison précise ?

En outre, si l'on veut obtenir la liste des observations non saisies, il faudra faire une liaison pour tester... la valeur "null"...

Il n’y a pas de Null à tester. Supposons que la table OPERATION ait pour appendice une table OBSERVATION dotée d’un attribut ObservationTexte prenant des valeurs qui sont les observations relatives aux observations :

OPERATION {OperId, Compte, OperDate, Montant, Sens, ...}

OBSERVATION {OperId, ObservationTexte}

Si on a besoin de connaître les observations non saisies, on soumet la requête SQL (un classique) :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
SELECT OperId 
FROM   OPERATION AS x
WHERE  NOT EXISTS 
       (SELECT  1
        FROM    OBSERVATION AS y
        WHERE   x.OperId = y.OperId   
       )
;

Requête dont on peut du reste faire une vue.

Il y a des règles de transformations qui garantissent l'équivalence du résultat.

Oui, mais qui peuvent être mises en difficulté comme l’a écrit Jeff Ullman (cf. la citation que j’ai faite) ou encore Chris Date.

Et un choix du plan d'accès à partir d'une estimation statistique des coûts

Oui, mais l’estimation statistique des coûts est une chose, la validité des résultats en est une autre. A quoi bon pédaler à toute vitesse si c’est pour produire des non théorèmes ?

Si un optimiseur fait cette transformation, alors c'est un bug de l'optimiseur en question. Car les 2 ne sont pas équivalents.

L’optimiseur n’est pas en cause, si R1 et R2 sont UNION compatibles, que JOIN dégénère en INTERSECT relève des mathématiques, c’est la faute à la théorie des ensembles et ça, ni vous ni moi n’y pouvons rien...

Intersect va tout dédoubloner. Pas la jointure.

Si ça ne vous ennuie pas, parlons algèbre relationnelle et pas « algèbre des sacs » (si cela existe). La théorie relationnelle est basée sur la théorie des ensembles, et dans un ensemble il n’y pas de doublons. SQL (Sorry Query Language ou Askew Wall) n’est pas un langage relationnel puisqu’il permet d'engendrer des doublons sans état d’âme...

Dans l'exemple que tu proposes avec CHEQUE -> OPERATION_COMPTABLE je vois les problèmes suivants:
insérer une opération avec un numéro de chèque va insèrer 2 lignes et mettre à jour 3 indexes (2 PK + 1 contrainte d'unicité sur NoCheque)

Au lieu d’insérer une ligne et mettre à jour 2 index (Un pour OperId (PK) et un pour le numéro de chèque), d'accord. Cela se traduit par 2 accès supplémentaires au disque, c'est-à-dire un surplus de l’ordre de 10 millisecondes, ce qui n’est pas un problème en transactionnel, on a vu pire.

chercher l'opération correspondant à un numéro de chèque va demander: aller voir l'index sur CHEQUE.NoCheque, aller voir l'enregistrement correspondant de CHEQUE, aller voir l'index sur OPERATIION_COMPTABLE.OperId, aller voir l'enregistrement correspondant de OPERATION_COMPTABLE.

Soit, comme précédemment, 2 accès supplémentaires au disque, c'est-à-dire un surplus de l’ordre de 10 millisecondes (plus une milliseconde CPU), ce qui n’est toujours pas un problème.

rien que sortir un listing des numéros de chèques du dernier mois va être long: pour chaque OPERATION_COMPTABLE qui correspond, il faudra aller voir l'enregistrement correspondant dans CHEQUE. Pour chacun, c'est au moins 2 I/O (accès index CHEQUE.OperId + accès table CHEQUE). C'est couteux et ca n'est pas scalable du tout.

Quand on en arrive aux traitements de masse, il n’y a pas une I/O par ligne de table mais pour un ensemble de lignes, nuance... Je me positionne par rapport à DB2 for z/OS (V8) : dans le cas de la table CHEQUE (2 colonnes de type INT), la taille d’une ligne est de 16 octets (8 octets de données + 8 octets système) ce à quoi on ajoute quelques autres octets de service au sein de chaque page. Bref, DB2 annonce 230 lignes par page. Supposons que le nombre de lignes de la table OPERATION_COMPTABLE soit de l’ordre de 10000000 et que la table CHEQUE en contienne 5000000. L’index primaire de chaque table est cluster (au sens DB2 du terme) sur OperId, d’où une déperdition minimale quant aux I/O, quelques secondes au plus. Je réfute donc vos affirmations qui se basent sur l’équation : une ligne = une I/O.

et si on veut partitionner OPERATION_COMPTABLE par mois, pour gérer l'archivage et les purges. Comment fait-on avec CHEQUE ?

Vous attaquez le problème de l’« optimisation », lequel n’est pas forcément à appréhender de la même façon en fonction des SGBD. Les données n’étant pas a priori archivées et purgées tous les jours, tout est possible : Synchroniser les index de partitionnement de table OPERATION_COMPTABLE (qui est cluster dans le cas de DB2) et ceux de ses tables satellites, soit se contenter de créer une table de travail contenant les OperId (triés tant qu’à faire) à purger, et opérer par jointure avec les satellites. Bref un travail de routine pour les DBA de la Production.

comment empêcher de rentrer un chèque pour un crédit ? comment empêcher de se retrouver avec un chèque pour un débit en espèce ?
A chaque mise à jour d'une table, il faudra aller voir dans l'autre et vérouiller l'enregistrement.
- et puisqu'on parle de l'optimiseur, comment dire à l'optimiseur qu'il n'y a des chèques que pour les débits ? On ne peut pas faire une contrainte CHECK entre 2 tables. C'est pourtant une information importante.

Avec SQL, si l’on ne dispose pas de l’instruction CREATE ASSERTION, ça sera au moyen d’un trigger qui lors des insert/update devra assurer la cohérence.

Bref, tout est 2 fois plus coûteux.

Non, car vous vous basez sur l’équation trop simple une ligne = une I/O.

On parle bien d'un modèle physique ? Il faut qu'il soit performant, évolutif, maintenable, etc.

Pas particulièrement. On parle d’abord modèle conceptuel, puis logique puis physique. Mais puisque vous insistez sur ce dernier, pour en mesurer objectivement la performance, on bâtit un prototype dédié, avant que les développements ne commencent. En tout cas, c’est toujours comme cela que j’ai agi (DBA de 1972 à 2005 entre autres fonctions) et sur de gros projets pour lesquels j’ai engagé la responsabilité de mon entreprise (une SSII de quelques milliers d’ingénieurs), et jamais je n’ai eu droit à la honte des pénalités.

On se retrouve aussi à stocker plusieurs fois les OperId, et les index dessus. Ca prends plus de place. Ca prends plus de temps. Ca va décupler le temps de n'importe quelle opération de maintenance là dessus.

Question temps, revoyez votre équation.

Coût du stockage (avec free space de 10%) :
J’ai regardé (toujours dans le contexte DB2) pour une table OPERATION_COMPTABLE ayant la structure (réduite) suivante :

(OperId Integer, OperDate Date, Montant Integer, Sens Integer, ChequeNo Integer)

Pour 10000000 de lignes : 76940 pages de 4KB.
Index UNIQUE et CLUSTER sur OperId : 30300 pages
Index sur ChequeNo (non unique car ChequeNo n’est pas clé candidate) : 24920 pages

Soit en tout 132160 pages.

En mettant en œuvre la table CHEQUE :

Table OPERATION_COMPTABLE de structure (OperId Integer, OperDate Date, Montant Integer, Sens Integer) :
Pour 10000000 de lignes : 65800 pages de 4KB.
Index UNIQUE et CLUSTER sur OperId : 30300 pages

Table CHEQUE de structure (OperId Integer, ChequeNo Integer) :
Pour 5000000 de lignes : 21840 pages de 4KB.
Index UNIQUE et CLUSTER sur OperId : 15200 pages
Index sur ChequeNo (non unique) : 18000 pages

Soit en tout 151140 pages, c'est-à-dire, comparé à 132160 pages, un surplus proche de 15%.

Maintenant, si l’on considère une table ayant la même structure que la table CHEQUE et qui contiendrait 1000000 de lignes au lieu de 5000000, le coût serait le suivant :
Table : 4370 pages
Index sur OperId : 3040 pages
Index sur ChequeNo : 3600 pages

Soit en tout 102750 pages au lieu des 132160 pages et cette fois-ci, le coût de stockage est moindre, comme quoi votre affirmation :
« On se retrouve aussi à stocker plusieurs fois les OperId, et les index dessus. Ca prends plus de place » relève en l’occurrence du sophisme.

On verra la suite plus tard.

[Richard_35]

Bonjour Fsmrel,

Si on a besoin de connaître les observations non saisies, on soumet la requête SQL (un classique) :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
SELECT OperId 
FROM   OPERATION AS x
WHERE  NOT EXISTS 
       (SELECT  1
        FROM    OBSERVATION AS y
        WHERE   x.OperId = y.OperId   
       );

Requête dont on peut du reste faire une vue.

==> c'est vrai. Néanmoins, la création d'une requête :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
SELECT OperId 
FROM   OPERATION
WHERE  Observation is null ;

ne me semble pas gênante.

créer une table des observations pour prévoir le cas d'observations non saisies ne me paraît pas judicieux

Pour quelle raison précise ?

==> juste une question d'équilibre, toujours bien difficile à trouver : l'excès de la chasse aux NULL me semble néfaste, l'excès inverse, tout autant. Mais, c'est vrai, le point d'équilibre est plus étendu qu'un point, justement.

Cette discussion, ouverte par CinePhil, est une bonne illustration de ce débat (d'ailleurs, j'aimerais bien savoir où en est CinePhil, dans sa réflexion).

Donc :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
Aucune chasse <--------------------------------------------------> Chasse systématique
                                    |<---->| Equlibre

==> chacun se situant un peu comme il le sent...

[pachot]

Bonjour fsmrel,

Mon but était seulement d'attirer l'attention sur le fait qu'une approche trop théorique dans la modélisation peut amener des problèmes de performance et de scalabilité lors de son exploitation.

Je ne parlais que de SQL, pas algèbre relationnelle, parce que je pense que c'est en SQL que ce modèle va être interrogé.
Et à propos de la citation de Jeff Ullman «For example, when taking an equijoin, should two nulls be regarded as equal? No answer is wholly satisfactory. », que ce soit satisfaisant ou non, en SQL ANSI la norme est claire: 2 nulls ne sont pas égaux.

un surplus de l’ordre de 10 millisecondes, ce qui n’est pas un problème en transactionnel, on a vu pire.

Non. Il manque une composante. 10 millisecondes c'est le temps de service de ces I/O.
Mais si tous les utilisateurs font 2x plus d'I/O que nécessaire, on remplit les files d'attentes des disques.
Dans le temps de réponse, il faut compter le temps d'attente due à la contention>. Et là c'est exponentiel lorsque la charge augmente.
Sans compter les autres files d'attente: la contention sur le cache, les verrous, etc.

Je réfute donc vos affirmations qui se basent sur l’équation : une ligne = une I/O

Le calcul 'une ligne=un I/O' se base sur des accès à des données dispersés, car je parlais du 'listing des numéros de chèques du dernier mois'. Le clustering sur l'ID ne va pas beaucoup aider pour cela. Il faudrait un clustering sur type d'opération et date pour cet exemple.
Mais le meilleur clustering, c'est de stocker les données ensemble - dans le même enregistrement - lorsqu'elle sont toujours manipulées ensemble.

Avec SQL, si l’on ne dispose pas de l’instruction CREATE ASSERTION, ça sera au moyen d’un trigger qui lors des insert/update devra assurer la cohérence.

Mais ni l'un ni l'autre ne donne d'indication à l'optimiseur, contrairement aux contraintes. CREATE ASSERTION, on est encore dans la théorie. Je crois qu'aucun éditeur de SGBDR a réussi à l'implémenter de manière performante et scalable (sans verrous excessifs notamment). Malheureusement.

Cordialement,
Franck.

[pachot]

Bonjour Richard,

==> juste une question d'équilibre, toujours bien difficile à trouver : l'excès de la chasse aux NULL me semble néfaste, l'excès inverse, tout autant. Mais, c'est vrai, le point d'équilibre est plus étendu qu'un point, justement.

Tout à fait d'accord.

Et ça dépends aussi du contexte. Pas besoin de se poser trop de questions sur une appli mono-utilisateur qui gère 100000 lignes.

De mon point de vue, la discussion dans un forum n'a pas pour but de faire changer les gens d'avis. Mais je pense que c'est utile de voir les raisons qu'il y a derrière chaque point de vue. Et chacun peut alors faire son choix dans son contexte.

Cordialement,
Franck.

[Richard_35]

Bonjour Franck,

De mon point de vue, la discussion dans un forum n'a pas pour but de faire changer les gens d'avis. Mais je pense que c'est utile de voir les raisons qu'il y a derrière chaque point de vue. Et chacun peut alors faire son choix dans son contexte.

==> entièrement d'accord.

J'ajoute que, de ce point de vue, cette discussion est riche. Enfin, sauf pour notre amie Cadao, il me semble...

Fsmrel, concernant ta culture sur le sujet.

[CinePhil]

Juste en aparté...

Cette discussion, ouverte par CinePhil, est une bonne illustration de ce débat (d'ailleurs, j'aimerais bien savoir où en est CinePhil, dans sa réflexion).

Je n'ai pas avancé, je suis sur un autre projet depuis cet automne.

J'attends toujours l'avis de fsmrel sur la question mais il semble très occupé par d'autres très longues discussions. Rien d'urgent François mais si tu pouvais ne pas oublier ce sujet, ça m'intéresserait vivement.

[fsmrel]

Bonsoir,

je ne crois pas qu'on puisse construire un modèle physique de données correct seulement avec des règles mathématiques

Qui dit le contraire ? Tout le monde n’est pas Zaniolo. On commence par élaborer un modèle conceptuel de données (ou un diagramme de classes) à la façon de Tabourier, mais ceci fait on utilise des techniques telles que la normalisation en xNF pour s’assurer que les structures sont saines.

En UML, cette analyse métier va se retrouver dans les Use Cases (pour le cycle de vie) et dans les notions d'aggrégation/composition et de navigabilité du diagramme de classe de ce(s) Use Case(s).

La plus belle fille ne peut offrir que ce qu’elle a... C’est bien beau, mais que l’on s’appuie sur UML ou Merise ou Corig ou que sais-je (chaque technologie, chaque méthode a ses fans), si dans les dossiers de conception il manque la moitié des règles de gestion, si elles sont floues, ambiguës, rien n’empêchera le projet de finir à la poubelle, une fois les développements terminés mais ne répondant pas à ce que demandait l’utilisateur ; on repartira pour tout recommencer avec une nouvelle équipe et une technologie conceptuelle de plus en plus pointue. Tant qu’il manquera des règles de gestion ou que celles-ci prêteront à confusion, ça pourra durer éternellement. J’ai expertisé de tels (gros) projets, non aboutis au bout de 10 ans... j’ai joué les Colombo et le plus souvent fait se réunir en « conclave » les chefs de projets et les utilisateurs le temps qu’il fallait, pour que par la maïeutique on arrive enfin à un dossier valide et pérenne. Je ne peux malheureusement pas citer ici les noms de clients et de sous-traitants, mais croyez-moi ça n’est pas l’envie qui m’en manque.

Pas de formes normales

Dans les années soixante-dix, je ne connaissais pas, mais je normalisais d’instinct (M. Jourdain faisait bien de la prose...) car la redondance c’est de la dynamite et on n’est pas tous artificiers. La normalisation n’est certes pas la panacée, mais c’est un outil indispensable quand on veut modéliser proprement, éviter l’obésité des tables et les risques inhérents en mise à jour, etc. Qu’on ne vienne pas me raconter les histoires de multiplication des tables, séparées physiquement sur les disques, les I/O insupportables, thèmes rebattus depuis trente ans. Je redis ce que j’ai dit, on monte des prototypes de performance pour que l’on sache objectivement à quoi s’en tenir avant que les développements ne commencent (1000 utilisateurs qui secouent simultanément les commandes et provoquent des verrouillages finissant en étreinte fatale, etc.) Comment peut-on prétendre que normaliser est inutile (voire nuisible) ?!

Pas de tabou des NULL

Je rappelle à cette occasion qu’une relation (au sens du Modèle Relationnel de Données, la table SQL n’étant qu’un semblant de relation) ne contient que des valeurs, rien que des valeurs. Je cite et traduis C.J. Date (The Relational Database Dictionary chez Apress) :

Null : Un montage, utilisé en particulier dans SQL, pour représenter l’information absente. Note : Par définition les nulls ne sont pas des valeurs (on les appelle parfois marques) ; il s’ensuit qu’un « type » qui « contient un null » n’est pas un type, un « tuple » qui « contient un null » n’est pas un tuple, une relation qui « contient un null » n’est pas une relation, qu’une relvar qui « contient un null » n’est pas une « relvar » [...]

Pour ma part, j’adhère à la théorie relationnelle et ne peut donc faire la promotion du bonhomme Null, même si c’est parfois tentant. A chacun de choisir (et justifier) son paradigme. De mon côté, il n’y a pas là de tabou (qui est un interdit d’origine sociale ou religieuse), seulement une conclusion de l’étude des effets de la logique trivaluée.

Je rappelle en passant que le développeur A qui utilise l’opérateur INTERSECT tandis que le développeur B utilise l’opérateur JOIN pour traiter le même problème peuvent obtenir des résultats différents à cause de Null.

Pas de règles aveugles, pas de mythes qui viennent des technologies aujourd'hui obsolètes...

Un point sur lequel nous devrions être a priori d’accord, à ceci près que je ne vois pas à quelles règles et à quelles technologies vous faites allusion...

Et pour les NOT IN où là aussi ça n'a pas trop de sens.

A savoir ? (A noter que je n’utilise pas NOT IN mais NOT EXISTS, eu égard à Frege). Si à la logique des prédicats vous préférez l’algèbre, on peut utiliser EXCEPT...

On voit souvent des systèmes en production qui souffrent de trop de tables et trop d'index. Des process qui doivent aller chercher des infos éparpillées dans toute la base. Et du coup, les équipes d'exploitation pensent qu'il faut dénormaliser et introduit des redondances pour arriver à quelque chose de performant et scalable.

Tout ceci est la conséquence d’une modélisation approximative. Quand j’ai eu à valider chez un client de mon entreprise un système donnant lieu au final à près de 2000 tables (dont certaines plutôt copieuses), bien urbanisé en référentiels (Personnes, Contrats, Produits, Cotisations, Prospection, Habilitations, Événements, etc.), normalisé en 4NF, il n’y a eu aucun problème important lors du prototypage des performances, même si ça a coûté de la sueur et si j’y ai passé des nuits et des nuits. Et quand l’entreprise a fusionné son informatique avec celle de son concurrent, c’est elle qui a hébergé sans problème les données de ce dernier.

Cela dit, je suis d’accord, ça n’est pas aux équipes d’exploitation de dénormaliser, elles doivent se rapprocher des Études et c’est aux DBA (Exploitation & Études) de quantifier les éventuels bienfaits de la chose. Je vous renvoie à ce sujet à une anecdote que j’aurais pu intituler « C’est la faute à la 3NF ! » et où l'on voit qu'il y avait erreur de cible...

Mon but était seulement d'attirer l'attention sur le fait qu'une approche trop théorique dans la modélisation peut amener des problèmes de performance et de scalabilité lors de son exploitation.

Je ne sais pas ce que vous entendez par « trop » théorique. Quoi qu’il en soit, après avoir pratiqué tous les métiers en informatique, de la soute à dunette, après avoir pratiqué le fer à souder, et avoir aussi ferraillé dans tous les sens (avec les éditeurs, les auteurs, les théoriciens, etc.), je défends la thèse selon laquelle, sans un bagage théorique suffisant on peut faire n’importe quoi, en toute incompétence, un peu à la façon d’un développeur qui n’aurait jamais appris un minimum d’algorithmique. Si Ted Codd (un théoricien, un vrai !) n’avait pas inventé la théorie relationnelle, on en serait encore à utiliser des bases de données façon hiérarchique, réseau, liste inverse ou que sais-je, et cette discussion n’aurait pas lieu.

en SQL ANSI la norme est claire: 2 nulls ne sont pas égaux.

Forcément, Null n’étant pas une valeur on ne peut pas lui appliquer l’opérateur d’égalité.

A propos de la norme SQL, je rappelle la magnifique boulette qu’on y trouve (page 30) et d’aucuns se sont fait remonter les bretelles :

The data type boolean comprises the distinct truth values True and False. Unless prohibited by a NOT NULL constraint, the boolean data type also supports the truth value Unknown as the null value. This specification does not make a distinction between the null value of the boolean data type and the truth value Unknown that is the result of an SQL <predicate>, <search condition>, or <boolean value expression>; they may be used interchangeably to mean exactly the same thing.

Comme le note malicieusement Peter Gulutzan (SQL-99 Complete, Really), selon la norme il revient au même de dire : « Je ne sais pas » et « Je sais que l’information est absente » !

Il manque une composante. 10 millisecondes c'est le temps de service de ces I/O.

Par prototypage des performances, on mesura le surcoût, par exemple pour fixer les idées, pour 200000 transactions/jour impliquant la table CHEQUE. Il m’étonnerait que ça soit rédhibitoire. Même combat pour les commandes : si les IO fichaient la patouille, on rapatrierait les lignes de commande dans les commandes, mais je n’ai jamais observé qu’on en fut réduit à une telle extrémité...

Le calcul 'une ligne=un I/O' se base sur des accès à des données dispersés, car je parlais du 'listing des numéros de chèques du dernier mois'. Le clustering sur l'ID ne va pas beaucoup aider pour cela. Il faudrait un clustering sur type d'opération et date pour cet exemple.

Ceci vaut quelque soit le scénario, que le numéro de chèque figure dans la table OPERATION_COMPTABLE ou dans la table CHEQUE. Par ailleurs, il y a plusieurs façons d’effectuer les traitements périodiques quand la table CHEQUE est impliquée. Par exemple :

— Jointure des deux tables ;
— Production à partir de la table OPERATION_COMPTABLE d’une table temporaire contenant les valeurs d’OperId des chèques concernés ;
— ...

Lister les numéros de chèques du dernier mois est une opération de type batch (en transactionnel l’utilisateur risquerait d'être découragé...), et là encore un prototype de performances permettra de choisir la solution la plus efficace aujourd’hui (et remplaçable le jour où l’on trouvera plus performant), sans que l’on touche à la structure des tables. A la Production de préciser qu’elle fenêtre elle accorde pour le traitement, à nous de nous y conformer. Cela dit le forum SCHÉMA est orienté modélisation, et je pense qu'on n’est donc pas là pour débattre des problèmes de production et des meilleures stratégies pour les résoudre.

Mais ni l'un ni l'autre ne donne d'indication à l'optimiseur, contrairement aux contraintes.

C'est-à-dire ? Quoi qu’il en soit, si avec DB2 for z/OS je branche par exemple un nouvel index sur une table, je peux déclencher un rebind des trigger packages pour réoptimiser :

DB2 - Commande REBIND TRIGGER PACKAGE :
« You might also rebind a trigger package to re-optimize its SQL statements after you create a new index or use the RUNSTATS utility. »

[Richard_35]

Bonjour à tous,

La sémantique utilisée prouve que nous débattons, ici, d'une question d'équilibre ("trop", "seulement", "pas de", etc...).

Comme dans pas mal de sujets, il est mieux de connaître la "théorie extrême" que de ne pas la connaître du tout. Ensuite, cela permet de "transiger".

Reprenons l'exemple de l'attribut "Observation" d'une entité E. Nous voyons bien que cet attribut ne doit pas être obligatoirement renseigné : il peut donc être NULL.
Eh bien, il me semble bon de savoir (et uniquement de savoir, dans un premier temps) que :

E ---0,1---[avoir pour observation]---(1,1)--- E_Obs ;
E(Id_E, ...) ;
E_Obs(#Id_E, Observation) ;
avec repérage des NULL :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
SELECT Id_E 
FROM   E AS x
WHERE  NOT EXISTS 
       (SELECT  1
        FROM    E_Obs AS y
        WHERE   x.Id_E = y.Id_E
       );

et

E(Id_E, ..., Observation, ...) ;
avec repérage des NULL :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
SELECT Id_E 
FROM   E
WHERE  Observation IS NULL ;

sont possibles.

Ensuite, suivant le cas, nous pouvons choisir l'une ou l'autre solution. Ne pas connaître la première option, ou du moins sa gymnastique de l'esprit, peut nous faire passer à côté du cas où il faut absolument l'utiliser.

C'est pour cela que la culture théorique et pratique de Fsmrel est précieuse. Celle de Franck, un peu moins théorique (il me semble, et sauf ton respect Franck, bien entendu ) mais pratique, non moins.

[dba01]

Je n'ai pas bien suivi toutes vos démonstrations, mais pour avoir passé 40 années dans une banque je peux vous dire que nous avons renoncé à utiliser le numéro de chèque comme critère pour ne plus en faire qu'une info.
Les 7 chiffres des numéros de chèque devenaient trop juste pour tout numéroter, alors nous avons décidé un beau jour de numéroter pour chaque compte en partant de 001. C'est ainsi que j'utilise actuellement le numéro 0000354, qui peut facilement se retrouver sur un autre compte, à moi-même ou à quelqu'un d'autre.

Je trouve donc risqué d'organiser une table des numéros de chèques qui peut se trouver en difficultés si on veut gérer plusieurs comptes d'une même entreprise par exemple. Et qu'attendre d'une multi succursales possédant des comptes dans plusieurs guichets, voir plusieurs banques ?

La notion de chèque est même devenue difficile quand il a fallu enregistrer les "provisions pour chèques" et "annulations de chèques". A ce moment, même le libellé "chèque" devenait un problème. D'où la définition plus précise de l'opération de "paiement chèque", possible à statistiquer pour évaluer la rentabilité d'un client ou d'un service par exemple (prix de traitement spécifique).
.

[Richard_35]

Bonjour Dba01,

Je n'ai pas bien suivi toutes vos démonstrations, mais pour avoir passé 40 années dans une banque je peux vous dire que nous avons renoncé à utiliser le numéro de chèque comme critère pour ne plus en faire qu'une info.

==> il s'agit d'un autre débat, non moins intéressant, d'ailleurs.

Si j'ai bien compris, tu veux dire

Je n'ai pas bien suivi toutes vos démonstrations, mais pour avoir passé 40 années dans une banque je peux vous dire que nous avons renoncé à utiliser le numéro de chèque comme critère clé primaire pour ne plus en faire qu'une info qu'un attribut.

Toujours si j'ai bien compris, effectivement, il faut toujours se poser ce genre de question :

une clé primaire qui, intrinsèquement, ne veut rien dire ;

ou

une clé primaire qui, intrinsèquement, porte une signification.

Personnellement, j'opte, à 98% pour la première option.

[seabs]

Bonjour,

@dba01

Je n'ai pas bien suivi toutes vos démonstrations, mais pour avoir passé 40 années dans une banque je peux vous dire que nous avons renoncé à utiliser le numéro de chèque comme critère pour ne plus en faire qu'une info.
Les 7 chiffres des numéros de chèque devenaient trop juste pour tout numéroter, alors nous avons décidé un beau jour de numéroter pour chaque compte en partant de 001. C'est ainsi que j'utilise actuellement le numéro 0000354, qui peut facilement se retrouver sur un autre compte, à moi-même ou à quelqu'un d'autre.

@Richard_35 a raison, il s'agit d'un autre débat. Pour ma part, je pense que ce message est hors sujet dans le cadre de cette discussion.

Il ne viendrait à l'idée, de personne, de créer un index primaire en s'appuyant sur les n° des chèques. D'ailleurs, un attribut basé sur le numéro de chèque ne peut pas être une clé candidate car, a mon avis, il ne respecte pas la règle d'unicité.

@+

[fsmrel]

Bonjour,

Je vois que tout le monde est en pleine forme, y-compris nous, les anciens !


@seabs

Il ne viendrait à l'idée, de personne, de créer un index primaire en s'appuyant sur les n° des chèques.

Je suppose que vous voulez-dire clé primaire ? Le concept d’index primaire existe le plus souvent au niveau physique, mais peut avoir un sens différent selon les SGBD.


@Daniel (dba01)

seabs le sage a bien sûr raison, à lui seul un n° de chèque ne peut pas faire l’objet d’une clé candidate et n’est qu’une pièce du puzzle. Pour prendre son sens, c'est-à-dire justifier par exemple un montant, il doit être accompagné d’éléments participant (selon le contexte) au RIB, à un journal (de banque, de caisse, etc.) une date d’opération et compagnie. C’est bien pour cela du reste que j’avais pris soin ici (au niveau de la tuyauterie donc) de préciser que l’index sur l’attribut ChequeNo n’était pas de type unique :

Pour 10000000 de lignes : 76940 pages de 4KB.
Index UNIQUE et CLUSTER sur OperId : 30300 pages
Index sur ChequeNo (non unique car ChequeNo n’est pas clé candidate) : 24920 pages

Daniel, rappelez-vous la discussion ouverte par seabs (Normalisation et clé numérique) à laquelle vous avez participé, et relisez l’histoire édifiante du SIREN des entreprises.

Je vous invite à relire ce que j’ai écrit à propos des concepts de clé candidate et de clé primaire.

Bonne route en tout cas

[Richard_35]

Bonjour Fsmrel,

100 % d'accord avec toi... je ne sais pas combien de temps j'ai passé à (essayer de) convaincre, toutes boîtes/dirigeants confondus, que l'utilisation d'une clé primaire significative n'est pas judicieuse (et je suis poli).

J'y suis, parfois, arrivé... à l'aide de ce type d'exemple.

Mais, les utilisateurs aiment bien comprendre un minimum de choses, simplement en voyant cette satanée clé primaire qui apparaît partout !

Je pense que cela change... doucement... la logique "informatique" étant visible à (presque) tout le monde : n° de mobile (et encore, le 07... a été ajouté), n°RIO, etc...

[fsmrel]

Hi!

Mais, les utilisateurs aiment bien comprendre un minimum de choses, simplement en voyant cette satanée clé primaire qui apparaît partout !

Si la clé primaire respecte le principe de non signification (ce qui devrait être systématique !), si donc elle revêt un caractère « système », il n'y a aucune raison de la montrer à l'utilisateur : on ne lui présente que les données qui pour lui ont un sens.

[seabs]

Bonjour,

@fsmrel

Je suppose que vous voulez-dire clé primaire ? Le concept d’index primaire existe le plus souvent au niveau physique, mais peut avoir un sens différent selon les SGBD.

Effectivement, il s'agit bien de "Clé primaire"

J'ai commencé à étudier les bases de données et à les utiliser à un moment où la plupart de nos compatriotes arrêtent de travail. Alors, le temps d'apprentissage est plus lent, d'où quelques soucis pour l'assimilation du vocabulaire.

Alors une seule devise continuer.

@+

[Richard_35]

.../... il n'y a aucune raison de la montrer à l'utilisateur .../...

==> oui, mais tu sais bien que, concrètement, cette clé primaire apparaît à beaucoup d'endroits (formulaires, états, extraction Excel, etc...), ne serait-ce que pour vérifier les informations d'une ligne, en particulier.

En effet, il est plus facile de contrôler (ou de faire contrôler) le [client 12345] que le [client Dupond Martin, celui qui habite dans le 92... non, pas celui-là, celui qui habite Puteaux... oui, mais il y en a plusieurs qui habitent Puteaux...].

[pachot]

Bonjour,

Au sujet des clés primaires significatives...

Et y a des identifiants métier. Par exemple, le code ISO des pays, un numéro de compte IBAN,...
Et il y a les clés techniques, utilisées en interne par le système. Par exemple, les inodes d'un filesystem unix, le ROWID d'Oracle.

Attention, j'ai parlé de clé et d'identifiant, mais je n'ai pas précisé primaire.

La première règle, c'est que les clés métiers, les utilisateurs les voient, et ils auront un jour envie d'en modifier une. Donc il faut que le système permette ce changement sans tout casser (intégrité, performances,...)
Ce qui veut dire qu'on ne doit pas l'utiliser pour l'intégrité référentielle, sauf si on a un moyen efficace de faire des update en cascade - mais j'en connais pas.

Qu'elle soit clé primaire ou non n'est pas le problème parce qu'une foreign key n'est pas obligée de référencer une clé primaire. N'importe quelle ensemble de colonnes unique lui suffit.
La règle importante c'est: ne pas avoir une foreign key qui référence une clé métier.

Et la deuxième règle, c'est l'inverse. Une clé technique, on peut être amené à la modifier (si on consolide plusieurs bases en une seule par exemple) et donc il ne faut pas l'exposer en dehors du système. Par exemple, on ne devrait pas exposer dans une url un id technique parce que sinon celui qui l'a bookmarkée aura une erreur 404.
La règle importante c'est: ne pas exposer hors du système une clé technique.

En gros, si la clé métier peut être modifiée, alors à chacun sa clé: une pour le métier et une pour le système.
Mais si on est sûrs qu'elle ne sera jamais modifiée, pourquoi pas l'utiliser aussi dans le système pour notre l'intégrité référentielle ?
Il y a quand même beaucoup de codes ISO qui ne changent pas. Et il y a aussi des clé métiers qui sont générées par le système, exposées à l'utilisateur, mais jamais modifiées.

Ce sont toutes des clés candidates. Ensuite, on met celle qu'on veut comme clé primaire parce que ce choix, c'est un problème d'implémentation. Par exemple si j'ai un SGBD qui regroupe physiquement les données d'après leur clé primaire, je peux choisir la clé métier pour favoriser les filtres sur les critères métier, ou choisir la clé technique pour favoriser les jointures.

En effet, il est plus facile de contrôler (ou de faire contrôler) le [client 12345] que le [client Dupond Martin, celui qui habite dans le 92... non, pas celui-là, celui qui habite Puteaux... oui, mais il y en a plusieurs qui habitent Puteaux...].

Mais le client 12345 c'est une clé métier. Il a probablement été généré par le système, n'a pas de signification, mais il est exposé aux utilisateurs: on l'affiche, on l'envoie par mail, on l'imprime sur les factures, etc. Donc à traiter comme une clé métier.
Par contre c'est une clé métier immuable: on ne va jamais le modifier.
Alors on peut aussi l'utiliser dans le système et le référencer par une foreign key.

Donc en résumé pourquoi pas - a priori - une clé primaire significative si elle n'est pas référencée et/ou si elle n'est jamais modifiée.

Cordialement,
Franck.