Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!newscon02.news.prodigy.com!newscon06.news.prodigy.com!prodigy.net!border1.nntp.dca.giganews.com!local01.nntp.dca.giganews.com!nntp.rogers.com!news.rogers.com.POSTED!not-for-mail
NNTP-Posting-Date: Mon, 08 Aug 2005 15:26:45 -0500
Date: Mon, 08 Aug 2005 16:26:58 -0400
From: Mark Malakanov <markmal@rogers.com>
User-Agent: Mozilla Thunderbird 1.0.6 (Windows/20050716)
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: comp.databases.oracle.server
Subject: Re: Flattening a time-based tree structure
References: <1123514516.292482.78040@o13g2000cwo.googlegroups.com>
In-Reply-To: <1123514516.292482.78040@o13g2000cwo.googlegroups.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Message-ID: <dpOdnVzwA8OYXWrfRVn-ig@rogers.com>
Lines: 45
NNTP-Posting-Host: 69.198.112.35
X-Trace: sv3-P0Z1VHAcPxU5eKLQoxAiPKa8fTXPSlkFEE4a64oA0yRf8Kt+yTZl879lnPdKC9P8tAjFgxxvnHgAcq2!nS18PlClV5yXZThreUiMcB0Mi8T00+ysljEQNHEA0nXnprobcuFHlRRLSOJRqqbAXh5CAV+7
X-Complaints-To: abuse@rogers.com
X-DMCA-Complaints-To: abuse@rogers.com
X-Abuse-and-DMCA-Info: Please be sure to forward a copy of ALL headers
X-Abuse-and-DMCA-Info: Otherwise we will be unable to process your complaint properly
X-Postfilter: 1.3.32
Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:249130

 > unfortunately, I can't use CONNECT BY clause, because at some moment,

You can in Oracle 10g. use CONNECT BY NOCYCLE

mm

Catalin Pitis wrote:
> 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
>