Home » RDBMS Server » Performance Tuning » While SQL tuning : Is NULL, In vs Exists
While SQL tuning : Is NULL, In vs Exists [message #288018] Fri, 14 December 2007 05:50 Go to next message
hidnana
Messages: 87
Registered: November 2007
Location: chennai
Member
With respect to performance tuning.
Could you please explain specific things to be considered while
using Is NULL/Is not NULL. Any way to write it better.
Is using EXISTS better than using IN ?

I understand that when we have most selective filter appearing in the subquery and there are indexes on the join columns, it is better to use IN. But when the most selective filter is in the parent query, using EXISTS allows the selective predicates in the parent query to be applied before filtering the rows against the EXISTS criteria.
Is my understanding right and does it apply under all circumstances ?

[Updated on: Fri, 14 December 2007 06:31]

Report message to a moderator

Re: While SQL tuning : Is NULL, In vs Exists [message #288038 is a reply to message #288018] Fri, 14 December 2007 07:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't worry about that, write the query in a logical/understanding way, keep your statistics up to date and let the optimizer choose the one it wants.

Regards
Michel

[Updated on: Fri, 14 December 2007 07:14]

Report message to a moderator

Re: While SQL tuning : Is NULL, In vs Exists [message #288219 is a reply to message #288038] Sat, 15 December 2007 22:54 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
As of V9, Oracle can use much the same access paths to evaluate Joins, [NOT] IN subqueries, and [NOT] EXISTS subqueries.

There are some subtle differences, but they do not affect performance in most situations.

The general rule I apply is:
  • Use [NOT] IN for driving predicates. ie. The keys in the sub-query drive the superset of rows that you are interested in.
  • Use EXISTS for filtering rows after you have already applied a primary access predicate. ie. You alread have a reduced row-set, and now you want to reduce it a bit more.

The important thing for performance is to ALWAYS look at your Explain Plan. Look for NESTED LOOPS and FILTER in your low-volume queries, and HASH joins in high-volume queries.

See here for some more on sub-queries.

Ross Leishman
Re: While SQL tuning : Is NULL, In vs Exists [message #288234 is a reply to message #288018] Sun, 16 December 2007 01:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oracle magazine, On Joins and Query Plans article from Tom Kyte.

Regards
Michel

Re: While SQL tuning : Is NULL, In vs Exists [message #288395 is a reply to message #288219] Mon, 17 December 2007 06:03 Go to previous message
hidnana
Messages: 87
Registered: November 2007
Location: chennai
Member
Thanks Ross & Michael.

[Updated on: Mon, 17 December 2007 06:04]

Report message to a moderator

Previous Topic: To improve execution time of query
Next Topic: Optimum value for INITIAL_EXTENT
Goto Forum:
  


Current Time: Sun Dec 11 00:27:01 CST 2016

Total time taken to generate the page: 0.06790 seconds