Re: Fetching from a hierarchical data structure?

From: FT <ft_at_ep-soft.dk>
Date: Tue, 10 Apr 2001 10:16:53 +0100
Message-ID: <hcAA6.127$X22.1787@news.get2net.dk>

Hi

An alternative approach would be to create a "Key" column. This column holds a key string made up of Subkey from each level in the hierarchy. IE:

       A
      /   \
    1      2

   / \
  3

"A" SubKey = 00001
"1" SubKey = 00001
"2" SubKey = 00002
"3" SubKey = 00001

Now when i walk up the hirarchy the complete key for "3" will be: 000010000100001
This value is put into the Key column of this record. Now to find all items below "1" I can simply say:
Select * From Table Where Key LIKE '000010001*'; The tricky part, is of cource to generate the subkeys and the keys. Hope this helps. (If i misunderstood your question, please bear with me, english is not my language).

Best Regards
FT

"Christoph Pfrommer" <Christoph.Pfrommer_at_oracle.com> wrote in message news:3AD2267E.4219C2EA_at_oracle.com...
>
>
> Maury Markowitz wrote:
>
> > "Christoph Pfrommer" <Christoph.Pfrommer_at_oracle.com> wrote in message
> > news:3AD14E79.7CD7461B_at_oracle.com...
> > > A few words on typical DW architecture: The well known star schema
 consists of a
> > > large fact table (as in your application) and some lookup tables, one
 for
 each
> > > (hierarchical) dimension. Since lookup tables are having a different
> > > representation of hierarchies - one column for each level - you are
 coding
 your
> > > SQL with some joins
> >
> > Now this certainly sounds like a good idea in performance terms, but
> > doesn't this limit the depth of the hierarchy?
> >
> > Maury
>
> Maury,
> that's true. (Even if you add one or two extra levels your argument
 remains
> valid).
>
> However, data warehouses are mostly dealing with static or slowly changing
> dimensions and hierarchies. ('Cause people want to analyze their data with
> well-known structures.) If you talk to the right people, you should know
 of
> those changes in advance.
>
> And if you want to go to extremes (in terms of coding): You can write load
> procedures in Oracle PL/SQL, that add new columns dynamically, e.g.
 statements
> like:
> execute immediate ('alter table my_table add column level' || i);
> where i stands for the depth of your tree.
>
> Hope this helps.
> Christoph.
>
Received on Tue Apr 10 2001 - 04:16:53 CDT