SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Oct 2004
    Location
    slovakia
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Oracle query question

    Hi,
    this two queries are supposed to return same

    1) select cus_no from customer where cus_no not in (select cus_no from cust_history)

    2) select cus_no from customer minus select cus_no from customer_hist

    hovever first one takes ages to execute and second is done within minute (yes there are 10 millions rows in cust tables:-)

    thank you

  2. #2
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,294
    Mentioned
    123 Post(s)
    Tagged
    1 Thread(s)
    That would be correct because:

    • In option 1, you are doing a subselect, so it will still have to process EACH row in the first table before looking to see if that value is in the subselected table.
    • In option 2, you are basically doing a left outer join so it will first join the two tables together then process the query so you're only doing one select, plus you're only processing those records that should apply.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  3. #3
    SitePoint Wizard Defender1's Avatar
    Join Date
    Apr 2001
    Location
    My Computer
    Posts
    2,808
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Stay away from subqueries wherever possible, they will considerably slow your query down.
    As dave mentioned, you'll have to process the subquery for every row returned by the main query.
    So if you have 10 mil rows in customers, and 20 mil in history, thats 10mil ^20mil as far as your query plan goes. That's why it'll take forever and a day.
    Defender's Designs
    I'm Getting Married!

    Not-so-patiently awaiting Harry Potter Book 7 *sigh*

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    no, that's not right, subqueries are fine, the database optimizer usually executes them as joins anyway

    see How to Misuse SQL's FROM Clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •