Re: Performance question

In article <ii3d4.1711$x4.186688_at_petpeeve.ziplink.net>,   "David Cressey" <David_at_DCressey.com> wrote:
> These answer to your question is that performance of queries is
 specific to
> the techniques used by the optimizer. Different relational DBMS
 engines use
> different optimization techniques. Over time, the optimizer in any
 given
> product evolves from one release of the product to the next.
>
> For Oracle RDBMS and Oracle RDB, the only two I know, the exists
 construct
> will deliver better results.
> (assuming you use cost based optimization with Oracle RDBMS)
>
> Oracle RDBMS underwent significant changes to its optimizer between
 version
> 7 and version 8.
>
> idiot wrote in message <3874a09e_at_athene.hdm-stuttgart.de>...
> >I'm confused of using two methods of corellating the subquery, i. e.
> >" ... x in (select y ...) ... " and
> >" ... exists (select * .... where y = x) ... "
> >
> >is that a fact that one of them always works faster than other or
 there are
> >nuances?
> >
> >thanx
> >

I also work with Oracle and it is my understanding that the diffence between an exists clause and an in clause is that the exists sub-query is fired once for every row returned in the main query while the in clause sub-query is executed once for the statement.

And it is not true that the exists sub-query is always faster than the in clause. It depends on the SQL statement and what you are testing. I do not know when one is better than the other, but I have worked with SQL statements where the explain plan and timed tests worked better with one clause over the other and another SQL worked the other way. I believe the determining factor has to do with the number of rows returned by the parent query and the number of values returned by the in clause select. As the number of rows returned by the parent increases the cost of the exists increases and when the result set of the in clause is small enough to fit in a few Oracle data blocks the in clause becomes superior.

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com http://www.deja.com/
Before you buy.