Oracle query question
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:-)
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.
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.
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