Les données d’une base SQL sont des ensembles…

Beaucoup de jeunes développeurs considèrent à tort une base de données comme s’il s’agissait d’écrire des enregistrement dans des fichiers. Il n’en est rien. Les notions de fichiers, d’enregistrement et autres concepts matériels n’existe en aucune façon dans le fonctionnement logique d’un serveur de bases de données relationnelles…

Voyons la chose de manière concrète avec un exemple… Y a t-il un ordre des données dans une table ?

Le script ci dessous met en évidence le fait qu’il n’existe aucun ordre pré établis dans une base de données relationnelles. Toute table est l’équivalent d’un sac de bille. La question suivante :
Dans quel ordre les billes ont-elles été rangées ?
N’a bien évidemment aucun sens…

Démonstration : (les exemples sont données pour MS SQL Server, mais peuvent être reproduits sous n’importe quel SGBDR et donnerons des résultats similaires [ce qui ne veut pas dire identiques !!!]…)


-- créons la base de données
CREATE DATABASE DB_LIGNES
GO
-- plaçons nous dedans
USE DB_LIGNES
GO
-- créons une table de travail
CREATE TABLE T_LIGNES  
(C INT NOT NULL,  
 D CHAR(16),
 E CHAR(32) DEFAULT '*')
GO
-- insérons quelques lignes
INSERT INTO T_LIGNES (C, D) VALUES (1, 'Z')
INSERT INTO T_LIGNES (C, D) VALUES (2, 'Y')
INSERT INTO T_LIGNES (C, D) VALUES (3, 'X')
INSERT INTO T_LIGNES (C, D) VALUES (4, 'W')
INSERT INTO T_LIGNES (C, D) VALUES (5, 'V')

Et maintenant, notre premier SELECT.
(Ce sera exactement le même pendant toute la démo….)


SELECT C, D FROM T_LIGNES
 
C           D
----------- ----------------
1           Z                
2           Y                
3           X                
4           W                
5           V

Apparament les lignes sont dans l’ordre d’insertion


-- supprimons une ligne
DELETE FROM T_LIGNES WHERE C = 3
GO
-- insérons une 6e ligne :
INSERT INTO T_LIGNES (C, D) VALUES (6, 'U')
GO

Second SELECT identique au précédent :


SELECT C, D FROM T_LIGNES
 
C           D
----------- ----------------
1           Z                
2           Y                
6           U                
4           W                
5           V

Patatras… la ligne 6 est en 3e position… Il semble que l’emplacement de la ligne 3 qui a été supprimée a été réutilisé…

Créons maintenant une clef primaire sur la colonne C de la table


ALTER TABLE T_LIGNES
ADD CONSTRAINT PK PRIMARY KEY (C)
GO

Troisième SELECT :


SELECT C, D FROM T_LIGNES
 
C           D
----------- ----------------
1           Z                
2           Y                
4           W                
5           V                
6           U

Re belote… un nouvel ordre des lignes apparaît !

Ajoutons un index à la table :

CREATE INDEX X ON T_LIGNES (D, C)

Faisons un quatrième et dernier SELECT :


SELECT C, D FROM T_LIGNES
 
C           D
----------- ----------------
6           U                
5           V                
4           W                
2           Y                
1           Z

Bingo, nouvel ordre des données…

4 SELECT identiques, 4 ordres des lignes différents… Êtes vous enfin convaincu qu’il n’existe aucun ordre dans les bases de données ?
En fait, et on ne le répétera jamais assez, une table c’est comme un sac de bille… Retrouver la Xeme bille que l’on y a mis n’a donc aucun sens !

Un peu plus loin…

Récemment un internaute se demandais pourquoi alors qu’il mettait un ORDER BY dans une vue (hélas certains SGBDR permettent une telle aberration…) le résultat de son SELECT sur cette vue ne respectait pas l’ordre imposé !

j’ai une petite question qui vous paraitra surement triviale mais qui me laisse perplexe moi petit néophyte en base de données. Voilà, j’ai une requête comme suit :


SELECT      
   TOP (100) PERCENT CAT_ID,  
   CAST(SPACE(CAT_Level) + CAT_NAME AS VARCHAR(64)) AS CAT_NAME,  
   CAT_Level, CAT_LB, CAT_RB,
       (SELECT     COUNT(*) AS Expr1
         FROM          dbo.T_CATALOG_CAT AS T2
        WHERE      (CAT_LB > T1.CAT_LB) AND (CAT_RB < T1.CAT_RB))
            AS CAT_NBR_DESCENDING,
   CAT_DESCRIPTION, CAT_CREATIONDATE, CAT_STARTDATE,  
   CAT_ENDDATE, CAT_RELEASED, CAT_RELEASEDDATE, CAT_URL,  
   CAT_INVENTORY, CAT_COMMENT, CAT_DIMENSION, CAT_TYPE
FROM         dbo.T_CATALOG_CAT AS T1
ORDER BY CAT_LB

Lorsque je la lance directement dans non analyseur de requête pas de souci, mais si je la place dans une vue et que je lance cette vue comme suit :

SELECT * FROM V_CATALOG_CAT

Le order by CAT_LB n’est plus respecté. Est ce normal? Dois je réaliser des order by a chaque fois que je vais rappeler cette vue?

et ma réponse…

Une vue n’est autre qu’un type de table. Une vue est destinée à devoir être requêtée. Elle ne peut donc pas contenir de clause ORDER BY. En imaginant que cela soit possible, quelle serait l’effet de cette requête :


SELECT *
FROM   V_CATALOG_CAT
ORDER  BY CAT_RB, CAT_NAME

Obliger à faire deux tris pour n’en rendre qu’un ???

Comprenez et apprenez que dans les bases de données, qui par nature sont ensemblistes, il n’y a aucun ordre prédéterminé. Même si SQL Server vous permet d’introduire une commande TOP totalement antirelationnelle, cette commande combinée au ORDER BY, ne peut pas vous garantir le moindre ordre !

Et la date alors ????

Autre demande parfaitement idiote : je voudrais savoir à quelle date/heure mes « enregistrements » ont été inséré / mis à jour…
Dans une base de données, on y trouve exactement ce qu’on y met, ni plus ni moins (comme une auberge espagnole). De la même manière qu’il n’y a aucun ordre des lignes (et non des enregistrements – ce terme est totalement inadéquat en terme de SGBDR)il n’y a aucun marqueur de temps capable de spécifier à quel moment telle ou telle ligne a été l’objet d’un INSERT ou d’un DELETE. Ce serait d’ailleurs fort difficile car les SGBDR n’écrivent pas sur le disque les lignes dans l’ordre chronologique des commandes effectuées et cela pour des raisons d’optimisation (écriture par regroupement de contigüité).

***
Fr̩d̩ric BROUARD РSQLpro РMVP SQL Server
Spécialiste SQL/BD modélisation de données
SQL & SGBDR http://sqlpro.developpez.com/
Expert SQL Server : http://www.sqlspot.com
audits – optimisation – tuning – formation

9 réflexions au sujet de « Les données d’une base SQL sont des ensembles… »

  1. Avatar de SQLproSQLpro Auteur de l’article

    Non, pas d’ordre. Dans certains cours ou des étudiants aussi naïfs que vous doutent de ce que je dit, je leur montre que c’est bien le cas en prenant une très grosse table (16 Go de données) dépassant la capacité de la RAM (ceci est important).du serveur sur lequel je travaille.
    Lors d’un premier SELECT, les lignes vont arriver dans un ordre donné et dans le cache va figurer une partie de la table.
    Lors d’un second SELECT, le système va trouver en RAM une partie des données, et les restituer en premier depuis le RAM tandis que le moteur de stockage va monter en RAM les données manquantes et les présenter en dernier.
    Au final nous avons bien deux ordres différentes pour les données.
    A chaque renouvellement de l’opération, l’ordre sera sensiblement différent.
    Pour peu que vous soyez sur un bon SGBDR comme oracle ou SQL Server (je ne parle pas de MySQL qui n’est pas du tout un SGBDR, ni de PostGreSQL qui ne fait pas de parallélisme), il se peut que l’ordre de restitution des données soit très différente du fait que ces SGBDR agissent en parallèle. Donc si un thread monte les données avant l’autre, celui-la passera ses données devant le suivant….

    C’est pénible cette naïveté de remise en question systématique !

    Apprenez… Suivez un cours sur le sujet. mais un bon cours !

  2. Ping : < Le blog de SQLpro/>

  3. Avatar de sqlprosqlpro Auteur de l’article

    //je suppose que dans la norme SQL il y a des « points » qui sont implementation-defined//

    En vérité non. Mais certains éléments n’existent pas dans la norme SQL. Par exemple tout ce qui touche aux notions physiques des bases de données n’est volontairement pas définit. C’est le cas par exemple :
    1) des fichiers et donc du CREATE ALTER, DROP DATABASE…
    2) des index et donc de la façon de stocker les données (CREATE, DROP, ALTER INDEX…)
    3) de la création des connexions et utilisateurs CREATE CONNEXION, CREATE USER…

  4. Avatar de AlpAlp

    Qui plus est, je suppose que dans la norme SQL il y a des « points » qui sont implementation-defined… C’est à dire que le SGBDR l’implémente comme il veut. Donc l’implémentation doit influencer la vision sac à bille, qui de toute manière semble ne devoir être jointe qu’au SQL tel qu’il est défini dans sa norme.

  5. Avatar de KeihilinKeihilin

    D’accord. L’analogie du sac de bille est à prendre d’un point de vu SQL pur !

    Pour SQL, le fait qu’il puisse y avoir un ordre de stockage des données dans le système sous-jacent n’est en fait qu’un cas particulier et en fait il s’en contre-fiche ^^.

  6. Avatar de sqlprosqlpro Auteur de l’article

    Ce qu’il faut comprendre c’est qu’il y a une grande séparation entre les aspects logiques d’un SGBDR (les tables, les lignes, les colonnes) autrement dit comment on manipule les données, et les aspects physiques (espaces de stockage, indexation, fragmentation…). Cette séparation voulue par la théorie est nécessaire pour que fonctionne correctement un SGBDR.
    Tant la théorie que la norme SQL font totalement abstraction des problématique physique… Ainsi les notions d’index, de « storage » (fichiers, groupes de fichiers, tablespaces…) sont totalement inconnu de ces deux mondes. Par exemple une commande comme CREATE INDEX n’existe pas en SQL !
    Il doit en résulter que lorsqu’une commande SQL est envoyée, il n’y a aucune raison pour qu’un ordre de présentation des données soit implicitement réalisé. On doit donc systématiquement s’attendre à recevoir ses données dans n’importe quel ordre. Pré supposer qu’un tel ordre existe conduit assez systématiquement à des erreurs fonctionnelles…

  7. Avatar de KeihilinKeihilin

    Et lorsqu’une table est dotée d’un index cluster ?

    Personnellement, je comprends cette notion de sac de billes comme une marque de l’inprédictibilité de l’ordre, pas une marque d’absence d’ordre…

    L’exemple sur de très grandes tables n’est pas une preuve en soit, puisqu’il est clairement dit que dans ce cas, la source de données est hétérogène (RAM et stockage).

    Bref, j’ai du mal à imaginer le « sac de bille » en terme de structure informatique, et d’autre part, mais je suis peut être à 100 lieues d’un début de compréhension de
    la manière de stocker des données…

  8. Avatar de sqlprosqlpro Auteur de l’article

    Il n’y a absolument aucun ordre des données….

    Le concept de sac de bille n’est d’ailleurs pas de moi mais de Codd (père des bases de données relationnelle et concepteur de l’algèbre relationnelles) qui parle de « bag » sac pour décrire les relations (tables).

    Quand à l' »aléatoirité » des SELECT il est réel et peut être mis en lumière dans de très grandes tables. Comme le SGBDR travaille exclusivement en RAM, si une partie d’une table est déjà en RAM, il arrive qu’il envoie ce qu’il a dans la RAM au client en premier et en parallèle demande au moteur de stockage d’alimenter la mémoire avec les pages non présentes, que le moteur relationnel enverra au client ultérieurement.
    J’ai eu l’occasion de démonter cela, mais le mettre en évidence nécessite à la fois de grandes tables, des serveurs costauds et des clients rapides !!!!

  9. Avatar de KeihilinKeihilin

    Est-ce que l’analogie du sac de billes est vraiment exacte ?

    Si vraiment nous étions dans le cas d’un sac de billes, deux select parfaitement identiques exécutés successivement pourraient présenter les données dans un ordre différent; or il ne semble pas que cela soit le cas !
    Ce sont les ajouts/suppressions de données, de contraintes et d’index qui font varier l’ordre dans ces exemples, mais il existe quand même un ordre. Non ?

Laisser un commentaire