| While SQL tuning : Is NULL, In vs Exists [message #288018] |
Fri, 14 December 2007 05:50  |
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 #288219 is a reply to message #288038] |
Sat, 15 December 2007 22:54  |
rleishman
Messages: 3728
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
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
