Re: Improve query performance

From: Anurag Varma <avdbi_at_hotmail.com>
Date: Wed, 10 Dec 2003 01:08:51 GMT
Message-ID: <DmuBb.5933$qe6.5310@news02.roc.ny>

"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message news:1071006972.76046_at_yasure...
> Sybrand Bakker wrote:
>
> > On 9 Dec 2003 07:49:34 -0800, danielroy10junk_at_hotmail.com (Daniel Roy)
> > wrote:
> >
> >
> >>What I've seen so far is that NOT EXISTS performs much
> >>better than NOT IN, but that depends on the volumes of data, of
> >>course.
> >
> >
> > According to Thomas Kyte in his latest book this is a myth.
> >
> >
> > --
> > Sybrand Bakker, Senior Oracle DBA
>
> I've repeatedly proven it is a myth. And even when it wasn't ... the
> result was entirely dependent upon on the arrangement of the larger and
> smaller tables.
>
> --
> Daniel Morgan
> http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
> http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
> damorgan_at_x.washington.edu
> (replace 'x' with a 'u' to reply)
>

Daniel,

Its a myth.

A "not in" query might perform differently with RBO. With CBO and all tables analyzed, "NOT IN" is executed the same exact way as "NOT EXISTS". I've tried it a lot of times and find this to be true 8i onwards.

In fact Harrison notes this fact in this SQL Tuning book. Do you have a working example to prove this the other way?

Anurag Received on Tue Dec 09 2003 - 19:08:51 CST