Home » SQL & PL/SQL » SQL & PL/SQL » In vs Exists
In vs Exists [message #193710] Tue, 19 September 2006 01:54 Go to next message
Messages: 194
Registered: January 2006
Senior Member
In an update statement where I have:

 update parent p
       set  ...  = ... 
    where exists ( select 1
                     from child c
                    where f.surname = c.surname);

My colleague tells me

'.. EXISTS execute for every row in the parent query, the overhead will be high since we only know that a fraction of rows in the parent will be updated. However, a subquery using IN is executed only once, so the parent rows will be updated efficiently.'

Is this true?? because I think otherwise. By the way, Parent table has 130M rows and Child has 400000 rows. The updated rows can be from 200000 to 5M rows..

Re: In vs Exists [message #193781 is a reply to message #193710] Tue, 19 September 2006 06:52 Go to previous messageGo to next message
Messages: 52
Registered: August 2006
Ask Tom
Re: In vs Exists [message #193910 is a reply to message #193781] Tue, 19 September 2006 21:31 Go to previous message
Messages: 3727
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I followed the link to Ask Tom, but got impatient because it is so long, so...

Prior to v8i, your colleague was correct. In 8i, the /*+UNNEST*/ hint was introduced, which allows an EXISTS to be resolved using a join, giving it much the same capabilities as IN.

As of (I think) 9i, the CBO works out from the statistics whether it reckons unnesting will help, and does it automatically. ie. There is no need for the hint.

There is one subtle difference between IN and an unnested EXISTS though: IN will de-duplicate the subquery, which requires a UNIQUE SORT. Depending on the size of the result set, this could be a significant factor. EXISTS avoids the sort by using a SEMI JOIN - look it up if you are interested.

So, in summary, for most large sub-queries, IN and EXISTS will perform similarly, although EXISTS has a slight performance advantage.

Be warned though, if your sub-query contains anything other than a simple join back to the outer query and perhaps a single WHERE filter, the CBO's estimate of size of the result set will get quite suspect and it might not make the correct decision on whether to nest or unnest. If you want consistent handling, you can use the NEST/UNNEST hint, or perhaps the CARDINALITY hint if you want it to be a bit more subtle and future-proof.

Ross Leishman
Previous Topic: How to execute the result
Next Topic: Need to generate hierarchical recursive XML using SQL
Goto Forum:

Current Time: Fri Aug 18 09:34:17 CDT 2017

Total time taken to generate the page: 0.05944 seconds