Home » RDBMS Server » Performance Tuning » No difference in plan and cost for IN and EXISTS (Oracle 10.2.0.1.0)
No difference in plan and cost for IN and EXISTS [message #392997] Thu, 19 March 2009 23:27 Go to next message
mbmalasenthil
Messages: 27
Registered: July 2008
Junior Member
Hi,

I have a query as shown below:

  select SUM(DECODE(a.year, 2003, a.month, 0)),
         a.FID,
         a.OID,
         a.GID,
         a.CID,
         a.PRODUCT_ID,
         a.TID,
         a.CURR
    from table1 a
   where a.CID = 500
   and a.OID IN
         (SELECT leaf_node
            FROM table2
           WHERE table2.id = 101069
             and (table2.node02 = 7009 or
                 table2.node01 = -99000 or
                 table2.node01 = 7011))
     and a.year IN (2003)
   group by a.FID,
            a.OID,
            a.GID,
            a.CID,
            a.PRODUCT_ID,
            a.TID,
            a.CURR

I replaced the IN clause with EXISTS and saw the execution plan. I dont see any change in the plan for both IN and EXISTS and also no change in cost/time. What does it mean? I read in many places that using EXISTS is a better style of programming that IN. Please suggest.
Thank you,
Senthil
Re: No difference in plan and cost for IN and EXISTS [message #393000 is a reply to message #392997] Thu, 19 March 2009 23:34 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>I read in many places that using EXISTS is a better style of programming that IN.
Perhaps what you read is/was wrong.
If "EXISTS" is "always" better than "IN", why does "IN" exist?
The GOLDEN rule of programming, is that no rule of programming is true 100% of the time.
Re: No difference in plan and cost for IN and EXISTS [message #393001 is a reply to message #393000] Thu, 19 March 2009 23:37 Go to previous messageGo to next message
mbmalasenthil
Messages: 27
Registered: July 2008
Junior Member
Thanks BlackSwan, in my case (query) what would you suggest me to use? In/EXISTS? I am perplexed as to what to be used as I dont see any change in either plan or cost.

Thanks,
Senthil
Re: No difference in plan and cost for IN and EXISTS [message #393003 is a reply to message #392997] Thu, 19 March 2009 23:46 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
Some SQL can not be made more efficient.
Take a look at the STICKY post in the Performance sub-forum.
Realize that if performance could be reduced to an algorithm,
it would have been already done.
SQL performance is still more art than science.
Re: No difference in plan and cost for IN and EXISTS [message #393004 is a reply to message #393003] Thu, 19 March 2009 23:51 Go to previous messageGo to next message
mbmalasenthil
Messages: 27
Registered: July 2008
Junior Member
Thanks! Very interesting thought that. Will read the post and revert with more questions. Have a good day Smile
Thanks,
Senthil
Re: No difference in plan and cost for IN and EXISTS [message #393247 is a reply to message #393004] Fri, 20 March 2009 16:32 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Oracle's optimizer can rewrite IN queries so that they execute as EXISTS and vice-versa.

What's happening here is that Oracle thinks one of them is better. When you try the other one, it rewrites the query internally.

If you suppress the REWRITE capability, you will be able to try out the other plan and see if it is faster. Just use the NO_REWRITE hint.
SELECT /*+NO_REWRITE*/ ...


Ross Leishman
Re: No difference in plan and cost for IN and EXISTS [message #393276 is a reply to message #392997] Sat, 21 March 2009 01:19 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read the following T. Kyte article:
http://www.oracle.com/technology/oramag/oracle/06-may/o36asktom.html

Regards
Michel
Previous Topic: Slowness Problem when logging in by tool Oracle sql developer
Next Topic: query problem (merged 4)
Goto Forum:
  


Current Time: Mon Dec 05 21:37:07 CST 2016

Total time taken to generate the page: 0.08801 seconds