Connect by prior repeating nodes
422652Jul 26 2010 — edited Oct 28 2010I 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