Re: High volume table comparison in Oracle

joel-garry_at_home.com (Joel Garry) wrote in message news:<91884734.0302261044.3f005a09_at_posting.google.com>...
> danielroy10_at_hotmail.com (Daniel Roy) wrote in message news:<1b061893.0302260729.1d21149e_at_posting.google.com>...
> > joel-garry_at_home.com (Joel Garry) wrote in message
 <snip>
> >
> > From what I've seen, "not exists" almost always outperforms "not in".
> > This is especially true when the subquery returns many rows (as is the
>
> I should have been more specific that I meant versus the MINUS and
> similar other posts. I'd really like to see the OP post results from
> trying it all suggested ways, but am not holding my breath.
>

When you need all the data from two large tables to find the difference, MINUS is faster than almost any other SQL. Each table is read once and sorted. Then Oracle simply compares the sorted lists. NOT IN in its default form and NOT EXISTS both use an algorithm similar to nested loops. What I mean by that is for each record in one table the same operation is performed on the other table via an index (don't even attempt to do this without an index). So for tables with 10 to 15 millions records as what the OP has, you end up with that many iterations. That's a lot of I/Os. If you instructs Oracle to use hash anti-join with NOT IN, it can run a lot faster, even close to what you can get with MINUS. As for NOT EXISTS, there are situations where it can be very efficient. But in this case it won't be. Received on Thu Feb 27 2003 - 04:55:03 CET