Overblog
Suivre ce blog
Editer l'article Administration Créer mon blog
10 décembre 2013 2 10 /12 /décembre /2013 16:38

Dans l'exemple type des row generators (par exemple ici), on utilise parfois une construction étrange qui est "CONNECT BY level <= N".


C'est certes étrange et abusé, mais ça se conçoit : "connecte moi avec n'importe quoi et récursivement, mais pas plus loin que le 3ème étage".

 

Cela dit, il y a des moments où on veut générer N lignes pour chaque lignes appartenant à une sélection (de plus d'une ligne cette fois).

 

CREATE TABLE test_rowgen AS                

SELECT 'pacmann' c, 3 n FROM DUAL UNION ALL

SELECT 'robert' c, 2 n FROM DUAL UNION ALL 

SELECT 'squelettor' c, 1 n FROM DUAL       

 

Par exemple ici, on voudrait générer 3 lignes pacmann, deux lignes robert, et 1 ligne squelettor.

 

Et là, si on tente : 

 

SELECT *             

FROM test_rowgen     

CONNECT BY level <= n

 

... on obtient toutes les combinaisons possibles récursivement, tant que le niveau est inférieur à "n", ce qui n'est pas bon. La différence avec le générateur à partir de dual, c'est que la ligne de dual ne peut se connecter qu'avec elle-même, alors que là, on a une tripotée d'autres arbres distincts à générer combinatoirement.

 

Si, pour empêcher de connecter n'importe quoi, on tente de lui préciser qu'il doit se connecter qu'avec lui-même... 

 

SELECT c, n          

FROM test_rowgen     

CONNECT BY level <= n

    AND c = prior c  

 

... on obtient : 

ORA-01436: CONNECT BY loop in user data

 

La boucle est détectée au niveau de l'analyse de la requête, et non au niveau de l'exécution (pour preuve la première requête, une autre preuve un peu plus loin dans cet article)

 

Si on ajoute NOCYCLE, on ne va pas bien loin : 

 

SELECT c, n                  

FROM test_rowgen             

CONNECT BY NOCYCLE level <= n

    AND c = prior c          

 

C                   N

---------- ----------

pacmann             3

robert              2

squelettor          1

 

Bien entendu, NOCYCLE évite l'erreur, mais pour autant, lorsque le cycle est détecté, aucune connexion n'est tentée pour les chemins suspectés d'induire des cycles.

 

Pour cela, des petits génies (dont Laurent Schneider, mais lui seul pourra nous dire s'il l'avait repompé ailleurs à l'époque :)) ont hacké le système et ont tenté : 

 

SELECT c, n                         

FROM test_rowgen                    

CONNECT BY level <= n               

    AND c = prior c                 

    AND prior sys_guid() IS NOT NULL

 

C                   N

---------- ----------

pacmann             3

pacmann             3

pacmann             3

robert              2

robert              2

squelettor          1

 

Alors oui, ça marche... mais qu'est ce que ça veut dire ?

 

Testons de réécrire un peu la requête : 

WITH u AS (                  

    SELECT c, n, sys_guid() t

    FROM test_rowgen         

    )                        

SELECT *                     

FROM  u                      

CONNECT BY level <= n        

    AND c = prior c          

    AND prior t IS NOT NULL  

 

C                   N T                               

---------- ---------- --------------------------------

pacmann             3 ED2ED377E71B437AE0436203650A0428

pacmann             3 ED2ED377E71D437AE0436203650A0428

pacmann             3 ED2ED377E71F437AE0436203650A0428

robert              2 ED2ED377E721437AE0436203650A0428

robert              2 ED2ED377E723437AE0436203650A0428

squelettor          1 ED2ED377E725437AE0436203650A0428

 

Ca marche !

Mais si on force la matérialisation : 

 

WITH u AS (                                  

    SELECT /*+materialize*/c, n, sys_guid() t

    FROM test_rowgen                         

    )                                        

SELECT *                                     

FROM  u                                      

CONNECT BY level <= n                        

    AND c = prior c                          

    AND prior t IS NOT NULL                  

 

... Paf !

ORA-01436: CONNECT BY loop in user data

 

Et moi, les résultats qui varient en fonction du plan d'exécution... hum. (Je dis ça, mais faudrait encore vérifier dans quel ordre les choses se passent...)

 

Par ailleurs la condition "prior sys_guid() is not null" n'empêche en rien les loop...

 

SELECT c, n                                           

FROM test_rowgen                                 

CONNECT BY prior sys_guid() IS NOT NULL

 

... boucle infinie tuée à la main après plusieurs millions de lignes fetched.

 

Donc voilà, cette astuce est démoniaque et plutôt dangereuse.

 

Je propose donc une méthode encore plus nulle, parce que je le vaux bien : 

 

SELECT c,n                                                                     

FROM (                                                                         

    SELECT c, n, coalesce(sum(n-1)                                             

    over(order by c desc rows between unbounded preceding and 1 preceding),0) p

    FROM test_rowgen                                                           

    )                                                                          

CONNECT BY rownum <= n + p                                                     

 

C                   N

---------- ----------

squelettor          1

robert              2

pacmann             3

pacmann             3

robert              2

pacmann             3

 

Pourquoi est-ce que ça marche ?

Parce qu'au moment de connecter, on se base sur rownum pour estimer le nombre de connexion qui ont été faites sur les lignes précédentes, et en déduire combien il faut en faire la ligne présente.


Pourquoi est-ce que c'est lamentable et à ne surtout pas refaire ?

Parce que d'une part, je n'ai pas la moindre idée de ce qu'est sensé être l'exécution de CONNECT BY, et (surtout ?) parce qu'on ne programme pas en fonction de l'algorithme interne d'Oracle, qui peut très bien changer d'un patch à l'autre (l'introduction du hash group by en étant certainement le plus bel exemple)

 

Comme dans tout article nihiliste, il faut finir par la clause MODEL (qui marche, n'est pas officiellement prohibée, mais vous mettra à dos vos collègues et votre hiérarchie) : 

 

SELECT c, n                                     

FROM test_rowgen                                

MODEL                                           

PARTITION BY (c c0)                             

DIMENSION BY (1 i)                              

MEASURES (c,n)                                  

RULES (                                         

    c[FOR i FROM 1 TO n[1] INCREMENT 1] = c[1]  

    , n[FOR i FROM 1 TO n[1] INCREMENT 1] = n[1]

    )                                           

 

C                   N

---------- ----------

robert              2

squelettor          1

robert              2

pacmann             3

pacmann             3

pacmann             3

 

C'est bien la première fois que je trouve MODEL moins obscure qu'une quelconque autre méthode...

Partager cet article

Published by Pacman - dans SQL
commenter cet article

commentaires