-
Oracle Joins Vs Not Exists
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
-
How about this??? Using timing and autotrace should give you a good measure on how the two queries measure up.
Code:
SET TIMING ON
SET AUTOTRACE ON
SELECT sf.store_format_id
FROM store_format sf
LEFT OUTER JOIN STORE st
ON st.store_format_id = sf.store_format_id
WHERE st.store_format_id IS NULL;
-
[QUOTE In other words are Outer and Inner Joins better than Not exists when considering performance?
Any details would be very helpful.
Regards,
Neha[/QUOTE]
If Joins were better then NOT Exists and Not in then the optimizer would start re-writing those queries transparently .
The answer to your question is it depends on the scenario and many other things (like indexes).You need to bench mark and see.
By the way i don't see a plan attached anywhere
regards
Hrishy
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|