Re: Linked Lists in Oracle Tables

"Ana C. Dent" <anacedent_at_hotmail.com> wrote in message news:<Laola.2498$%X5.451_at_fed1read03>...
> KevY wrote:
> > If I'm storing a hierrachy in an Oracle table, is there any benefit in
> > making the records "doubly-linked" (i.e. each having a "parent" and a
> > "child" reference) rather than simply having a "singly-linked" list
> > (i.e. just a "parent" reference)?
> >
> > In order to find all the offspring of a record in a singly-linked
> > system, I have (or the system has) to examine every record to see if
> > it has me as a "parent". Then repeat this step for all my 1st
> > generation children, etc., until I have a complete list.
> >
> > In the doubly-linked version, although I can quickly identify the
> > children (have a foreign key to them) I have (or the system has) still
> > to examine every record to see if it is the right one. Then repeat for
> > all the 1sr generation children found ...
> >
> > On the face of it, the doubly-linked version doesn't seem to save much
> > (although it is able to stop searching once it has found the matching
> > record).
> >
> > Is that really as good as it gets? Doesn't Oracle efficientize this
> > somehow? Is there no real concept of "pointers" that can take you
> > directly to another referenced record, without having to check all
> > records against the key to find the match?
>
> Yes, they are called indexes.

To add to what Ana said. Look at the Oracle SQL Manual example connect by clause query. You would want an index on empno and mgr in the example.

HTH -- Mark D Powell -- Received on Fri Apr 11 2003 - 18:49:14 CEST