Re: traversing all dependent data

markus.breitenbach_at_gmx.de (Markus) wrote in message news:<9bc574d3.0402240914.521e40bb_at_posting.google.com>...
> 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.

Markus, if your application needs to process dozens of child tables that have a column in T as the leading column in their PK then sorting T on this key column could be very beneficial to the overall job performance. Since the child tables will be retrieved in key order the number of physical IO to process the child is greatly reduced.

There is no need in step 2 to re-retrieve data from T since you should already have it in the driving cursor. You would just grab the child data.

If there are only a few child tables: U1, U2, U3 you may be better off to combine the queries into a single join.

A large amount of data is a relative term. Exactly how many rows are expected in the driver and how many child tables are there?

You should examine each child query to ensure that each has an optimal plan.

HTH -- Mark D Powell -- Received on Wed Feb 25 2004 - 07:26:07 CST