I would rewrite it with a where not exists.... But that is just me. I would also not bother selecting anything in the subquery. Just a 1 or a 'x' would do. Don't return stuff from the database that you don't want. All you care is whether or not there is a record in the subquery not what it is or how many there are. When you have that criteria think EXISTS.
-----Original Message----- Sent: Tuesday, April 02, 2002 10:48 PM To: Multiple recipients of list ORACLE-L Why not code up a couple of SQL's and try it out? I just tried a couple of examples that match your code, and the NOT IN version was slightly faster, so I'm not frowning upon it. ;-) ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, April 02, 2002 9:13 PM > 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kimberly Smith 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).
