Discussion :

[JGLord]

Bonjour à vous experts PL/SQL!

À la suite d'une demande d'aide concernant une requête SQL récursive, on m'a conseillé d'utiliser le PL/SQL pour obtenir le résultat désiré. Malheureusement, je ne m'y connais pas en PL/SQL, alors j'apprécierais qu'on puisse m'aider à y arriver (idéalement avec une solution complète (fonctionnelle), détaillée et commentée).

La problématique est la suivante: J'essaie d'obtenir une liste de données classée par connectivité (relation parent-enfant), et ce, tout en omettant certains types de données dans un environnement Oracle 11gR2. Pour illustrer mes propos, c'est comme si je voulais partir d'un arbre généalogique et en créer un nouveau, mais en excluant les personnes ayant des cheveux roux (je n'ai rien contre les personnes rousses )...

À titre d'exemple, voici les informations que j'ai dans ma table que j'appelle "OriginalSequence"

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
WITH OriginalSequence AS
(
SELECT 'ID1' AS KEY, 'INVALID1' AS CHILD, NULL AS PARENT FROM dual union ALL
SELECT 'INVALID1'  , 'ID2'              , 'ID1'          FROM dual union ALL
SELECT 'INVALID1'  , 'ID3'              , 'ID1'          FROM dual union ALL
SELECT 'ID2'       , 'INVALID2'         , 'INVALID1'     FROM dual union ALL
SELECT 'ID3'       , 'ID4'              , 'INVALID1'     FROM dual union ALL
SELECT 'ID3'       , 'INVALID3'         , 'INVALID1'     FROM dual union ALL
SELECT 'INVALID2'  , NULL               , 'ID2'          FROM dual union ALL
SELECT 'ID4'       , NULL               , 'ID3'          FROM dual union ALL
SELECT 'INVALID3'  , 'ID5'              , 'ID3'          FROM dual union ALL
SELECT 'INVALID3'  , 'INVALID4'         , 'ID3'          FROM dual union ALL
SELECT 'ID5'       , 'ID6'              , 'INVALID3'     FROM dual union ALL
SELECT 'INVALID4'  , 'ID7'              , 'INVALID3'     FROM dual union ALL
SELECT 'ID6'       , NULL               , 'ID5'          FROM dual union ALL
SELECT 'ID7'       , 'ID8'              , 'INVALID4'     FROM dual union ALL
SELECT 'ID8'       , NULL               , 'ID7'          FROM dual
)

IMPORTANT: Même si dans mon exemple je montre le contraire, les données de ma "vraie" table NE SONT PAS triées en ordre de connectivité.


Dans ma table "OriginalSequence"

• La colonne "KEY" est le nom de ma clé courante.
• La colonne "Child" est le nom de la clé suivant (après) la clé courante.
• La colonne "Parent" est le nom de la clé précédant (avant) la clé courante.
• Est considéré "invalide" une clé qui débute par "INVALID".

De façon visuelle, les données de mon exemple ressemblent à ceci: http://i.xomf.com/npckv.png

Par contre, NE JAMAIS oublier que les données de ma "vrai" table NE SONT PAS triées en ordre de connectivité.

Alors, ce que j'aimerais faire c'est d'omettre (sauter par dessus) les données invalides de façon à avoir le résultat suivant:

KEY,NEW_CHILD,NEW_PARENT
ID1,ID2      ,null
ID1,ID3      ,null
ID2,null     ,ID1
ID3,ID4      ,ID1
ID3,ID5      ,ID1
ID3,ID7      ,ID1
ID4,null     ,ID3
ID5,ID6      ,ID3
ID6,null     ,ID5
ID7,ID8      ,ID3
ID8,null     ,ID7

Ce qui donne de façon visuelle ceci: http://i.xomf.com/vqqcp.png

Merci beaucoup!

[Zyniel]

Bonjour,

NOTE: Ceci est une méthode pour 10g. La méthode 11g, bien plus normée a été exposée dans le lien proposé plus bas dans ce post et ramenant à la section SQL.
Tout d'abord, merci pour votre sujet interessant, j'ai adoré passer un petit moment à me torturer l'esprit pour tenter de le résoudre.

Je fais cependant une entorse à votre demande car je vais tenter une approche purement SQL Oracle (Je fais bien noter SQL Oracle, car je vais faire appel à des fonctionnalités du SGBD).

Problème à solutionner et postulats:

• Les données de la table ne sont pas triées
• Certaines lignes, dont les racines peuvent être considérées comme invalides
• Contrairement à une structure d'arbre classique, les noeuds apparaissent plusieurs fois
• La restitution des lignes se fait tel un parcours en profondeur mais les noeuds de branches sont répétés (?)
• Pas de récursivité dans l'arbre

Fonctionnalités utilisées
Ma solution se base sur un certain nombre de fonctionnalités Oracle

• Fonctions SQL Hiérarchiques : Permettront de reconstituer l'arbre à partir des données sources
• Fonctions d'Expressions régulières : Permettront de récupérer les parents en sautant les Invalides
• Jointures Externes : Permettront de démultiplier les noeuds pour chaque branche
• Parcours en profondeur : Trouver le dernier parent valide revient en partant des branches à remonter l'arbre en parcours par profondeur jusqu'à trouver un noeud valide.

Approche et Résolution
Avant toute chose, et pour simplifier le problème et les requêtes j'ai préféré mettre les données dans une seule et même table afin de ne pas me trimballer un WITH de plus.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
CREATE TABLE OriginalSequence AS 
WITH OriginalSequence AS
(
SELECT 'ID1' AS KEY, 'INVALID1' AS CHILD, NULL AS PARENT FROM dual union ALL
--SELECT 'INVALID0' AS KEY, 'INVALID1' AS CHILD, NULL AS PARENT FROM dual union ALL -- Pour tester les racines Invalides
--SELECT 'INVALID1'  , 'ID2'              , 'INVALID0'          FROM dual union ALL -- Pour tester les racines Invalides
--SELECT 'INVALID1'  , 'ID3'              , 'INVALID0'          FROM dual union ALL -- Pour tester les racines Invalides
SELECT 'INVALID1'  , 'ID2'              , 'ID1'          FROM dual union ALL
SELECT 'INVALID1'  , 'ID3'              , 'ID1'          FROM dual union ALL
SELECT 'ID2'       , 'INVALID2'         , 'INVALID1'     FROM dual union ALL
SELECT 'ID3'       , 'ID4'              , 'INVALID1'     FROM dual union ALL
SELECT 'ID3'       , 'INVALID3'         , 'INVALID1'     FROM dual union ALL
SELECT 'INVALID2'  , NULL               , 'ID2'          FROM dual union ALL
SELECT 'ID4'       , NULL               , 'ID3'          FROM dual union ALL
SELECT 'INVALID3'  , 'ID5'              , 'ID3'          FROM dual union ALL
SELECT 'INVALID3'  , 'INVALID4'         , 'ID3'          FROM dual union ALL
SELECT 'ID5'       , 'ID6'              , 'INVALID3'     FROM dual union ALL
SELECT 'INVALID4'  , 'ID7'              , 'INVALID3'     FROM dual union ALL
SELECT 'ID6'       , NULL               , 'ID5'          FROM dual union ALL
SELECT 'ID7'       , 'ID8'              , 'INVALID4'     FROM dual union ALL
SELECT 'ID8'       , NULL               , 'ID7'          FROM dual
) SELECT * FROM OriginalSequence;

La suppression :

Code :

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

1
2
 
DROP TABLE OriginalSequence;

Voici la bête complète:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
 
WITH tree_data_os
AS (
SELECT os.rown
      ,os.is_leaf
      ,os.key
      ,os.old_parent
      ,os.new_parent
      --,os.is_invalid
      --,os.tree_path_all
      --,os.tree_path_valid_clean
FROM
( 
SELECT os.rown
      ,os.is_leaf
      ,os.key
      ,os.parent as old_parent
      --,os.is_invalid
      --,os.tree_path_all
      --,os.tree_path_valid_clean
      ,CASE WHEN instr(os.tree_path_valid_clean, '/', -1, 2) > 0 THEN
               substr(
                  substr(os.tree_path_valid_clean,
                         instr(os.tree_path_valid_clean, '/', -1, 2)),
                  2,
                  length('/' || os.key) -1
               )  
            ELSE
              NULL
       END as new_parent
  FROM (SELECT rownum as rown
              ,CONNECT_BY_ROOT(os.key) as root
              ,connect_by_isleaf AS is_leaf
              ,os.key
              ,os.parent
              --,os.is_invalid
              ,regexp_replace(sys_connect_by_path(os.key_valid,'/'),'/+', '/') as tree_path_valid_clean
              --,sys_connect_by_path(os.key,'/') as  tree_path_all             
              ,os.key_valid
          FROM (SELECT DISTINCT os.key
                               ,os.parent
                               -- ,CASE WHEN instr(os.key,'INVALID')  > 0 THEN 1    ELSE 0      END AS is_invalid
                               ,CASE WHEN instr(os.key ,'INVALID') > 0 THEN NULL ELSE os.key END AS key_valid
                  FROM originalsequence os) os
        CONNECT BY PRIOR os.key = os.parent
         START WITH PARENT IS NULL) os
        WHERE os.key_valid IS NOT NULL) os)
SELECT tos1.key AS key,
       tos2.key AS new_child,
       tos1.new_parent AS new_parent,
       tos1.rown as order1,
       tos2.rown as order2       
FROM   tree_data_os tos1
LEFT OUTER  JOIN tree_data_os tos2
ON     tos1.key = tos2.new_parent
ORDER BY tos1.rown, tos2.rown

Voici ma méthoden en pur SQL. (Oui, il serait possible de le faire en Hiérarchique récursive 11g. J'ai du le faire en 10g d'où l'utilisation des fonctions CONNECT BY ...)
PREMIER SELECT
L'objectif ici est de préparer les données pour les rendre utilisables sous forme d'arbre.

1. Récupération uniquement l'identifiant "KEY" et le "PARENT" afin de faire une requête hiérarchique
2. Suppression des doublons causés par la colonne "CHILD".
3. Marquage des lignes que je considère comme non valides en ne gardant que la "KEY" des éléments valides

SECOND SELECT
L'objectif est de construire un arbre à partir des données via requête hiérarchique, de sauter les noeuds invalides

1. Construction d'une requête hiérarchique. On part des racines, les lignes "PARENT" IS NULL et l'on construit un arbre en joignant via "CONNECT BY PRIOR" les lignes dont le "PARENT" est "KEY"
2. Récupération du chemin parcouru par le moteur pour la construction de l'arbre via "SYS_CONNECT_BY_PATH". Le moteur Oracle parcourt alors les données en profondeur et retourne un arbre trié ainsi. La fonction SYS_CONNECT_BY_PATH permet de retourner le cheminement du parcours en profondeur pour aller jusqu'au noeud courant. C'est exactement le cheminement qu'il nous faut. Sauter des noeuds parents ne peut se faire simplement que via un parcours en profondeur. On fera donc le SYS_CONNECT_BY_PATH sur "KEY_VALID" qui mettra des NULL sur les noeuds invalides et se chargera se ne garder que le parcours d'éléments valides de bas en haut.
3. Suppression des occurrences multiples de séparateurs '/' issus des noeuds invalides => Obtention d'un cheminement en sautant les invalides depuis la racine
4. Récupération de l'ordre des lignes pour ne pas perdre le tri
5. Récupération de données tierces pour le DEBUG et les tests (IS_LEAF et ROOT)
   

   Code :

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

   1
   2
   3
   4
   5
   6
   7
   8
   9
   10
   11
   12
   13
   14
    
   ROWN  ROOT  IS_LEAF  KEY       PARENT    IS_INVALID  TREE_PATH_VALID_CLEAN  TREE_PATH_ALL                                KEY_VALID
   1     ID1   0        ID1                 0           /ID1                   /ID1                                         ID1
   2     ID1   0        INVALID1  ID1       1           /ID1/                  /ID1/INVALID1  
   3     ID1   0        ID2       INVALID1  0           /ID1/ID2               /ID1/INVALID1/ID2                            ID2
   4     ID1   1        INVALID2  ID2       1           /ID1/ID2/              /ID1/INVALID1/ID2/INVALID2  
   5     ID1   0        ID3       INVALID1  0           /ID1/ID3               /ID1/INVALID1/ID3                            ID3
   6     ID1   1        ID4       ID3       0           /ID1/ID3/ID4           /ID1/INVALID1/ID3/ID4                        ID4
   7     ID1   0        INVALID3  ID3       1           /ID1/ID3/              /ID1/INVALID1/ID3/INVALID3  
   8     ID1   0        ID5       INVALID3  0           /ID1/ID3/ID5           /ID1/INVALID1/ID3/INVALID3/ID5               ID5
   9     ID1   1        ID6       ID5       0           /ID1/ID3/ID5/ID6       /ID1/INVALID1/ID3/INVALID3/ID5/ID6           ID6
   10    ID1   0        INVALID4  INVALID3  1           /ID1/ID3/              /ID1/INVALID1/ID3/INVALID3/INVALID4  
   11    ID1   0        ID7       INVALID4  0           /ID1/ID3/ID7           /ID1/INVALID1/ID3/INVALID3/INVALID4/ID7      ID7
   12    ID1   1        ID8       ID7       0           /ID1/ID3/ID7/ID8       /ID1/INVALID1/ID3/INVALID3/INVALID4/ID7/ID8  ID8

6. Suppression des doublons causés par la colonne "CHILD".

TROISIEME SELECT
L'arbre est remonté, toutes les lignes valides valides sont à garder. Pour chaque ligne valide, on connait le cheminement via le "TREE_PATH_VALID_CLEAN" pour aller jusqu'à chaque élément. Trouver le parent, dans la chaîne formée revient à trouver, si il existe le noeud parcouru juste avant. Toute ligne qui n'a pas au moins deux neuds sera donc une racine.

• Récupération du dernier parent "NEW_PARENT" à partir du "TREE_PATH_VALID_CLEAN"
  
  1. Récupération des deux dernières parties de chaque "TREE_PATH_VALID_CLEAN" en prenant tous les caractères de la chaîne à partir de l'avant dernier '/' en partant de la fin
  2. Puis retrait de la chaîne '/' || <ID DU NOEUD COURANT afin de ne garder que le noeud parent valide.
  3. Bien prendre les caractères en ne prenant pas le premier '/'
  
  

  Code :

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

  1
  2
  3
  4
  /ID1/ID3/ID7  ===> Parent: ID3
  /ID1/......IDN/IDY  ===> Parent: IDN
  /ID1  ===> Parent: NULL

  Ne faire celà que pour les lignes ayant deux noeuds. les autres seront donc des racines et seront NULL.
• Suppression des lignes invalides maintenant inutiles

A ce niveau il est possible de récupérer un arbre classique des données:

Code :

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

1
2
3
4
5
6
7
8
9
10
 
KEY  NEW_PARENT  ORDER1
ID1              1
ID2  ID1         3
ID3  ID1         5
ID4  ID3         6
ID5  ID3         8
ID6  ID5         9
ID7  ID3         11
ID8  ID7         12

QUATRIEME SELECT
Création d'une auto-jointure à gauche des données de l'arbre structuré afin de permettre de dédoubler à nouveau les lignes de noeuds de branches comme souhaité. Celà va permettre de récupérer ainsi la notion de "NEW_CHILD"
Afin de garder le même ordre, je trie une première fois sur le rown de la première table à gauche pour garder le semblant d'ordre de parcours en profondeur, puis je trie en secondaire sur le rown de la jointure de droite pour prendre les noeuds de gauche à droite.

Résultat Final

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
KEY  NEW_CHILD  NEW_PARENT  ORDER1  ORDER2  
ID1  ID2                    1       3
ID1  ID3                    1       5
ID2             ID1         3       3
ID3  ID4        ID1         5       6
ID3  ID5        ID1         5       8
ID3  ID7        ID1         5       11
ID4             ID3         6       
ID5  ID6        ID3         8       9
ID6             ID5         9       
ID7  ID8        ID3         11      12
ID8             ID7         12

J'espère que je n'ai perdu personne dans la démonstration. J'espère surtout ne pas avoir fait de boulettes, car je n'ai pu consacrer au problème qu'un petit moment.

NOTE: Oui, il y a surement bien plus optimisé sur certaines parties. Il y a même surement des incohérences ou des améliorations à apporter sur la gestion des doublons.
Certes, dans le meilleur des mondes il faudrait utiliser la syntaxe WITH + Hierarchical Récursif pour se mettre au goût du jour ... Mais cette approche reste un exemple pour donner des idées de ce qui peut-être fait.
Dans cette optique, merci de faire des remarques, donner des avis et des optimisations :]

En PL/SQL ...
A mon avis, il serait simple de fabriquer une structure de stockage des données sous forme d'arbre avec des types PL/SQL ou Oracle, de stocker les données sous forme d'arbre puis de faire un parcours en profondeur pour marquer les NEW_PARENT en fonction du dernier parent valide.

Peut-être que si j'ai du temps ce WE je le ferai...


Références:

• http://docs.oracle.com/cd/B19306_01/...nctions164.htm
• http://docs.oracle.com/cd/B19306_01/...queries003.htm
• http://docs.oracle.com/cd/B19306_01/...nctions130.htm
• http://docs.oracle.com/cd/B19306_01/...nctions162.htm
• http://psoug.org/reference/with.html

[jkofr]

Hello,
Intéressant comme case:-)

Il me semble qu'il y a plus simple mais à confirmer...
ps: J'ai volontairement changé l'ordre des lignes:-)

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
 
select LPAD(' ', 2*lev-1)||thekey 
from
(WITH OriginalSequence AS
(
SELECT 'INVALID3'AS KEY , 'INVALID4'AS CHILD,'ID3'AS PARENT  FROM dual union ALL
SELECT 'ID1'       , 'INVALID1'         , NULL           FROM dual union ALL
SELECT 'ID4'       , NULL               , 'ID3'          FROM dual union ALL
SELECT 'INVALID3'  , 'ID5'              , 'ID3'          FROM dual union ALL
SELECT 'ID5'       , 'ID6'              , 'INVALID3'     FROM dual union ALL
SELECT 'ID3'       , 'ID4'              , 'INVALID1'     FROM dual union ALL
SELECT 'ID3'       , 'INVALID3'         , 'INVALID1'     FROM dual union ALL
SELECT 'INVALID2'  , NULL               , 'ID2'          FROM dual union ALL
SELECT 'ID6'       , NULL               , 'ID5'          FROM dual union ALL
SELECT 'ID7'       , 'ID8'              , 'INVALID4'     FROM dual union ALL
SELECT 'INVALID4'  , 'ID7'              , 'INVALID3'     FROM dual union ALL
SELECT 'INVALID1'  , 'ID2'              , 'ID1'          FROM dual union ALL
SELECT 'INVALID1'  , 'ID3'              , 'ID1'          FROM dual union ALL
SELECT 'ID2'       , 'INVALID2'         , 'INVALID1'     FROM dual union ALL
SELECT 'ID8'       , NULL               , 'ID7'          FROM dual
)
select distinct key thekey, level lev
from OriginalSequence connect by prior key=parent start with parent is null
order  by 1)
where thekey not like '%INV%'

Résultat:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
THEKEY   
---------------------------
 ID1                       
     ID2                   
     ID3                   
       ID4                 
         ID5               
           ID6             
           ID7             
             ID8           
 
 8 rows selected

Par la suite en PL/SQL tu fais un curseur dans un block ou une procédure.

Dit moi si cela correspond à ton besoin.
jko

[Zyniel]

La solution que vous proposez est simple et évidente et sert de base à la première partie de l'algorithme de ma réponse.

Dans le cas ou l'utilisateur n'a pas à remonter les notions de NEW_PARENT et de NEW_CHILD, elle sert amplement (pour faire un arbre directement par exemple).

Par contre, toute remontée des informations de parentée, entrainera de la plomberie supplémentaire.

[jkofr]

Hello,

Exact.

C'était juste en observant les 2 exemples.
Maintenant attendons de voir si JGLord est intéressé d'avoir la totalité des infos.

jko

[Waldar]

Une réponse SQL a été fournie par aieeeuuuuu dans ce sujet :
http://www.developpez.net/forums/d13...ete-recursive/

[JGLord]

Malheureusement, je ne crois pas que cette approche va m'être bien utile, car je dois effectivement "remonter les notions de NEW_PARENT et de NEW_CHILD". Par exemple, pour mes traitements les ID5 et ID7 doivent être au même niveau que le ID4.

Hello,
Intéressant comme case:-)

Il me semble qu'il y a plus simple mais à confirmer...
ps: J'ai volontairement changé l'ordre des lignes:-)

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
 
select LPAD(' ', 2*lev-1)||thekey 
from
(WITH OriginalSequence AS
(
SELECT 'INVALID3'AS KEY , 'INVALID4'AS CHILD,'ID3'AS PARENT  FROM dual union ALL
SELECT 'ID1'       , 'INVALID1'         , NULL           FROM dual union ALL
SELECT 'ID4'       , NULL               , 'ID3'          FROM dual union ALL
SELECT 'INVALID3'  , 'ID5'              , 'ID3'          FROM dual union ALL
SELECT 'ID5'       , 'ID6'              , 'INVALID3'     FROM dual union ALL
SELECT 'ID3'       , 'ID4'              , 'INVALID1'     FROM dual union ALL
SELECT 'ID3'       , 'INVALID3'         , 'INVALID1'     FROM dual union ALL
SELECT 'INVALID2'  , NULL               , 'ID2'          FROM dual union ALL
SELECT 'ID6'       , NULL               , 'ID5'          FROM dual union ALL
SELECT 'ID7'       , 'ID8'              , 'INVALID4'     FROM dual union ALL
SELECT 'INVALID4'  , 'ID7'              , 'INVALID3'     FROM dual union ALL
SELECT 'INVALID1'  , 'ID2'              , 'ID1'          FROM dual union ALL
SELECT 'INVALID1'  , 'ID3'              , 'ID1'          FROM dual union ALL
SELECT 'ID2'       , 'INVALID2'         , 'INVALID1'     FROM dual union ALL
SELECT 'ID8'       , NULL               , 'ID7'          FROM dual
)
select distinct key thekey, level lev
from OriginalSequence connect by prior key=parent start with parent is null
order  by 1)
where thekey not like '%INV%'

Résultat:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
THEKEY   
---------------------------
 ID1                       
     ID2                   
     ID3                   
       ID4                 
         ID5               
           ID6             
           ID7             
             ID8           
 
 8 rows selected

Par la suite en PL/SQL tu fais un curseur dans un block ou une procédure.

Dit moi si cela correspond à ton besoin.
jko

[JGLord]

Premièrement, un GROS merci à Zyniel pour avoir donné tant de détail dans la démarche, je n'aurais jamais pu espérer mieux!

J'ai bien hâte de tester le tout dans mon "vrai" environnement et de comparer la performance à la version de aieeeuuuuu, qui est aussi terriblement performante.



Néanmoins, j'apprécierais qu'on puisse me donner une solution en PL/SQL (idéalement aussi détaillée que la solution de Zyniel) afin que je puisse voir et surtout apprendre la logique du PL/SQL en fonction de mon exemple (c'est toujours plus facile de partir d'exemple qu'on connaît). Je vous rappelle que je ne connais pas le PL .

Avec un exemple en PL/SQL fonctionnel, je pourrais comparer la performance d'utiliser cette avenue par rapport aux solutions de Zyniel et aieeeuuuuu. Je parle de performance, car dans mon "vrai" environnement j'ai plusieurs centaines de millions de rows à traiter...


Finalement, pour ceux qui aimeraient essayer d'optimiser la solution de Zyniel:
http://sqlfiddle.com/#!4/f2c33/1
ou de aieeeuuuuu:
http://sqlfiddle.com/#!4/7988c/1

[aieeeuuuuu]

Bonjour,

En fait je pense que l'on peut améliorer la solution que j'avais proposée, en calculant le prédécesseur directement dans la récursive, afin d'éviter la jointure externe finale.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
;WITH cte ( CLE, SUIVEUR,PREDECESSEUR)
 AS (
	SELECT  CLE, SUIVEUR, PREDECESSEUR
	FROM MaSequence
	WHERE PREDECESSEUR IS NULL
	UNION ALL
	SELECT 
       CASE WHEN S.CLE LIKE 'INVALID%' THEN P.CLE ELSE S.CLE END 
     , S.SUIVEUR
     , CASE WHEN P.SUIVEUR LIKE 'INVALID%' THEN P.PREDECESSEUR ELSE P.CLE END
	FROM cte P
	INNER JOIN MaSequence S
		ON S.CLE=P.SUIVEUR
)
SELECT CLE, SUIVEUR, PREDECESSEUR
FROM cte 
WHERE CLE NOT LIKE 'INVALID%'
AND (SUIVEUR NOT LIKE 'INVALID%' OR SUIVEUR IS NULL)