Re: Storing tree structures

You can add a validity time range and a serial number on all your rows. For instance, a "beg" column for the timestamp of the creation and the an "end" column for the timestamp of the deletion or the modification.

v734>create table t (curr number, prev number, serial# number,

     beg number, end number);

Table created.

v734>insert into t values (1,null,0,0,10);

1 row created.

v734>insert into t values (2,1,0,0,5);

1 row created.

v734>insert into t values (3,1,0,3,10);

1 row created.

v734>select * from t start with prev is null connect by prev=prior curr;

      CURR PREV SERIAL# BEG END ---------- ---------- ---------- ---------- ----------

         1                     0          0         10
         2          1          0          0          5
         3          1          0          3         10

3 rows selected.

v734>select * from t
  2 where 2 between beg and end
  3 start with prev is null connect by prev=prior curr;

      CURR PREV SERIAL# BEG END ---------- ---------- ---------- ---------- ----------

         1                     0          0         10
         2          1          0          0          5

2 rows selected.

v734>select * from t
  2 where 7 between beg and end
  3 start with prev is null connect by prev=prior curr;

      CURR PREV SERIAL# BEG END ---------- ---------- ---------- ---------- ----------

         1                     0          0         10
         3          1          0          3         10

2 rows selected.

At timestamp 7, we move node 2 from sub-tree 1 to sub-tree 3, so we create a new node 2 with beg time 7

v734>insert into t values (2,1,1,7,10);

1 row created.

v734>select * from t
  2 where 7 between beg and end
  3 start with prev is null connect by prev=prior curr;

      CURR PREV SERIAL# BEG END ---------- ---------- ---------- ---------- ----------

         1                     0          0         10
         3          1          0          3         10
         2          1          1          7         10

3 rows selected.

etc...

Vikas Agnihotri a écrit dans le message <7rts4q$h98$1_at_nnrp1.deja.com>...
>What is the best way to store tree structures in Oracle?
>
>A non-cyclical, single-parent tree. The kind that Oracle's CONNECT BY
>clause navigates.
>
>In its simplest form, I could simply have one row per node with the
>current node and its parent node. The root node will have a NULL
>parent node.
>
>create table t1 (curr number, prev number);
>insert into t1 values (1,null);
>insert into t1 values (2,1);
>insert into t1 values (3,1);
>
>select .... from t1
>start with prev is null connect by prev=prior curr
>
>would suffice for navigating this tree.
>
>Now, say a node is moved from one place in the tree to another, a node
>is deleted, etc. I want to capture all these changes and not simply
>store the most current tree. Store different versions of the tree.
>
>i.e. How can I *re-create* the tree as an "as of" date?
>
>Also, if I have a CONNECT BY in a query, I cannot have a JOIN in the
>same query! Seems like a bummer!
>
>Can the new features in Oracle 8/8i help here? Nested tables, object
>types, collection unnesting, etc? Or will good ol' 7.3.4 suffice?
>
>I hope I am expressing myself clearly enough for someone to help me.
>
>Thanks,
>Vikas
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.
Received on Tue Sep 21 1999 - 05:16:06 CDT