recursive view

From: Marco Dieckhoff <dieck_at_gmx.de>
Date: Mon, 6 Feb 2006 13:54:50 +0100
Message-ID: <qhtjb3-la8.ln1@hamlet.frbr.etc.tu-bs.de>

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

-- 
Marco Dieckhoff
icq# 22243433
GPG Key 0x1A6C95BA -- http://www.frankonia-brunonia.de/keys

Received on Mon Feb 06 2006 - 06:54:50 CST