Re: The best solution to find differences between multiple rowset
Date: Fri, 29 Feb 2008 12:49:17 -0800 (PST)
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
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
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.
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Fri Feb 29 2008 - 14:49:17 CST