Home » RDBMS Server » Performance Tuning » Not Exists Vs Join (10g)
Not Exists Vs Join [message #345177] Tue, 02 September 2008 09:11 Go to next message
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 212 times)
Re: Not Exists Vs Join [message #345178 is a reply to message #345177] Tue, 02 September 2008 09:13 Go to previous messageGo to next message
BlackSwan
Messages: 25038
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above

> In other words are Outer and Inner Joins better than Not exists when considering performance?

What do your benchmark comparison tests show?
Re: Not Exists Vs Join [message #345182 is a reply to message #345178] Tue, 02 September 2008 09:20 Go to previous messageGo to next message
neha_garg123
Messages: 7
Registered: July 2008
Junior Member
I am not sure how to bench mark these results. Pardon me I am a beginner at performance related things.

Re: Not Exists Vs Join [message #345189 is a reply to message #345182] Tue, 02 September 2008 09:56 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Please go through the link below. May be helpful for you

http://www.orafaq.com/forum/mv/msg/122989/338974/125380/#msg_342758


Regards,
Oli

[Updated on: Tue, 02 September 2008 09:57]

Report message to a moderator

Re: Not Exists Vs Join [message #345198 is a reply to message #345189] Tue, 02 September 2008 10:23 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: Performance tuning
Next Topic: alternative for DISTINCT
Goto Forum:
  


Current Time: Mon Dec 05 15:06:48 CST 2016

Total time taken to generate the page: 0.07735 seconds