Re: Fetching from a hierarchical data structure?

I would just use the hirarchy tables that are in Oracle 8i. If you need an example let me know.

FT wrote:

> 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 - 06:35:38 CDT