Home » SQL & PL/SQL » SQL & PL/SQL » Connect By Prior and nocycles ... (Oracle 10g)
Connect By Prior and nocycles ... [message #352828] Thu, 09 October 2008 16:24 Go to next message
chlamy
Messages: 2
Registered: October 2008
Junior Member
Hi everyone,

I was playing with Oracles recursive capabilities Connect By and discovered, that cycle-free is not quite true. I have the following data (simple example):
create table edge (from_node integer, to_node integer);

INSERT INTO edge VALUES ('1', '2');
INSERT INTO edge VALUES ('1', '4');
INSERT INTO edge VALUES ('4', '9');
INSERT INTO edge VALUES ('9', '14');
INSERT INTO edge VALUES ('14', '1');

I use the following query:
SELECT 1 as start_node, to_node AS end_node, level AS path_length 
FROM edge 
start with from_node=1 
connect by nocycle prior to_node = from_node;


Ok, I would expect the following data to show up (which they do)

 start_node  | to_node  | path_length
--------------------------------------
           1 |        4 |          1 
           1 |        9 |          2
           1 |       14 |          3
           1 |        2 |          1


But then I also get

 start_node  | to_node  | path_length
--------------------------------------
           1 |        1 |          4
           1 |        2 |          5 


Which clearly is a cycle as it runs over 1 again (1->4->9->14->1->2).

Has anyone experience with that - or can anyone tell me how to eliminate those last two lines in the result? Thanks.
Re: Connect By Prior and nocycles ... [message #352863 is a reply to message #352828] Thu, 09 October 2008 23:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for providing the full test case and aligning the result columns.
Reference is: Hierarchical Queries section of SQL Reference:
Quote:
The NOCYCLE parameter instructs Oracle Database to return rows from a query even if a CONNECT BY LOOP exists in the data.

Oracle chooses to stop the loop the level after the loop is detected. Why this level and not when the loop is detected? I don't know. Is is documented? Nowhere I'm aware of (but you can see it in the documentation examples)
If you rewrite your query in the following way, you will understand how to stop it when a cycle is detected:
SQL> SELECT connect_by_root from_node as start_node, to_node AS end_node, level AS path_length,
  2         connect_by_iscycle as cycle
  3  FROM edge 
  4  start with from_node=1 
  5  connect by nocycle prior to_node = from_node;
START_NODE   END_NODE PATH_LENGTH      CYCLE
---------- ---------- ----------- ----------
         1          2           1          0
         1          4           1          0
         1          9           2          0
         1         14           3          0
         1          1           4          1
         1          2           5          0

6 rows selected.

Regards
Michel
Re: Connect By Prior and nocycles ... [message #353033 is a reply to message #352863] Fri, 10 October 2008 12:24 Go to previous messageGo to next message
chlamy
Messages: 2
Registered: October 2008
Junior Member
Hi Michel,

thanks for the answer. Well, I figured out that I could get rid of the 1 that causes the cycle by simply using the following statement:

SELECT 1 as start_node, to_node AS end_node, level AS path_length 
FROM edge 
WHERE to_node <> 1
start with from_node=1 
connect by nocycle prior to_node = from_node;


But your example as well as mine still leaves me with the additional 2 at path_length 5 (and much more, as my example is just a simple one).

So I would need a preprocessing step in which to eliminate all rows of the output that are

  • behind a 1 in cycle
  • and before the following 1 in cycle
  • have a higher path_length than the tuple with the last 1 in cycle


But for that I have to assume that Oracle will always provide the result in the order of the traversal. It generally is, but I don't think Oracle would guarantee it Sad .
I think, programming a function myself is the better way to do it unfortunately.

Anyway, thanks for the help,
chlamy

[Updated on: Fri, 10 October 2008 12:27]

Report message to a moderator

Re: Connect By Prior and nocycles ... [message #353070 is a reply to message #353033] Fri, 10 October 2008 21:25 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Starting from FROM_NODE = 1, you get the following paths:

Path 1: (1,4)->(4,9)->(9,14)->(14,1)->(1,2)
Path 2: (1,2)

That's six rows in total, no cycles.

Ross Leishman
Re: Connect By Prior and nocycles ... [message #353078 is a reply to message #353070] Sat, 11 October 2008 00:56 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Of course, silly me. Confused
I didn't see there were 2 paths. Embarassed

Regards
Michel
Previous Topic: SQL Mass update - dumb question
Next Topic: query
Goto Forum:
  


Current Time: Sat Dec 03 05:57:03 CST 2016

Total time taken to generate the page: 0.18419 seconds