Re: Linked Lists in Oracle Tables
From: Ana C. Dent <anacedent_at_hotmail.com>
Date: Sat, 12 Apr 2003 06:23:19 -0700
Message-ID: <9rUla.5491$%X5.995_at_fed1read03>
>>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?
Date: Sat, 12 Apr 2003 06:23:19 -0700
Message-ID: <9rUla.5491$%X5.995_at_fed1read03>
STOP top posting, see below
> > > > google_at_kevyale.com (KevY) wrote in message news:<2c436769.0304101219.5d12c677_at_posting.google.com>... >
>>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?
Jaan Marck wrote:
> Would it be possible to use type ROWID to represent the pointers here?
Possible? Yes.
Recommended? NEVER!
ROWIDs should NEVER be stored as a column in a table. If ROWID is not stored than you are back to the problem of how to determine it efficiently (other than by an index)? Received on Sat Apr 12 2003 - 15:23:19 CEST