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, [EMAIL PROTECTED] 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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
