Oracle - Les requêtes hiérarchiques

Cet article se base sur des tests et recherches réalisés dans la version 8.1.5 d'Oracle.

Introduction

Tout au long de cet article, nous allons nous intéresser à une structure de données permettant de créer un Forum de discussion, matérialisé par la table suivante :

===
SQL> create table messages (
  2  id number,
  3  id_parent number,
  4  titre varchar2(50),
  5  auteur varchar2(30),
  6  dateheure date,
  7  texte long);

Table créée.
SQL> alter table messages add primary key (id);

Table modifiée.
SQL> alter table messages modify dateheure default sysdate;

Table modifiée.
===

La colonne id_parent contient NULL si le message est le premier message d'un fil de discussion, sinon elle contient l'id du message auquel le message répond. La colonne dateheure a pour valeur par défaut la date système du serveur de base de données. On pourra donc se permettre de l'ignorer lors de l'insertion des messages.

Nous n'allons pas nous attarder sur l'enregistrement des messages dans la table (ordre SQL "insert"). Nous n'allons nous intéresser qu'à la façon dont les données sont lues dans la table, donc aux ordres SQL "select" qu'il faudra exécuter pour obtenir les résultats escomptés.

Voici un exemple de contenu de la table messages :

insert into messages (id, id_parent, titre, auteur, texte)
values (1, NULL, 'Combien d''oeufs dans la pate à crêpes ?',
'John', 'Quelqu''un sait-il combien il faut d''oeufs ?');

insert into messages (id, id_parent, titre, auteur, texte)
values (2, NULL, 'Sondage : votre marque de lait préférée',
'John', 'Dites-moi quelle est votre marque de lait préférée ?');

insert into messages (id, id_parent, titre, auteur, texte)
values (3,1, 'Re : Combien d''oeufs dans la pate à crêpes ?',
'Martine', 'Je crois que c''est 2 par personne non ?');

insert into messages (id, id_parent, titre, auteur, texte)
values (4,3, 'C''est sûrement ça', 'John', 'Tu dois avoir raison, merci !');

insert into messages (id, id_parent, titre, auteur, texte)
values (5,2, 'Re : Sondage : votre marque de lait préférée',
'Fifi', 'Moi c''est Lait Blanc');

insert into messages (id, id_parent, titre, auteur, texte)
values (6,2, 'Re : Sondage : votre marque de lait préférée',
'Petrus', 'SuperMilk');

insert into messages (id, id_parent, titre, auteur, texte)
values (7,4, 'Re : C''est sûrement ça', 'Martine', 'De rien ;o)');

insert into messages (id, id_parent, titre, auteur, texte)
values (8,2, 'Re : Sondage : votre marque de lait préférée',
'Fifi', 'Au fait pourquoi ce sondage ?');

insert into messages (id, id_parent, titre, auteur, texte)
values (9,2, 'Re : Sondage : votre marque de lait préférée',
'Enrico', 'Le lait de chèvre');

insert into messages (id, id_parent, titre, auteur, texte)
values (10,8, 'Re : Sondage : votre marque de lait préférée',
'John', 'Je suis statisticien de formation et j''ai rien d''autre à faire');

insert into messages (id, id_parent, titre, auteur, texte)
values (11, NULL, 'Qui a vu le match de volley hier soir ?',
'Petrus', 'Si qq''un connaît le score, merci de nous le donner...');

insert into messages (id, id_parent, titre, auteur, texte)
values (12,10, 'Re : Sondage : votre marque de lait préférée', 'Fifi', 'Ok');

insert into messages (id, id_parent, titre, auteur, texte)
values (13,3, 'Re : Combien d''oeufs dans la pate à crêpes ?',
'Fifi', 'Oui c''est 2 par personne');

Ce qui nous donne le contenu suivant (par ordre chronologique de création des messages) :

 ID       ID_P    TITRE                            AUTEUR HEURE
------  --------------------------------------------- ------------------
1       Combien d'oeufs dans la pate à crêpes ?         John    16:58:20
2       Sondage : votre marque de lait préférée         John    16:58:37
3 1     Re : Combien d'oeufs dans la pate à crêpes ?   Martine  16:58:51
4 3     C'est sûrement ça                                                         John  16:59:00
5 2     Re : Sondage : votre marque de lait préférée   Fifi     16:59:08
6 2     Re : Sondage : votre marque de lait préférée   Petrus   16:59:14
7 4     Re : C'est sûrement ça                                           Martine        16:59:20
8 2     Re : Sondage : votre marque de lait préférée   Fifi     16:59:26
9 2     Re : Sondage : votre marque de lait préférée   Enrico   16:59:31
10 8    Re : Sondage : votre marque de lait préférée   John     16:59:45
11      Qui a vu le match de volley hier soir ?         Petrus  16:59:51
12 10   Re : Sondage : votre marque de lait préférée   Fifi     16:59:59
13 3    Re : Combien d'oeufs dans la pate à crêpes ?   Fifi     17:00:03

Ainsi : - les deux premiers messages sont des débuts de fil de discussion - le message ID=3 répond au message ID=1 car son ID_PARENT=1 - le message ID=13 répond au message ID=3 car son ID_PARENT=3 - etc...

Notre but est donc d'obtenir une hiérarchie de la forme :

ID=1
|-ID=3
| |-ID=4
| | |-ID=7
| |-ID=13
ID=2
|-ID=5
|-ID=6
|-ID=8
| |-ID=10
| | |-ID=12
|-ID=9
ID=11

Le lien entre un message et son parent est bien sûr fait avec les colonnes ID et ID_PARENT. Voici la syntaxe de l'ordre SQL permettant de récupérer la hiérarchie souhaitée :

===
SQL> select id, id_parent, titre
  2  from messages
  3  start with id_parent is null
  4  connect by id_parent = prior id;

           ID ID_PARENT TITRE
--------- --------- --------------------------------------------------
                1                  Combien d'oeufs dans la pate à crêpes ?
                3                1 Re : Combien d'oeufs dans la pate à crêpes ?
                4                3 C'est sûrement ça
                7                4 Re : C'est sûrement ça
           13            3 Re : Combien d'oeufs dans la pate à crêpes ?
                2                  Sondage : votre marque de lait préférée
                5                2 Re : Sondage : votre marque de lait préférée
                6                2 Re : Sondage : votre marque de lait préférée
                8                2 Re : Sondage : votre marque de lait préférée
           10            8 Re : Sondage : votre marque de lait préférée
           12           10 Re : Sondage : votre marque de lait préférée
                9                2 Re : Sondage : votre marque de lait préférée
           11              Qui a vu le match de volley hier soir ?
13 ligne(s) sélectionnée(s).
===

Notre objectif est atteint : nous avons les messages dans le bon ordre. Une requête hiérarchique permet donc de trier des enregistrements en utilisant des notions d'arborescence inter-enregistrement.

La pseudo-colonne Level

Dans une hiérarchie, on peut avoir un élément A qui a pour enfant un élément B, qui lui-même a pour enfant un élément C, etc... On peut alors dire que l'élément C est au niveau n+2 par rapport au niveau n de l'élément A. Afin de mieux voir la profondeur de hiérarchie, nous allons utiliser la pseudo-colonne LEVEL, qui permet de savoir à quel niveau hiérarchique on se trouve :

===
SQL> select level, titre, auteur
  2  from  messages
  3  start with id_parent is null
  4  connect by id_parent = prior id;

        LEVEL TITRE                                                                                       AUTEUR
--------- -------------------------------------------------- ---------
                1 Combien d'oeufs dans la pate à crêpes ?                        John
                2 Re : Combien d'oeufs dans la pate à crêpes ?          Martine
                3 C'est sûrement ça                                                               John
                4 Re : C'est sûrement ça                                                         Martine
                3 Re : Combien d'oeufs dans la pate à crêpes ?          Fifi
                1 Sondage : votre marque de lait préférée                       John
                2 Re : Sondage : votre marque de lait préférée     Fifi
                2 Re : Sondage : votre marque de lait préférée     Petrus
                2 Re : Sondage : votre marque de lait préférée     Fifi
                3 Re : Sondage : votre marque de lait préférée     John
                4 Re : Sondage : votre marque de lait préférée     Fifi
                2 Re : Sondage : votre marque de lait préférée     Enrico
                1 Qui a vu le match de volley hier soir ?                       Petrus
13 ligne(s) sélectionnée(s).
===

Pour plus de clarté, nous allons faire précéder chaque titre d'un nombre d'espaces égal au level qui lui correspond. Cela génèrera une indentation du plus bel effet !

===
SQL>
  1  select id, id_parent, rpad(' ', level-1) || titre as titre, auteur
  2  from messages
  3  start with id_parent is null
  4  connect by id_parent = prior id;

 ID ID_PARENT   TITRE                                                   AUTEUR
--- ---------   ------------------------------------------------------  ----------
  1             Combien d'oeufs dans la pate à crêpes ?                 John
  3              1       Re : Combien d'oeufs dans la pate à crêpes ?           Martine
  4              3        C'est sûrement ça                                     John
  7              4         Re : C'est sûrement ça                               Martine
 13              3        Re : Combien d'oeufs dans la pate à crêpes ?          Fifi
  2             Sondage : votre marque de lait préférée                 John
  5              2       Re : Sondage : votre marque de lait préférée           Fifi
  6              2       Re : Sondage : votre marque de lait préférée           Petrus
  8              2       Re : Sondage : votre marque de lait préférée           Fifi
 10              8        Re : Sondage : votre marque de lait préférée          John
 12             10         Re : Sondage : votre marque de lait préférée         Fifi
  9              2       Re : Sondage : votre marque de lait préférée           Enrico
 11             Qui a vu le match de volley hier soir ?                 Petrus


13 ligne(s) sélectionnée(s).
===

Nous venons donc de voir à quoi peut servir une requête hiérarchique. Parmis les applications possible de cet outil puissant : - les forums avec fil de discussion (notre exemple) - hiérarchie d'un groupe de personnes (chefs de service - employés) - gestion de catégories et sous-catégories pour organiser des informations - ...

Quelques précisions sur la syntaxe des requêtes hiérarchiques

La clause START WITH permet d'indiquer la condition qui détermine quels enregistrements sont au niveau 1 (aucun enregistrement au-dessus d'eux dans la hiérarchie). Si on n'avait voulu lister que les fils de discussion initiés par John, avec leurs réponses, voici la requête qu'il aurait fallu exécuter :

===
SQL>
  1  select id, id_parent, rpad(' ', level-1) || titre as titre, auteur
  2  from messages
  3  start with id_parent is null and auteur = 'John'
  4* connect by id_parent = prior id
 ID ID_PARENT   TITRE                                                   AUTEUR
--- ---------   ------------------------------------------------------  ----------
  1             Combien d'oeufs dans la pate à crêpes ?                 John
  3              1       Re : Combien d'oeufs dans la pate à crêpes ?           Martine
  4              3        C'est sûrement ça                                     John
  7              4         Re : C'est sûrement ça                               Martine
 13              3        Re : Combien d'oeufs dans la pate à crêpes ?          Fifi
  2             Sondage : votre marque de lait préférée                 John
  5              2       Re : Sondage : votre marque de lait préférée           Fifi
  6              2       Re : Sondage : votre marque de lait préférée           Petrus
  8              2       Re : Sondage : votre marque de lait préférée           Fifi
 10              8        Re : Sondage : votre marque de lait préférée          John
 12             10         Re : Sondage : votre marque de lait préférée         Fifi
  9              2       Re : Sondage : votre marque de lait préférée           Enrico
12 ligne(s) sélectionnée(s).
===

Le 13ème et dernier message n'a pas été sélectionné car il ne fait pas partie d'un fil de discussion initié par John. Les requêtes hiérarchiques sont donc non seulement un moyen de trier les enregistrements, mais aussi un moyen de sélectionner des enregistrements (même sans utilisation de la clause where).

La clause CONNECT BY permet d'indiquer la condition qui lie un enregistrement à son enregistrement père. "connect by id_parent = prior id" doit se lire "la relation père-fils entre deux enregistrements est définie par le fait que la colonne id_parent de l'enregistrement fils est égale à la colonne id de l'enregistrement père" (c'est le mot clé PRIOR qui indique que l'on fait référence au père).

Article écrit par Tittom

Cet article est régulièrement mis à jour par des experts sous la
direction de Jean-François Pillou, fondateur de CommentCaMarche.
Ce document intitulé « Oracle - Les requêtes hiérarchiques » issu de Comment Ça Marche (www.commentcamarche.net) est mis à disposition sous les termes de la licence Creative Commons. Vous pouvez copier, modifier des copies de cette page, dans les conditions fixées par la licence, tant que cette note apparaît clairement.