Home » SQL & PL/SQL » SQL & PL/SQL » Which method uses less server resources, 'MINUS' or 'NOT IN'?
Which method uses less server resources, 'MINUS' or 'NOT IN'? [message #189658] Fri, 25 August 2006 08:15 Go to next message
Rustican
Messages: 51
Registered: July 2006
Member
I'm working with two large tables and need to exclude certain rows from one which is found in the other. I was just wondering which method would make a faster query return, the 'MINUS' method or a 'NOT IN'. Also is there a way i can check the performance of my query? I'm using the Oracle SQL Developer application.
Re: Which method uses less server resources, 'MINUS' or 'NOT IN'? [message #189668 is a reply to message #189658] Fri, 25 August 2006 08:49 Go to previous messageGo to next message
gbarbisan
Messages: 67
Registered: August 2006
Location: Treviso - Italy
Member
I personally am a fan of MINUS...
I always found it more efficient than NOT IN.

Wink
Re: Which method uses less server resources, 'MINUS' or 'NOT IN'? [message #189719 is a reply to message #189658] Fri, 25 August 2006 17:49 Go to previous messageGo to next message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
If one were always better than the other, the poor performing one would be deprecated. The question is not answerable. If I'm selecting entire rows from one table that has millions of records in it, and it needs records that are not in another table, say with 10 records and a different structure, I certainly wouldn't use MINUS. Neither one is magic, it depends on the situation. When you have a situation, benchmark and test, then you will know for sure.
Re: Which method uses less server resources, 'MINUS' or 'NOT IN'? [message #189819 is a reply to message #189719] Mon, 28 August 2006 00:19 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I kind of agree with Scott, but I reckon he's sitting on the fence a bit.

MINUS will never be deprecated because its so simple to use. NOT IN is tricky because it gives non-intuitive results if the sub-query returns NULLs.

MINUS does essentially a MERGE ANTI-JOIN, plus it removes duplicates from the first query.

NOT-IN also does an anti-join, but the optimizer can choose whether to do Nested Loops, Merge, or Hash, depending on the queries. This makes NOT IN a bit more versatile.

If the outer-query is a very small result set, then NOT IN can nest the sub-query and perfom an indexed lookup, which will way-out-perform the MINUS.
If the sub-query is a small-medium result set (<10000 rows), then it may perform a HASH anit-join, which will also out-perform the MINUS.

If both the outer and sub-queries are very large, then NOT-IN will almost certainly perform a HASH anti-join, which could end up under-performing - but no guarantees. You can usually get NOT IN to perform a MERGE ANTI JOIN using a hint, and it will perform comparably with MINUS.

So NOT IN is always faster than MINUS, right? Wrong. The power of NOT IN depends on being able to perform an ANTI-JOIN, which is not always possible - read the doco. My advice is to use NOT IN. If you cannot get it performing satisfactorily, start reading up on anti-joins. If you don't have time to do that, revert to MINUS.


Ross Leishman
Re: Which method uses less server resources, 'MINUS' or 'NOT IN'? [message #189936 is a reply to message #189819] Mon, 28 August 2006 11:46 Go to previous message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
Oh c'mon Ross. I think all that can be inferred from my reply. Wink
Previous Topic: loading CLOB data into Oracle 9i tables
Next Topic: SQL Query Performance Problem
Goto Forum:
  


Current Time: Fri Dec 09 01:56:31 CST 2016

Total time taken to generate the page: 0.06900 seconds