DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Oracle Joins Vs Not Exists

Hybrid View

  1. #1
    Join Date
    Sep 2008
    Posts
    1

    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

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    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;

  3. #3
    Join Date
    Jan 2001
    Posts
    2,828
    [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
  •  


Click Here to Expand Forum to Full Width