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

Home -> Community -> Mailing Lists -> Oracle-L -> RE: IN or Exists --- performance issue

RE: IN or Exists --- performance issue

From: Stephane Faroult <sfaroult_at_oriolecorp.com>
Date: Tue, 03 Jun 2003 05:01:28 -0800
Message-ID: <F001.005A8B4B.20030603050128@fatcity.com>

>
>Hi Listers
>
>I have a unique performance problem. As a general
>rule by oracle while
>writing SQL scripts EXISTS should be used in place
>of IN.
>

As a general rule there is no general rule. Why do you want to use EXISTS if it goes faster with IN ?

EXISTS is mostly used with a correlated subquery - in other words, a query which you cannot execute without knowing some values from the current row. It fires for each row you return. In contrast, a IN is usually used with an uncorrelated subquery - you execute it once, get a number of values, and then compare each row to the resulting set. If you have no other criterion, and if of course the uncorrelated subquery doesn't return zillions of rows, the uncorrelated subquery is usually faster. If you have other efficient criteria and the existence test is executed as a kind of after-thought, final screening of a relatively modest set of rows, go for the EXISTS and the correlated subquery. In case of doubt, test both.

Do not try to make results fit the theory, especially when the theory is wrong. And if I were you I would get rid of hints. I tend to see hints as surgeons see amputation. If I can avoid them ...

Regards,

Stephane Faroult
Oriole

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriolecorp.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Tue Jun 03 2003 - 08:01:28 CDT

Original text of this message

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