| Not Exists Vs Join [message #345177] |
Tue, 02 September 2008 09:11  |
neha_garg123
Messages: 7
Registered: July 2008
|
Junior Member |
|
|
Hi,
I have general question regarding Oracle performance for Join VS Not Exists scenario.
I have the following query
SELECT sf.store_format_id
FROM store_format sf
WHERE NOT EXISTS (SELECT 1
FROM STORE st
WHERE st.store_format_id = sf.store_format_id);
I am also attaching the explain plan for this.
Now my question is that can this query be written without using not exists condition, so that it performs better than the former query. In other words are Outer and Inner Joins better than Not exists when considering performance?
Any details would be very helpful.
Regards,
Neha
-
Attachment: explain.txt
(Size: 0.46KB, Downloaded 1260 times)
|
|
|
|
|
|
|
|
|
|
| Re: Not Exists Vs Join [message #345198 is a reply to message #345189] |
Tue, 02 September 2008 10:23  |
neha_garg123
Messages: 7
Registered: July 2008
|
Junior Member |
|
|
Thanks a lot Olivia. But this one doesnt talks about actually joining the tables . I want to go through the cases where outer joins were replaced for Exists or not exists clauses.
Kindly let me know your inputs on those cases.
Regards,
Neha
|
|
|
|
| Re: Not Exists Vs Join [message #345392 is a reply to message #345198] |
Wed, 03 September 2008 06:11 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
You can rewrite the query like this to get rid of the not exists:SELECT store_format_id
FROM (SELECT sf.store_format_id
,st.store_format_id chk
FROM store_format sf
,store st
WHERE st.store_format_id(+) = sf.store_format_id)
WHERE chk IS NULL;
you could also write it using a Minus:SELECT sf.store_format_id
FROM store_format sf
MINUS
SELECT st.store_format_id
FROM STORE st;
Do let us know how they perform
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
