Publicité
+ Répondre à la discussion
Affichage des résultats 1 à 12 sur 12
  1. #1
    Membre Expert Avatar de nuke_y
    Inscrit en
    mai 2004
    Messages
    1 823
    Détails du profil
    Informations forums :
    Inscription : mai 2004
    Messages : 1 823
    Points : 1 812
    Points
    1 812

    Par défaut [10g] Bug connect by nocycle - Requete hierarchique

    Bonjour à tous.

    Je viens vers vous concernant un problème de comportement (un bug ?) dans les requêtes hierarchiques sur des données contenant des boucles.

    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
    With the Partitioning, OLAP and Data Mining options

    Soit la table suivante :

    Code :
    1
    2
    3
    4
    5
    6
    7
    DROP TABLE "OBJECT_LINK";
    CREATE TABLE "OBJECT_LINK"
    ( 
      OBJECT VARCHAR2(10) NOT NULL -- ex: 'MYSCHEMA.MYTABLE.MYCOLUMN'
     ,LINKED_OBJECT VARCHAR2(10) NOT NULL -- ex: 'MYSCHEMA.MYTABLE.MYCOLUMN'
     ,TYPE VARCHAR2(10) NOT NULL -- ex: 'refers'
    );

    Cas 1
    Code :
    1
    2
    3
    4
    A-->B
    ^   |
    |   v
    D<--C
    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    delete from OBJECT_LINK;
     
    insert into OBJECT_LINK VALUES ('A', 'B', 'refers');
    insert into OBJECT_LINK VALUES ('B', 'C', 'refers');
    insert into OBJECT_LINK VALUES ('C', 'D', 'refers');
    insert into OBJECT_LINK VALUES ('D', 'A', 'refers');
     
    select connect_by_root OBJECT, level, TYPE, OBJECT, LINKED_OBJECT
    from "OBJECT_LINK"
    connect by OBJECT = prior LINKED_OBJECT
    START WITH OBJECT = 'A';
    On obtient une erreur car il y a une boucle dans les données :
    ERROR:
    ORA-01436: boucle CONNECT BY dans les données utilisateur
    Il faut utiliser le mot clé "nocycle" :
    Code :
    1
    2
    3
    4
    select connect_by_root OBJECT, level, OBJECT, TYPE, LINKED_OBJECT
    from "OBJECT_LINK"
    connect by nocycle OBJECT = prior LINKED_OBJECT
    START WITH OBJECT = 'A';
    Code :
    1
    2
    3
    4
    5
    6
    CONNECT_BY      LEVEL OBJECT     TYPE       LINKED_OBJ
    ---------- ---------- ---------- ---------- ----------
    A                   1 A          refers     B
    A                   2 B          refers     C
    A                   3 C          refers     D
    A                   4 D          refers     A
    Les 4 références sont bien données. Parfait


    Cas 2

    Code :
    1
    2
    3
    4
    A-->B-->E
    ^   |   |
    |   v   v
    D<--C<--F
    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    delete from OBJECT_LINK;
     
    insert into OBJECT_LINK VALUES ('A', 'B', 'refers');
    insert into OBJECT_LINK VALUES ('B', 'C', 'refers');
    insert into OBJECT_LINK VALUES ('C', 'D', 'refers');
    insert into OBJECT_LINK VALUES ('D', 'A', 'refers');
    insert into OBJECT_LINK VALUES ('B', 'E', 'refers');
    insert into OBJECT_LINK VALUES ('E', 'F', 'refers');
    insert into OBJECT_LINK VALUES ('F', 'C', 'refers');
     
    select connect_by_root OBJECT, level, OBJECT, TYPE, LINKED_OBJECT
    from "OBJECT_LINK"
    connect by nocycle OBJECT = prior LINKED_OBJECT
    START WITH OBJECT = 'A';
    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    CONNECT_BY      LEVEL OBJECT     TYPE       LINKED_OBJ
    ---------- ---------- ---------- ---------- ----------
    A                   1 A          refers     B
    A                   2 B          refers     C
    A                   3 C          refers     D
    A                   4 D          refers     A
    A                   2 B          refers     E
    A                   3 E          refers     F
    A                   4 F          refers     C
    A                   5 C          refers     D
    A                   6 D          refers     A
    Les 4 references sont bien données mais il y a 2 doublons ce qui prouve qu'Oracle ne s'arrête pas dès qu'il commence la boucle :
    3 C refers D
    5 C refers D
    4 D refers A
    6 D refers A

    Ces "doublons" s'expliquent par le fait qu'ils n'ont pas lieu dans le même chemin. Le chemin a donc de l'importance. Si on présente la requête en arbre cela se comprend :

    Code :
    1
    2
    3
    4
    select  lpad(' ', level-1) || level ||' '|| OBJECT ||' '|| TYPE ||' '|| LINKED_OBJECT
    from "OBJECT_LINK"
    connect by nocycle OBJECT = prior LINKED_OBJECT
    START WITH OBJECT = 'A';
    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    9
    1 A refers B
     2 B refers C
      3 C refers D
       4 D refers A
     2 B refers E
      3 E refers F
       4 F refers C
        5 C refers D
         6 D refers A

    Cas 3

    Code :
    1
    2
    3
    4
    5
    6
    7
    A-->C<--D-->F-->L
    |   |   ^   |   ^
    v   v   |   v   |
    B   H-->I   J-->K
    |           |   |
    v           v   v
    E           G   M
    cf ici

    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    delete from OBJECT_LINK;
     
    insert into OBJECT_LINK values ('A', 'C', 'refers');
    insert into OBJECT_LINK values ('A', 'B', 'refers');
    insert into OBJECT_LINK values ('B', 'E', 'refers');
    insert into OBJECT_LINK values ('C', 'H', 'refers');
    insert into OBJECT_LINK values ('H', 'I', 'refers');
    insert into OBJECT_LINK values ('I', 'D', 'refers');
    insert into OBJECT_LINK values ('D', 'F', 'refers');
    insert into OBJECT_LINK values ('D', 'C', 'refers');
    insert into OBJECT_LINK values ('F', 'J', 'refers');
    insert into OBJECT_LINK values ('J', 'K', 'refers');
    insert into OBJECT_LINK values ('J', 'G', 'refers');
    insert into OBJECT_LINK values ('K', 'L', 'refers');
    insert into OBJECT_LINK values ('F', 'L', 'refers');
    insert into OBJECT_LINK values ('K', 'M', 'refers');
     
    select connect_by_root OBJECT, level, OBJECT, TYPE, LINKED_OBJECT
    from "OBJECT_LINK"
    connect by nocycle OBJECT = prior LINKED_OBJECT
    START WITH OBJECT = 'A';
    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    CONNECT_BY      LEVEL OBJECT     TYPE       LINKED_OBJ
    ---------- ---------- ---------- ---------- ----------
    A                   1 A          refers     C
    A                   2 C          refers     H
    A                   3 H          refers     I
    A                   4 I          refers     D
    A                   5 D          refers     F
    A                   6 F          refers     J
    A                   7 J          refers     K
    A                   8 K          refers     L
    A                   8 K          refers     M
    A                   7 J          refers     G
    A                   6 F          refers     L
    A                   1 A          refers     B
    A                   2 B          refers     E
    On a perdu D refers C

    Ceci prouve que la gestion des boucles avec une requête hierarchique n'est pas du tout au point, même en 10g puisqu'on peut perdre des relations (alors que la plupart des cas sont correctement gérés).

    En conclusion, si je ne trouve pas une méthode pour contourner ce comportement je vais être obligé d'abandonner les requêtes hierarchiques pour faire ma récursivité en code applicatif (toujours pratique quand on utilise un ETL ou un outil de requêtage).

    Donc si quelqu'un s'est déjà frotté à ce problème, a du temps pour m'aider ou a ses entrées chez Oracle, merci du coup de main.
    Il vaut mieux monopoliser son intelligence sur des bêtises que sa bêtise sur des choses intelligentes.

  2. #2
    Membre éprouvé
    Inscrit en
    septembre 2004
    Messages
    470
    Détails du profil
    Informations forums :
    Inscription : septembre 2004
    Messages : 470
    Points : 456
    Points
    456

    Par défaut

    Sujet très intéressant.

  3. #3
    Expert Confirmé Sénior

    Avatar de laurentschneider
    Homme Profil pro Laurent Schneider
    Administrateur de base de données
    Inscrit en
    décembre 2005
    Messages
    2 942
    Détails du profil
    Informations personnelles :
    Nom : Homme Laurent Schneider
    Localisation : Suisse

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : Finance

    Informations forums :
    Inscription : décembre 2005
    Messages : 2 942
    Points : 4 880
    Points
    4 880

    Par défaut

    je suis assez d'accord que D refere C est manquant. A mon sens ça vaut le coup d'ouvrir un bug sur Metalink.

    Quant à l'implémentation propre au chemin, c'est bien ça le bug qui fait que CONNECT BY LEVEL<10 fonctionne (à tort ). J'ai ouvert une SR à ce sujet il y a fort longtemps ;-)

  4. #4
    Membre Expert Avatar de nuke_y
    Inscrit en
    mai 2004
    Messages
    1 823
    Détails du profil
    Informations forums :
    Inscription : mai 2004
    Messages : 1 823
    Points : 1 812
    Points
    1 812

    Par défaut

    Salut Laurent. C'est quoi cette histoire de LEVEL < 10 ? Tu peux développer stp ?

    Merci
    Il vaut mieux monopoliser son intelligence sur des bêtises que sa bêtise sur des choses intelligentes.

  5. #5
    Modérateur

    Homme Profil pro Fabien
    Ingénieur d'études en décisionnel
    Inscrit en
    septembre 2008
    Messages
    6 893
    Détails du profil
    Informations personnelles :
    Nom : Homme Fabien
    Âge : 36
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Ingénieur d'études en décisionnel
    Secteur : High Tech - Multimédia et Internet

    Informations forums :
    Inscription : septembre 2008
    Messages : 6 893
    Points : 14 332
    Points
    14 332

    Par défaut

    C'est l'astuce pour générer n lignes à la volée :

    Code :
    1
    2
    select level from dual
    connect by level <= 10
    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    LEVEL
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10

  6. #6
    Modérateur

    Homme Profil pro Fabien
    Ingénieur d'études en décisionnel
    Inscrit en
    septembre 2008
    Messages
    6 893
    Détails du profil
    Informations personnelles :
    Nom : Homme Fabien
    Âge : 36
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Ingénieur d'études en décisionnel
    Secteur : High Tech - Multimédia et Internet

    Informations forums :
    Inscription : septembre 2008
    Messages : 6 893
    Points : 14 332
    Points
    14 332

    Par défaut

    Pour votre soucis, je ne sais pas si c'est viable dans votre cas pratique mais si vous pouvez vous passez des informations level et connect_by_root vous pouvez obtenir tous les résultats ainsi :
    Code :
    1
    2
    3
    4
    5
    SELECT distinct OBJECT, TYPE, LINKED_OBJECT
    FROM OBJECT_LINK
    connect BY nocycle OBJECT = prior LINKED_OBJECT
    START WITH OBJECT in (select object from OBJECT_LINK
                          group by object having count(*) > 1)

  7. #7
    Membre Expert Avatar de nuke_y
    Inscrit en
    mai 2004
    Messages
    1 823
    Détails du profil
    Informations forums :
    Inscription : mai 2004
    Messages : 1 823
    Points : 1 812
    Points
    1 812

    Par défaut

    Hmmm disons que cette méthode va me permettre d'avoir toutes les relations, mais pas toutes les relations en partant d'un point donné.

    J'utilise déjà cette méthode pour "mettre à plat" toutes mes relations à partir de tous les objets possibles, mais je perds toujours des relations dans certains cas de boucles.

    PS : ok je connaissais pas l'astuce du DUAL connect by
    Il vaut mieux monopoliser son intelligence sur des bêtises que sa bêtise sur des choses intelligentes.

  8. #8
    Expert Confirmé Sénior

    Avatar de laurentschneider
    Homme Profil pro Laurent Schneider
    Administrateur de base de données
    Inscrit en
    décembre 2005
    Messages
    2 942
    Détails du profil
    Informations personnelles :
    Nom : Homme Laurent Schneider
    Localisation : Suisse

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : Finance

    Informations forums :
    Inscription : décembre 2005
    Messages : 2 942
    Points : 4 880
    Points
    4 880

    Par défaut

    Citation Envoyé par nuke_y Voir le message
    PS : ok je connaissais pas l'astuce du DUAL connect by
    Et bien tant mieux

    Cette astuce est une perversion du système de hiérarchie et elle a l'avantage et l'inconvénient d'être performante. Mais ce n'est pas une syntaxe légale.

    En fait, il y a boucle dès que la valeur courante existe dans la liste des "PRIOR" précédentes. Cependant si tu fais CONNECT BY 1=1, tu as une boucle infinie, mais comme tu n'as pas de PRIOR, la boucle n'est pas détectée. Différentes astuces plus ou moins réussies ont permis d'abuser cette technique pour créer des lignes. Il y a bien sûr plein de bugs et ce n'est pas supporté à mon humble avis.

    Pour revenir à la relation manquante, je vais tenter de faire ouvrir un bug sur Metalink.

    A+
    Laurent

    PS: bien sûr j'update dès que j'ai le numéro du bug...

  9. #9
    Expert Confirmé Sénior

    Avatar de laurentschneider
    Homme Profil pro Laurent Schneider
    Administrateur de base de données
    Inscrit en
    décembre 2005
    Messages
    2 942
    Détails du profil
    Informations personnelles :
    Nom : Homme Laurent Schneider
    Localisation : Suisse

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : Finance

    Informations forums :
    Inscription : décembre 2005
    Messages : 2 942
    Points : 4 880
    Points
    4 880

    Par défaut

    j'ai ajouté and prior sys_guid() is not null dans la clause connect by, suite à un commentaire sur mon blog.

    Code :
    1
    2
    3
    4
    5
    SELECT connect_by_root OBJECT, level, OBJECT, TYPE, LINKED_OBJECT
    FROM "OBJECT_LINK"
    connect BY nocycle OBJECT = prior LINKED_OBJECT
    and prior sys_guid() is not null
    START WITH OBJECT = 'A';
    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    CONNECT_BY      LEVEL OBJECT     TYPE       LINKED_OBJ
    ---------- ---------- ---------- ---------- ----------
    A                   1 A          refers     B         
    A                   2 B          refers     E         
    A                   1 A          refers     C         
    A                   2 C          refers     H         
    A                   3 H          refers     I         
    A                   4 I          refers     D         
    A                   5 D          refers     C         ====> BINGO
    A                   5 D          refers     F         
    A                   6 F          refers     J         
    A                   7 J          refers     G         
    A                   7 J          refers     K         
    A                   8 K          refers     L         
    A                   8 K          refers     M         
    A                   6 F          refers     L

  10. #10
    Expert Confirmé Sénior

    Avatar de laurentschneider
    Homme Profil pro Laurent Schneider
    Administrateur de base de données
    Inscrit en
    décembre 2005
    Messages
    2 942
    Détails du profil
    Informations personnelles :
    Nom : Homme Laurent Schneider
    Localisation : Suisse

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : Finance

    Informations forums :
    Inscription : décembre 2005
    Messages : 2 942
    Points : 4 880
    Points
    4 880

    Par défaut

    mais c'est probablement une mauvaise idée, Oracle risque de boucler infiniment dans certains cas

    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    9
    WITH t AS
         (SELECT 1 x, 2 y
            FROM DUAL
          UNION ALL
          SELECT 2, 2
            FROM DUAL)
    SELECT * FROM t
    CONNECT BY NOCYCLE x = PRIOR y AND PRIOR SYS_GUID () IS NOT NULL
    START WITH x = 1;
    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    9
    X Y
    - -
    1 2
    2 2
    2 2
    2 2
    2 2
    2 2
    ...

  11. #11
    Expert Confirmé Sénior

    Avatar de laurentschneider
    Homme Profil pro Laurent Schneider
    Administrateur de base de données
    Inscrit en
    décembre 2005
    Messages
    2 942
    Détails du profil
    Informations personnelles :
    Nom : Homme Laurent Schneider
    Localisation : Suisse

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : Finance

    Informations forums :
    Inscription : décembre 2005
    Messages : 2 942
    Points : 4 880
    Points
    4 880

    Par défaut

    peut-être que
    Code :
    1
    2
    CONNECT BY NOCYCLE OBJECT = PRIOR linked_object
                AND OBJECT != PRIOR object
    peut marcher, parfois. Bien sûr le mieux serait de harceller Oracle jusqu'à ce qu'ils fixent leur implémentations de NOCYCLE

  12. #12
    Expert Confirmé Sénior

    Avatar de laurentschneider
    Homme Profil pro Laurent Schneider
    Administrateur de base de données
    Inscrit en
    décembre 2005
    Messages
    2 942
    Détails du profil
    Informations personnelles :
    Nom : Homme Laurent Schneider
    Localisation : Suisse

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : Finance

    Informations forums :
    Inscription : décembre 2005
    Messages : 2 942
    Points : 4 880
    Points
    4 880

    Par défaut

    j'ai écrit un article sur mon blog quant à l'implémentation en 11gR2 des requêtes hiérarchiques

    http://laurentschneider.com/wordpres...rsive-cte.html

Liens sociaux

Règles de messages

  • Vous ne pouvez pas créer de nouvelles discussions
  • Vous ne pouvez pas envoyer des réponses
  • Vous ne pouvez pas envoyer des pièces jointes
  • Vous ne pouvez pas modifier vos messages
  •