Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Question on Tree Walks

Re: Question on Tree Walks

From: timkarnold <timkarnold_at_home.com>
Date: Tue, 30 Oct 2001 13:12:13 GMT
Message-ID: <NGxD7.247961$Xz1.51115296@news1.rdc1.md.home.com> 





  From   MetaLink



Bookmark Fixed font  Go to End



Doc ID:  Note:29704.1


Subject:  SQL: EG - Tree Walking Examples.
Type:  SCRIPT


Status:  REVIEWED


 Content Type:  TEXT/PLAIN


Creation Date:  08-NOV-1996


Last Revision Date:  27-JUN-2000




Title: Example script to demonstrate tree walking



Disclaimer:


This script is provided for educational purposes only. It is NOT
supported by Oracle Support Services.  The script has been
tested and appears to work as intended.  However, you should always
test any script before relying on it.



PROOFREAD THIS SCRIPT PRIOR TO USING IT!  Due to differences in the
way text editors, email packages and operating systems handle text
formatting (spaces, tabs and carriage returns), this script may not
be in an executable state when you first receive it.  Check over the
script to ensure that errors of this type are corrected.



Abstract:


This script gives an example of a tree walk.



Requirements:


SQL*Plus session.



Version Testing:


This example was tested on 8.0.5.



Script:


REM Tree Walking Example

REM ~~~~~~~~~~~~~~~~~~~~




REM



REM   Build a tree of data to play with


REM    PID is the parent ID


REM ID is the current ID

REM      T describes the node position
REM      M is for ordering within levels if required.


REM



drop table tmp_tree ;


create table tmp_tree ( pid number, id number, t char(30), m char(5) );

insert into tmp_tree values( 0, 1, 'Top of the tree (0,1)', 'TOP'  );
insert into tmp_tree values( 1, 2, 'Node 1 (1,2)', 'P3'  );
insert into tmp_tree values( 1, 3, 'Node 2 (1,3)', 'P2'  );
insert into tmp_tree values( 1, 4, 'Node 3 (1,4)', 'P1'  );
insert into tmp_tree values( 2, 5, 'Node 1-1 (2,5)', 'P3'  );
insert into tmp_tree values( 2, 6, 'Leaf 1-2 (2,6)', 'P2'  );
insert into tmp_tree values( 2, 7, 'Leaf 1-3 (2,7)', 'P1'  );
insert into tmp_tree values( 3, 8, 'Leaf 2-1 (3,8)', 'P3'  );
insert into tmp_tree values( 3, 9, 'Leaf 2-2 (3,9)', 'P2'  );
insert into tmp_tree values( 3, 10, 'Leaf 2-3 (3,10)', 'P1'  );
insert into tmp_tree values( 4, 11, 'Leaf 3-1 (4,11)', 'P3'  );
insert into tmp_tree values( 4, 12, 'Leaf 3-2 (4,12)', 'P2'  );
insert into tmp_tree values( 4, 13, 'Leaf 3-3 (4,13)', 'P1'  );
insert into tmp_tree values( 5, 14, 'Node 1-1-1 (5,14)', 'P2'  );
insert into tmp_tree values( 5, 15, 'Leaf 1-1-2 (5,15)', 'P1'  );
insert into tmp_tree values( 14, 16, 'Leaf 1-1-1-1 (14,16)', 'P1'  );


commit;


create index i_tree on tmp_tree ( m );


REM



REM



REM



spool treewalk


set echo on


REM



REM *** All nodes top down


REM



select pid, id, t "All nodes top down"


 from tmp_tree


connect by prior id=pid


start with id=1;


REM



REM *** All nodes top down ORDERING within each level
REM     See [PR:1009345.6] for full details
REM



select pid, id, t "All nodes top down", m
 from tmp_tree


connect by prior id=pid


 and m>=' '        /* Force ordering */


start with id=1;


REM



REM



REM *** Start at bottom node and walk UP the tree
REM



select pid, id, t "Bottom node up"


from tmp_tree


connect by prior pid=id


start with id=16;


REM



REM *** Set some Values to check on a walk DOWN the tree
REM



update tmp_tree set m='XXX' where id in (1, 10, 14);
REM



REM *** Top down looking for rows matching particular criteria
REM



select pid, id, t "Top down Xs", m


from tmp_tree


where m='XXX'


connect by prior id=pid


start with id=1;


REM



REM *** Bottom up looking for rows matching particular criteria
REM



select pid, id, t "Bottom Up Xs", m


from tmp_tree


where m='XXX'


connect by prior pid=id


start with id=16;


REM



REM *** From a node find all its children
REM



select pid, id, t "Leaf 1 + only"


from tmp_tree


connect by prior id=pid


start with id=2;


REM



REM *** Top down X's


REM



select pid, id, t "Top down Xs NO START WITH", m
from tmp_tree


where m='XXX'


connect by prior id=pid;


REM



REM *** Find all leaf nodes - a messy way
REM



select pid, id, level , t "Leaf Nodes 1"
from tmp_tree m


where 1 = (select count(*) from tmp_tree d

                   connect by prior d.id=d.pid
                    start with d.id=m.id )


connect by prior id=pid


start with id=1;


REM



REM *** Find all leaf nodes - tidier method
REM



select pid, id, level , t "Leaf Nodes 2"
from tmp_tree m


where not exists (select 1 from tmp_tree d

                   where d.id!=m.id
                   connect by prior d.id=d.pid
                    start with d.id=m.id )


connect by prior id=pid


start with id=1;


REM



REM  Tree Walk with Join ??


REM   Use this in 7.2 onwards. Prior to 7.2 use a VIEW for the query in the
REM   FROM clause


REM



select v.org_chart, v.empno, v.mgr, v.job, d.dname
 from


   (


    select

        lpad(' ',2*(level-1))|| ename org_chart,
        e.empno, e.mgr, e.job, e.deptno
        from  emp e
        connect by prior empno = mgr
        start with mgr is null




   ) v,


   dept d


 where d.deptno = v.deptno;


REM



REM Example output


REM



REM ORG_CHART          EMPNO      MGR JOB       DNAME

REM --------------- -------- -------- --------- --------------


REM KING                7839          PRESIDENT ACCOUNTING
REM   JONES             7566     7839 MANAGER   RESEARCH
REM     FORD            7902     7566 ANALYST   RESEARCH
REM       SMITH         7369     7902 CLERK     RESEARCH
REM     SCOTT           7788     7566 ANALYST   RESEARCH
REM       ADAMS         7876     7788 CLERK     RESEARCH
REM   BLAKE             7698     7839 MANAGER   SALES


REM


REM ** Tree walk joined on > 1 column


REM



REM Example only - needs an mgr_name column adding to EMP and populating
REM



SELECT level, ename, empno, mgr, mgr_name FROM my_emp
 connect by prior empno=mgr


 and prior ename=mgr_name


 start with mgr is null


 order by level


;


spool off


exit


.










 Copyright (c) 1995,2000 Oracle Corporation. All Rights Reserved. Legal
Notices and Terms of Use.




"newbie" <noone_at_nowhere.com> wrote in message
news:Z6mD7.7303$c5.87896_at_newsfeeds.bigpond.com...


> Thanks for your thoughts... although now I can't find anything in the

> manuals about doodling with soot... ;)


>




> Anyhoo... I'm trying to find out a bit more than that... although I must

> confess I can't explain the problem any better.


>




> I'm about to sit an 8i OCP exam which I know has some questions on Tree

> Walks (several of them), but there doesn't appears to be anything even

> remotely useful to explain their behavior, significance, and implications.

> Especially in the context of  what I could learn in preparation for an

exam.

>




> If anyone can fathom what i'm gibbering about, and has even half an idea,

> don't be shy... I would value your opinions.


>




> Thanks


>




> P


>




> Nuno Souto <nsouto_at_optushome.com.au.nospam> wrote in message

> news:3bdd39dd.5076013_at_news...

> > In a valiant and sublime effort,newbie

> > frowned, dipped a thumb in soot and doodled:

> >

> > >Can anyone please explain to me what a tree walk is?

> > >

> > >I haven't found anything useful in the limited documentation i have.

> > >

> >

> > If it is what I think, then it's the use of CONNECT BY to traverse a

> > hierarchical table (recursive relationship).  If you need more info, I

> > suggest you stop the x-post and re-post to

> > comp.databases.oracle.server and someone for sure will pick it up.

> >

> >

> > Cheers

> > Nuno Souto

> > nsouto_at_optushome.com.au.nospam



>


>
Received on Tue Oct 30 2001 - 07:12:13 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US