Home » SQL & PL/SQL » SQL & PL/SQL » Performance of Exist and IN
Performance of Exist and IN [message #248417] Thu, 28 June 2007 23:59 Go to next message
vermarah
Messages: 1
Registered: June 2007
Junior Member
Hi

I am new to SQL...Can anyone tell me Which gives me better performance.......EXISTS or IN
Re: Performance of Exist and IN [message #248418 is a reply to message #248417] Fri, 29 June 2007 00:03 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
It depends.
Base upon what/which metric to quantify "better".
What problem are you really trying to solve & how would an independent observer conclude the correct answer was met.
Re: Performance of Exist and IN [message #248432 is a reply to message #248417] Fri, 29 June 2007 01:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If the answer was always IN or always EXISTS, do you think Oracle will keep the other one?

Regards
Michel
Re: Performance of Exist and IN [message #248436 is a reply to message #248418] Fri, 29 June 2007 01:16 Go to previous messageGo to next message
charu.jain21@gmail.com
Messages: 1
Registered: June 2007
Location: delhi
Junior Member

hello

I completely agree with the above statement

To determine which clause offers better performance , consider how many rows the inner query will return in comparison to the outer query. In many cases, EXISTS is better because it requires you to specify a join condition, which can invoke an INDEX scan. However, IN is often better if the results of the subquery are very small. You usually want to run the query that returns the smaller set of results first.

best regards
charu
Re: Performance of Exist and IN [message #248439 is a reply to message #248436] Fri, 29 June 2007 01:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use CBO, it will rewrite the query with the best option.

Regards
Michel
Re: Performance of Exist and IN [message #248442 is a reply to message #248436] Fri, 29 June 2007 01:39 Go to previous messageGo to next message
caliguardo
Messages: 107
Registered: February 2007
Location: Chennai
Senior Member

If the table in the subquery is
small, and the table in the main query is large, then IN Operator usually makes sense.
If the table in the subquery is
large, and the table in the main query is small, then EXISTS
Operator usually makes sense.
Re: Performance of Exist and IN [message #248539 is a reply to message #248442] Fri, 29 June 2007 07:56 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
caliguardo wrote on Fri, 29 June 2007 16:39
If the table in the subquery is
small, and the table in the main query is large, then IN Operator usually makes sense.
If the table in the subquery is
large, and the table in the main query is small, then EXISTS
Operator usually makes sense.



An interesting theory. On what do you base this? Explain in terms of reduced IO how you think this happens.

Ross Leishman
Re: Performance of Exist and IN [message #248542 is a reply to message #248539] Fri, 29 June 2007 08:02 Go to previous messageGo to next message
caliguardo
Messages: 107
Registered: February 2007
Location: Chennai
Senior Member

Using "IN", the optimizer generally runs the subquery first, then joins with the main dataset.But on the Other hand.
Using "EXISTS", the optimizer generally runs the main query first, then applies that dataset to the subquery.

I have read this in a performance tuning article and tried with my database. It worked really better as per the article.
Re: Performance of Exist and IN [message #248544 is a reply to message #248542] Fri, 29 June 2007 08:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
caliguardo wrote on Fri, 29 June 2007 15:02
Using "IN", the optimizer generally runs the subquery first, then joins with the main dataset.But on the Other hand.
Using "EXISTS", the optimizer generally runs the main query first, then applies that dataset to the subquery.

I have read this in a performance tuning article and tried with my database. It worked really better as per the article.

Using IN or EXISTS, the optimizer will change it in the best one.

Read this definitive article from T. Kyte: On Joins and Query Plans

Regards
Michel

Re: Performance of Exist and IN [message #248545 is a reply to message #248542] Fri, 29 June 2007 08:16 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
That was certainly correct up to 8i, but it's less true in later versions where unnesting is automated in the optimizer.

Lets say it is still true though, how does outer-first-then-inner (for an EXISTS) work faster when the outer table is small and the inner table is large?

I don't see the connection.

Ross Leishman
Re: Performance of Exist and IN [message #248547 is a reply to message #248544] Fri, 29 June 2007 08:18 Go to previous messageGo to next message
caliguardo
Messages: 107
Registered: February 2007
Location: Chennai
Senior Member

Then Michael, What is your pick for me?.

Sorry Ross, Didn't see your reply?.
What you said is really thinkable. But Full outer table scan is faster when the outer table is small. Though sounds freaky, It does have an impact on the query.But it purely depends on the OP's requirement whether to go for IN or EXISTS.

[Updated on: Fri, 29 June 2007 08:47]

Report message to a moderator

Re: Performance of Exist and IN [message #248557 is a reply to message #248547] Fri, 29 June 2007 08:36 Go to previous message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Then Michael, What is you pick for me?

Nothing personal, you're the Nth in the thread to explain why IN and why EXISTS when it does not matter as Oracle optimizer may convert it to the other.
It could be the next one if I'd be a little bit more patient.

The conclusion is: write as you prefer and let the optimizer do its job.

Regards
Michel
Previous Topic: SQL query problem
Next Topic: Error PLS-00428: Problem creating Procedure
Goto Forum:
  


Current Time: Wed Dec 07 06:54:36 CST 2016

Total time taken to generate the page: 0.05683 seconds