CODEWITHSUNDEEP
sqloracle-databaseconnect-by
vorko
vorko

Reputation: 13

CONNECT BY never ending

I've got a little trouble with a recursion using Oracle START WITH / CONNECT BY.

Given a table id_string, id, ordre, prec with id_string being a concatenation of id_ordre. and prec the link between two elements having the same id (1_1,1,1,2 is linked to 1_1,1,2,null). The table contains 500 rows with 196 unique id_string.

I want to retrieve all the linked rows from this table given starting with id_string equals id|| '_' || ordre and linked through ordre and prec, I use the following request

SELECT tda.*
FROM T_DRU_ALL tda
START WITH tda.ID|| '_' || tda.ORDRE = tda.id_string
CONNECT BY NOCYCLE PRIOR tda.ORDRE = tda.PREC and tda.id_string = tda.id_string
order by 1,2,3

My problem is very simple, the select has been running for one hour and is still running :'( I'm sure there is something wrong in my code but I don't know where.

data file https://pastebin.com/R66T3hAu

Upvotes: 1

Views: 294

Answers (1)

Alex Poole
Alex Poole

Reputation: 191235

You're missing a PRIOR, which is probably where your cycles were coming from and why you added NOCYCLE; without it, tda.id_string = tda.id_string is always true.

So at first glance you could just change:

CONNECT BY NOCYCLE PRIOR tda.ORDRE = tda.PREC and PRIOR tda.id_string = tda.id_string

or remove the NOCYCLE:

CONNECT BY PRIOR tda.ORDRE = tda.PREC and PRIOR tda.id_string = tda.id_string

But, that still never ends; curiously just rearranging the terms to what appears to be the same logical outcome works (but to me scans better anyway):

or rearrange the terms (which scans better to me, but shouldn't have any practical effect - not sure why I thought I saw one earlier!):

CONNECT BY tda.id_string = PRIOR tda.id_string AND tda.PREC = PRIOR tda.ORDRE

which now works OK:

ID_STRING         ID      ORDRE       PREC
--------- ---------- ---------- ----------
7682_2          7682          2
7682_2          7682         13          2
7682_2          7682         14         13
7690_6          7690          6
7690_7          7690          7
7693_2          7693          2
7693_2          7693          9          2
7693_2          7693         10          9
...

371 rows selected.

Personally I'd probably make the starting condition

START WITH tda.PREC IS NULL

rather than concatenating the values - it's the same result with the data you have, anyway.

db<>fiddle (which you could supply in future with sample data if there's that much of it; or provide a much smaller sample that shows the issue...)

Upvotes: 3

Related Questions