Re: traversing all dependent data

Markus wrote:

> I have a table T with several 1:n relationships to tables U1, U2,...
> Now I'd like to process all records in T (plus dependent data)
> satisfiying some condition C.
> As there is a hugh amount of data my idea to handle this bulk data
> was:
>
> 1. SELECT * FROM T WHERE C
> 2. SELECT * FROM T WHERE C EQUI-JOIN U (for each of the U's)
> 3. nested loop:
> for every selected record in T
> read dependent data from the joined result sets.
>
> Now I have to ensure that the order in which elements of T appear in
> the various result sets is the same, but an ORDER BY on some key of T
> is very slow. Some natural (!?) BUT CONSISTENT ordering would be
> enough.
> How can this be done most efficiently with Oracle? Any hints?
>
> Things get even more complicated as the tables U may have 1:n
> relationships with tables V...
>
> Cheers,
> Markus.

My guess is that you have a lot of experience with some database other than Oracle. First off ... what is a huge amount of data to you. It may or may not be huge to Oracle or to us.

But from what little you posted, and it is very little, I'd say your approach will be slow and non-scalable. Post more information and look up bulk collection and FORALL. The ORDER BY makes no sense to me at all.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)