Hi Gurus,
My senior DBA always tell us that the "not in" command sucks and we are all
encourage to use the select count(*). SQL A is greatly frowned upon and SQL
B will be the best.
SQL A :
SELECT col1,col2
FROM Table_1
WHERE (col1,col2) NOT IN (SELECT col3,col4
FROM Table_2
WHERE col3 = col1
AND col4 = col2);
SQL B :
SELECT col1,col2
FROM Table_1 A
WHERE (0=(SELECT COUNT(*) FROM Table_2 b WHERE b.col3=a.col1 AND
b.col4=a.col2));
Qn : Is it true ? Could someone shed some light ? Please advise. Thanks.
Regds,
Catherine
-----Original Message-----
Sent: Wednesday, March 27, 2002 8:19 PM
To: CHAN Chor Ling Catherine (CSC)
Hello Catherine
Thanks first of all for your suggestions.
The indexes were already in exitance before your email, so I did not even
try that.
But your query and that of Marco van Rooy ran exactly the same number of
seconds.
They are both basicly the same.
Marco's looked like this...
SELECT col1,col2
FROM Table_1
WHERE (col1,col2) NOT IN (SELECT col3,col4
FROM Table_2
WHERE col3 = col1
AND col4 = col2);
Because both yours and Marcos brought the data back in so short a time
*16sec*, I have not yet experimented with any of the others.
Thanks again
Rgds
Denham
-----Original Message-----
<mailto:[EMAIL PROTECTED]> ]
Sent: Wednesday, March 27, 2002 2:00 PM
To: '[EMAIL PROTECTED]'
Hi Denham,
I would like to know which solution is the fastest.
Regds,
Catherine
-----Original Message-----
Sent: Wednesday, March 27, 2002 7:44 PM
To: Multiple recipients of list ORACLE-L
Hi List
Thank you to everyone who took the time to answer, I never realised that
there could be so many solutions :)
Rgds
Denham
-----Original Message-----
Sent: Wednesday, March 27, 2002 10:53 AM
To: Multiple recipients of list ORACLE-L
Hello List
Is there anyone who can give me a solution to this problem.
It is a sql that runs forever and I eventually have to kill it, both tables
are large 500000 + rows.
Is there perhaps a quicker more effecient way of doing this.
SELECT col1,col2
FROM Table_1
WHERE (col1,col2) NOT IN (SELECT col3,col4 FROM Table_2);
TIA
Denham Eva
Oracle DBA
_____
This e-mail message has been scanned for Viruses and Content and cleared by
MailMarshal - For more information please visit
< http://www.marshalsoftware.com <http://www.marshalsoftware.com> >
www.marshalsoftware.com
_____
_____
This e-mail message has been scanned for Viruses and Content and cleared by
MailMarshal - For more information please visit
< http://www.marshalsoftware.com <http://www.marshalsoftware.com> >
www.marshalsoftware.com
_____
_____
This e-mail message has been scanned for Viruses and Content and cleared by
MailMarshal - For more information please visit
<http://www.marshalsoftware.com> www.marshalsoftware.com
_____
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: CHAN Chor Ling Catherine (CSC)
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).
