Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: traversing all dependent data

Re: traversing all dependent data

From: Markus <markus.breitenbach_at_gmx.de>
Date: 24 Feb 2004 23:42:05 -0800
Message-ID: <9bc574d3.0402242342.76b94721@posting.google.com>


Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1077660558.330078_at_yasure>...
> 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.

I'm talking about > 10^7 records but in the end it does not matter. I'm trying to put it a bit simpler:
- there is a table T
- there is a table X with a n:1 relationship to T (one t relates to many x's)
- there is a table Y with a n:1 relationship to T Now I'd like to join T with X and (with a different SELECT) T with Y. Is there a way to ensure that the elements of T appear in the same order in both result sets without ordering them?

Markus. Received on Wed Feb 25 2004 - 01:42:05 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US