PROTECTED]
Subject: RE: IN or Exists --- performance issue
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
I nearly alwayshave issues with
blanket statements on performance. That includes the statement that EXISTS
should be used in place of IN.
If the IN performs better in the particular
case, use it. If it doesn't perfom adiquately then, by all means, convert it to
an EXISTS. And do the
there is an in exists thread on asktom. Generally speaking exists is better if the
sub-query will have a larger and most costly result set than the outer query. Its the
other way around for 'in'.
I may have them backward, though I think that is correct.
From: Munish Bajaj [EMAIL PROTECTED]
Munish,
You are right, as a *general rule of thumb* EXISTS is *usually* faster than
IN..
There can however be problems when an EXISTS is used to manipulate or select
data from a very large table, where the row exists in a far smaller table,
as it will read every row in the large table, and then
--_=_NextPart_001_01C329BC.79FDEA20
Content-Type: text/plain; charset=iso-8859-1
Munish
I've got a funny feeling that this thing about using EXISTS rather than IN
is a bit of a myth. I do a lot of this sort of thing and I find that almost
invariably, an IN with a simple subquery is faster
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
