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.

Your question is not all that clear. Here is my interpretation

1. you are joining two tables
2. you have a large result set
3. you need an order to those results.

My answer:
1. do not hint for nested loop join. analyze your tables. good chance you will use a hash join or a sort merge. Let oracle decide and see how it goes from there. In 8i and 9i the CBO is very good.

2. How else do you get an ordered result without an order by? The key is to order by as few records as possible.

Post your query and I'll look at it. Its diffuclt to entirely follow what you are doing. If I can see the sql I can work from there. Received on Wed Feb 25 2004 - 07:16:16 CST