Marco Dieckhoff schrieb:
> Hi!
>
> I have a little database problem, and I can't figure out if there's
> a solution in Oracle:
>
>
> I have a database of articles:
>
> ident number
> type varchar
> other_info ...
> ...
>
>
> The ident is a unique identifier for that article.
>
> Now sub-articles can be made of the articles above.
> Every sub-article can only have one parent article.
>
> Sub-articles can be parent-articles for other sub-articles. It's
> stricly hierachical.
>
> I have a table
>
> ident_sub number
> ident_parent number
>
> (where ident_sub is a unique identifier)
>
> to represent this in my database.
>
>
> Now I have some cases where I'd like to get the other_info from the
> original article on the top of the tree (I call it anchestor)
>
>
> In any programming language, I'd do this recursive / by loop
>
> do
> SELECT ident_parent FROM parent_subs WHERE ident_sub LIKE $anchestor
> if ident_parent found set $ancestor = ident_parent
> while ident_parent found
>
> SELECT other_info FROM articles WHERE ident=$anchestor
>
>
> This is way too slow, esp. if I do it with some 100 articles for a
> table view to the user (e.g. in php), resulting in some 101+ database
> queries at least (main query + minimum 1 for every article...)
>
>
> Now, I wonder if I can setup a view in oracle directly to get:
>
> ident number
> anchestor number
> anchestor_other_info whatever
Oracle has hierarchical query operators, please have a look at
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/queries003.htm#i2053935
Lots of Greetings!
Volker
Received on Mon Feb 06 2006 - 07:05:38 CST
