Home » SQL & PL/SQL » SQL & PL/SQL » NOT EXISTS VERSUS MINUS
NOT EXISTS VERSUS MINUS [message #308456] Mon, 24 March 2008 07:12 Go to next message
aditiC
Messages: 38
Registered: February 2006
Location: INDIA
Member
Dear All,

Please clarify the below reg SQL Tuning:

I have 2 SQL Query (one with 'NOT EXISTS' & other with 'MINUS')as below:

Query 1(NOT EXISTS):

SELECT title,copy#
FROM dvd_titles T
WHERE NOT EXISTS
(SELECT 'x'
FROM checked_out CO
WHERE T.title = CO.title
AND T.copy# = CO.copy#);

Query 2(MINUS):

SELECT title,copy#
FROM dvd_titles T
MINUS
SELECT title,copy#
FROM checked_out CO

Both tables are having considerable amount of data (20435786 rows). Query 2 takes lesser time as compared to Query 1.

According to me in Query 2, we are providing full table scan in both the tables so it should take more time.

Indexes are also created on the table. Please let me know the reason (in simple terms)why 'MINUS' is giving better performance as compared to 'NOT EXISTS'. I am new to Performance Tuning.

Thanks,
Aditi
Re: NOT EXISTS VERSUS MINUS [message #308459 is a reply to message #308456] Mon, 24 March 2008 07:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It depends on many things.
Sometimes minus is better, sometimes it is not exists.
See How to Identify Performance Problem and Bottleneck and OraFAQ Oracle SQL Tuning Guide

By the way, the 2 queries may not return the same result in case there are NULL values.

Regards
Michel
Re: NOT EXISTS VERSUS MINUS [message #308564 is a reply to message #308459] Mon, 24 March 2008 21:57 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
If TITLE and COPY# are defined as NOT NULL in both tables, then Method 1 has greater opportunity for better performance.

If this constraint is satisfied, then the NOT EXISTS can be converted into a NOT IN, which can in turn be converted into an ANTI-JOIN. A Hash ANTI-JOIN is faster over higher volumes than a SORT, which is what is required for MINUS.

Ross Leishman
Previous Topic: String function
Next Topic: dblink between 10g and 9i
Goto Forum:
  


Current Time: Sat Dec 10 20:50:46 CST 2016

Total time taken to generate the page: 0.05624 seconds