Connect by prior : pourquoi ça marche pas [Résolu]

[StringBuilder]

Décidément, Oracle a décidé me m'ennuyer jusqu'au bout ce matin...

J'ai une table avec la structure suivante :

TIE (CODSOC, TYPTIE, SIGTIE, TYPGRP, SIGGRP, ...)

Souligné : Clé primaire
Gras : Foreign key sur TIE (CODSOC, TYPTIE, SIGTIE)

La table TIE contient donc des tiers.
Ils peuvent être regroupés sous un autre tiers.

Par exemple :
Magasin 1 et Magasin 2 sont regroupés dans un même point de livraison, qui lui-même est regroupé dans une plateforme régionale, qui est elle-même regroupée dans une centrale d'achat nationale, qui elle-même est regroupée dans une enseigne.

J'ai donc les données :

Code :

1
2
3
4
5
6
7
8
 
CODSOC TYPTIE SIGTIE TYPGRP SIGGRP NOMTIE
   100 CLI    M1     PDL    P1     Magasin 1
   100 CLI    M2     PDL    P1     Magasin 2
   100 PDL    P1     REG    R1     PDL 1
   100 REG    R1     ACH    A1     Plateforme 1
   100 ACH    A1     ENS    E1     Centrale d'achat 1
   100 ENS    E1                   Enseigne 1

Je souhaite retrouver l'ensemble des parents du client M1 :

Code :

1
2
3
4
5
6
 
SELECT level, typtie, sigtie
FROM tie
WHERE codsoc = 100
CONNECT BY typtie = typgrp AND sigtie = siggrp
START WITH typtie = 'CLI' AND sigtie = 'M1';

Ca ne me retourne que la ligne M1... Pourquoi j'ai pas l'ensemble des parents ?

J'ai déjà tenté d'intervertir l'ordre des champs dans le connect by, mais ça ne change rien...

J'ai aussi tenté avec une CTE, mais j'ai pas trouvé la syntaxe pour faire une auto-jointure.

[Rei Ichido]

Il faut préciser les PRIOR :

Code :

1
2
3
4
5
6
 
SELECT level, typtie, sigtie
FROM tie
WHERE codsoc = 100
CONNECT BY PRIOR typgrp = typtie AND PRIOR siggrp = sigtie
START WITH typtie = 'CLI' AND sigtie = 'M1';

[StringBuilder]

Merci, ça marche impec !

[Waldar]

Il faut aussi rajouter le codsoc dans le connect by sinon vous allez avoir des suprises.

[StringBuilder]

Envoyé par Waldar

Il faut aussi rajouter le codsoc dans le connect by sinon vous allez avoir des suprises.

J'ai mis en filtre en dur sur CODSOC = 100 dans le WHERE, donc pas besoin ici.

J'ai jamais compris d'ailleurs pourquoi il n'y avait pas un SOCGRP... Un groupe est forcément défini dans la même société que ses membres... Ca limite vachement la notion de référentiel supportée par le logiciel mais bon...

Du coup, à partir du moment où le CODSOC est filtré, pas besoin de le spécifier dans le PRIOR.

[Waldar]

Le where dans les requêtes récursives s'applique après la récursion connect by.
Avec de multiples branches, la requête va partir dans tous les codsoc pour ne filtrer qu'au résultat.
Pour filtrer au départ, c'est à mettre dans le START WITH.

En plus, vous zappez la récursion par la PK ce qui est dommage.

J'ai recréé la table avec les données que vous avez fournies, regardez les plans d'exécution, c'est très net :

Code :

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
    SELECT level, typtie, sigtie, nomtie
      FROM tie
     WHERE codsoc = 100
CONNECT BY PRIOR typgrp = typtie
       AND PRIOR siggrp = sigtie
START WITH typtie = 'CLI'
       AND sigtie = 'M1';
 
------------------------------------------------------------------------------------------------------------------------     
| Id  | Operation                  | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |     
------------------------------------------------------------------------------------------------------------------------     
|   0 | SELECT STATEMENT           |      |      1 |        |      8 |00:00:00.01 |      18 |       |       |          |     
|*  1 |  FILTER                    |      |      1 |        |      8 |00:00:00.01 |      18 |       |       |          |     
|*  2 |   CONNECT BY WITH FILTERING|      |      1 |        |      9 |00:00:00.01 |      18 |  2048 |  2048 | 2048  (0)|     
|*  3 |    TABLE ACCESS FULL       | TIE  |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |     
|*  4 |    HASH JOIN               |      |      5 |        |      5 |00:00:00.01 |      15 |  1088K|  1088K|  727K (0)|     
|   5 |     CONNECT BY PUMP        |      |      5 |        |      5 |00:00:00.01 |       0 |       |       |          |     
|   6 |     TABLE ACCESS FULL      | TIE  |      5 |      1 |     35 |00:00:00.01 |      15 |       |       |          |     
------------------------------------------------------------------------------------------------------------------------     
 
 
PLAN_TABLE_OUTPUT                                                                                                            
-----------------------------------------------------------------------------------------------------------------------------
Predicate Information (IDENTIFIED BY operation id):                                                                          
---------------------------------------------------                                                                          
 
   1 - filter("CODSOC"=100)                                                                                                  
   2 - access("TYPTIE"=PRIOR NULL AND "SIGTIE"=PRIOR NULL)                                                                   
   3 - filter(("TYPTIE"='CLI' AND "SIGTIE"='M1'))                                                                            
   4 - access("TYPTIE"=PRIOR NULL AND "SIGTIE"=PRIOR NULL)

Code :

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
    SELECT level, typtie, sigtie, nomtie
      FROM tie
     WHERE codsoc = 100
CONNECT BY PRIOR typgrp = typtie
       AND PRIOR siggrp = sigtie
       AND PRIOR codsoc = codsoc
START WITH typtie = 'CLI'
       AND sigtie = 'M1';
 
------------------------------------------------------------------------------------------------------------------------------         
| Id  | Operation                      | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |         
------------------------------------------------------------------------------------------------------------------------------         
|   0 | SELECT STATEMENT               |        |      1 |        |      5 |00:00:00.01 |      12 |       |       |          |         
|*  1 |  FILTER                        |        |      1 |        |      5 |00:00:00.01 |      12 |       |       |          |         
|*  2 |   CONNECT BY WITH FILTERING    |        |      1 |        |      5 |00:00:00.01 |      12 |  2048 |  2048 | 2048  (0)|         
|*  3 |    TABLE ACCESS FULL           | TIE    |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |         
|   4 |    NESTED LOOPS                |        |      5 |        |      4 |00:00:00.01 |       9 |       |       |          |         
|   5 |     CONNECT BY PUMP            |        |      5 |        |      5 |00:00:00.01 |       0 |       |       |          |         
|   6 |     TABLE ACCESS BY INDEX ROWID| TIE    |      5 |      1 |      4 |00:00:00.01 |       9 |       |       |          |         
|*  7 |      INDEX UNIQUE SCAN         | TIE_PK |      5 |      1 |      4 |00:00:00.01 |       5 |       |       |          |         
------------------------------------------------------------------------------------------------------------------------------         
 
PLAN_TABLE_OUTPUT                                                                                                                      
---------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (IDENTIFIED BY operation id):                                                                                    
---------------------------------------------------                                                                                    
 
   1 - filter("CODSOC"=100)                                                                                                            
   2 - access("TYPTIE"=PRIOR NULL AND "SIGTIE"=PRIOR NULL AND "CODSOC"=PRIOR NULL)                                                     
   3 - filter(("TYPTIE"='CLI' AND "SIGTIE"='M1'))                                                                                      
   7 - access("CODSOC"=PRIOR NULL AND "TYPTIE"=PRIOR NULL AND "SIGTIE"=PRIOR NULL)

Code :

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
    SELECT level, typtie, sigtie, nomtie
      FROM tie
CONNECT BY PRIOR typgrp = typtie
       AND PRIOR siggrp = sigtie
       AND PRIOR codsoc = codsoc
START WITH typtie = 'CLI'
       AND sigtie = 'M1'
       AND codsoc = 100;
 
-----------------------------------------------------------------------------------------------------------------------------          
| Id  | Operation                     | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |          
-----------------------------------------------------------------------------------------------------------------------------          
|   0 | SELECT STATEMENT              |        |      1 |        |      5 |00:00:00.01 |      11 |       |       |          |          
|*  1 |  CONNECT BY WITH FILTERING    |        |      1 |        |      5 |00:00:00.01 |      11 |  2048 |  2048 | 2048  (0)|          
|   2 |   TABLE ACCESS BY INDEX ROWID | TIE    |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |          
|*  3 |    INDEX UNIQUE SCAN          | TIE_PK |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |          
|   4 |   NESTED LOOPS                |        |      5 |        |      4 |00:00:00.01 |       9 |       |       |          |          
|   5 |    CONNECT BY PUMP            |        |      5 |        |      5 |00:00:00.01 |       0 |       |       |          |          
|   6 |    TABLE ACCESS BY INDEX ROWID| TIE    |      5 |      1 |      4 |00:00:00.01 |       9 |       |       |          |          
|*  7 |     INDEX UNIQUE SCAN         | TIE_PK |      5 |      1 |      4 |00:00:00.01 |       5 |       |       |          |          
-----------------------------------------------------------------------------------------------------------------------------          
 
PLAN_TABLE_OUTPUT                                                                                                                      
---------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (IDENTIFIED BY operation id):                                                                                    
---------------------------------------------------                                                                                    
 
   1 - access("TYPTIE"=PRIOR NULL AND "SIGTIE"=PRIOR NULL AND "CODSOC"=PRIOR NULL)                                                     
   3 - access("CODSOC"=100 AND "TYPTIE"='CLI' AND "SIGTIE"='M1')                                                                       
   7 - access("CODSOC"=PRIOR NULL AND "TYPTIE"=PRIOR NULL AND "SIGTIE"=PRIOR NULL)

[StringBuilder]

Hmmm, d'accord.

La syntaxe me laissait à penser que le connect était évalué après le where.

Merci pour ces informations, je ferai attention à l'avenir !