Flattening a time-based tree structure

From: Catalin Pitis <cata.pitis_at_gmail.com>
Date: 8 Aug 2005 08:21:56 -0700
Message-ID: <1123514516.292482.78040@o13g2000cwo.googlegroups.com>

Hi all

I have to develop a job for transforming a hierarchy of users of the form:

USER

---

user_id
parent_user_id
valid_from
valid_to

(where valid_from valid_thru specifies the time interval the parent_user_id was the parent of user_id, but gives no warranty about the upper levels in the hierarchy)

into a "flat" structure like this:

USER_HIERARCHY

---

user_id
level_1_user_id
level_2_user_id
...

level_n_user_id
valid_from
valid_to

There are about 10 millions records in the user TABLE and, unfortunately, I can't use CONNECT BY clause, because at some moment, the user U1 is the parent of user U2 and at another moment, the user U2 is the parent of user U1.

The Oracle version I'm using is Oracle 9.2.0.5.

Thanks,
Catalin Received on Mon Aug 08 2005 - 10:21:56 CDT