Re: Traversing, while keeping a constant

From: VC <boston103_at_hotmail.com>
Date: Fri, 14 Nov 2003 01:06:35 GMT
Message-ID: <vUVsb.143547$9E1.725740_at_attbi_s52>

Hello Ray,

Apparently you want to get the transitive closure over your tree.

Given :

create table t1(PARENT INT, CHILD INT);

insert into t1 values(null, 1);
insert into t1 values(1, 2);
insert into t1 values(1, 3);
insert into t1 values(2, 4);
insert into t1 values(2, 5);
insert into t1 values(3, 6);
insert into t1 values(3, 7);
insert into t1 values(5, 8);

In Oracle 9i., one way would be:

select

    substr(path,2,instr(path,'/',1,2)-2) parent,     substr(path, instr(path,'/',-1,1)+1, length(path)-instr(path,'/',-1,1)) child,
 distance
from (select sys_connect_by_path(child,'/') path, level-1 distance

      from t1
        connect by prior child=parent)

where instr(path,'/',1,2)!= 0

... and another:

select p.child parent ,

       c.child child,
       level-1 distance

from t1 p, t1 c
where level > 1
  connect by prior c.child = c.parent and prior p.child=p.child   start with p.child= c.child

PARENT CHILD DISTANCE

2       4       1
2       5       1
2       8       2
3       6       1
3       7       1
5       8       1
1       2       1
1       4       2
1       5       2
1       8       3
1       3       1
1       6       2
1       7       2

Both queries are not very efficient for large trees. Which one is worse is left as an exercise for the reader ;)

In Oracle 8i, one has to write a stored procedure to perform BFS or DFS. The stored procedure solution will be more efficient in 9i too since only one tree traversal is needed.

Rgds.

"Ray" <rbujarski_at_hotmail.com> wrote in message news:cb2954f2.0311131242.3b3275fd_at_posting.google.com...
> I have a real puzzle, I figured someone on here could help. I have a
> table that tracks all parents and children. I would like to set
> iterate over all entities where I set a variable to equal a root
> parent and return all relations disregarding why the relation exists.
> Essentially here is what I would like to do however, this won't work
> for obvious reasons:
>
> select parent, (select child from table
> start with parent = (select parent from table)
> connect by parent = prior child)
> from table
>
> so instead of
> 1,2
> 1,3
> 3,4
> 3,5
>
> I need my result to be used in a view where
> 1,2
> 1,3
> 1,4
> 1,5
> 3,4
> 3,5
>
> Anyone know of a way to do this? I know that I can use a cursor and
> keep track of the root parent while using a cursor to track all
> relations for the root parent and place that into a function, but this
> will be constantly growing and I can't process this logic everytime
> there is an update.
> Thanks for any insight...
> Ray
Received on Fri Nov 14 2003 - 02:06:35 CET