Re: The best solution to find differences between multiple rowset

On Feb 29, 3:26 pm, Mauro Pagano <mauro.pag..._at_gmail.com> wrote:
> Hi all,
> in the last days I'm thinking about the best way to find rows present
> in a set not present in an other set.
> Suppose that both sets have a pk, is always the outer join filtering
> only null values on one side the most *efficient* solution?
> The other solutions I propose are to use "not in / not exists" (I know
> that they're not the same) or minus.
> In my experience I had usally achieved better performance using outer
> join, expecially on big volumes.
> If you want I can provide example, execution plan, etc but my question
> is not about specific case... it's just to share experiences,
> opinions, solutions, etc
> Thanks a lot for your attention
> Regards
> Mauro

From my experience, an outer join usually provides a significant performance improvement over the MINUS syntax. NOT IN syntax is usually slower than an outer join as well, unless Oracle transforms the NOT IN into a more efficient form - the automatic transformations that are available are version dependent.

If I were trying to determine which of the methods worked the fastest, I might set up a small test case, maybe with 100,000 to 500,000 rows in each of two tables, and then run a DBMS Xplan with actual time values displayed. Run each query method twice, and compare the timings for the second run of each type of syntax. Example of using DBMS Xplan:
http://jonathanlewis.wordpress.com/2006/11/09/dbms_xplan-in-10g/

If you search this group, you will probably find several examples where I (and others) have compared the performance of the various syntax methods, with execution times for each syntax.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Fri Feb 29 2008 - 14:49:17 CST