Hi,

(in rule based optimisation)
When you use an IN query use are using the inner query to drive the
outer query.
When you use EXISTS you are running the outer query first and using each
value
to fetch a value from the inner query

Most of the time the WHERE EXISTS will give you better performance as
you need to specify
a join condition which may invoke an index scan

An IN query is good when the inner query will return only a few results.

HTH

C.

-----Original Message-----
From: Premnath
[mailto:oracledba-ezmlmshield-x2018825.[Email address protected]
Sent: Friday, 2 December 2005 12:05 PM
To: LazyDBA Discussion
Subject: [SPAM] - Differnce - Number of numbers in MIME From exceeds
maximum threshold


Hi guru's,

I would like to know the difference between the use of IN and
EXISTS in a query. Which has better performance and which should be
when?

Thanks
Prem


--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html


Oracle LazyDBA home page