Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Improve query performance

Re: Improve query performance

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Wed, 10 Dec 2003 08:51:00 -0800
Message-ID: <1071075094.173151@yasure>


Anurag Varma wrote:

> "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
>
>

I said "I've repeatedly proven it is a myth".

Of that there is no question. It took creating special conditions in older versions of Oracle under RBO for it to be otherwise.

-- 
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)
Received on Wed Dec 10 2003 - 10:51:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US