> >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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
