Skip to Main Content

Connect by prior repeating nodes

422652Jul 26 2010 — edited Oct 28 2010
I have a hierarchical structure (a directed acyclic graph with a root node) and I want to list only the leaf nodes for a root node. For example, say I have the following graph

A -> B, A->C, A->D
B->C,
C->D,
D->E,
D->F

I want to get the results as A ->E, A->F


Queries to create the above graph
create table test_hierarchy (parent varchar2(10), child varchar2(10));
insert into test_hierarchy (parent, child) values ('a','b');
insert into test_hierarchy (parent, child) values ('a','c');
insert into test_hierarchy (parent, child) values ('a','d');
insert into test_hierarchy (parent, child) values ('b','c');
insert into test_hierarchy (parent, child) values ('b','d');
insert into test_hierarchy (parent, child) values ('c','d');
insert into test_hierarchy (parent, child) values ('d','e');
insert into test_hierarchy (parent, child) values ('d','f');

To achieve the desired output i have written the following query
Select connect_by_root(parent), child from test_hierarchy where connect_by_isleaf = 1
start with parent = 'a'
connect by prior child = parent

But I get the result set with A->E and A->F repeated 6 times from all the possible paths.

This is making the whole operation very slow in the production env. Adding distinct is making it even slower.

Is there a way for me to specify to the db to not traversed the already traversed paths.
Any help is highly appreciated
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked due to inactivity on Sep 3 2010
Added on Jul 26 2010
13 comments
16,202 views