Re: minus vs. where not exists, vs. where not in

On 2003.09.21 15:24, Wolfgang Breitling wrote:
> First off, the three are not equivalent, not substitutes for each other.
> Well not in and minus would be, but they are different from not exists. "not
> in"/"minus" and "not exists" can return different results. See
> <http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:442029737684>http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:442029737684
>
> for examples and explanation.
>
> I have not done any performance comparisons but I personally routinely use
> minus and I am quite happy with it, especially across a db link.

Of course you do, because oracle brings the results of the whole remote query over db link into the temporary tablespace of the local database. Query like
select ename,job,dname
from emp e, dept_at_db d
where e.deptno=d.deptno

will bring the whole dept table over the database link into the temporary tablespace and perform join. The "not exist" condition may be faster if indexes are involved and if nested loops will give better results then sort/ merge, but those cases have to be carefully optimized and measured. Now, a slight digression: exactly because of the database having tendency to bring a ton of information over the database link, I frequently try to access remote views to bring over just a few necessary records.

--
Mladen Gogala
Oracle DBA
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mladen Gogala
  INET: mgogala_at_adelphia.net

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).