RE: RE: Long running SQL Problem?
Catherine, I am sure that Larry Elkins will forgive my taking the opportunity of being 7 hours ahead of him (and 7 hours behind yourself) for singing the praise of the 'hash anti-join' in his name. And anyway you could remind your senior DBA the existence of NOT EXISTS, far better than COUNT(*) in this case. Basically, in case A you have a non-correlated sub-query, and in case B a correlated one. A correlated sub-query means that for each row from Table_1 you must search Table_2. For one thing, if the corresponding columns are not indexed, you're dead. Even if they are, it may really be bad if Table_1 is huge AND THERE IS NO OTHER CRITERION, because you will have a full scan of Table_1. A non-correlated subquery is executed only once. If it returns few rows, you will have a full TS of Table_1 in both cases, but the NOT IN is likely to be slightly more efficient. If it returns many rows, if Table_1 is big, if there is no other criterion and if col3 and col4 are not null, then the NOT IN with a hint asking for a hash anti-join will outperform a NOT EXISTS, not to mention a 0 = (select COUNT(*) ...). The nested loops of a correlated subquery will be excellent when you have fairly selective criteria besides, and when the correlated subquery is, so to speak, the icing on the cake. Let me add that an external join with a test for nullity usually gives fairly good results too (in fact, it often goes the hash antijoin way) and that I have also had excellent results under some circumstances with an inline view (typically when you have additional criteria bearing on Table_2) ... To summarize, abruptly saying 'this sucks' exposes you to be proved wrong once in a while. - Original Message - From: CHAN Chor Ling Catherine (CSC) [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tue, 02 Apr 2002 21:13:19 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 Stephane Faroult Oriole Corporation Performance Tools Free Scripts -- http://www.oriole.com, designed by Oracle DBAs for Oracle DBAs -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroul 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).
RE: Long running SQL Problem?
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 50 + 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
RE: Long running SQL Problem?
Use 'exists' or 'not exists' only if you have index on col3 and col4 on table_2 that can be used in the sub-query, else the query will be running like a dog. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! *2 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. *2
Re: Follow-up :Long running SQL Problem?
CHAN Chor Ling Catherine (CSC) wrote: 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 Larry has pointed to me off-list that your 'SQL A' query is indeed correlated - totally unusual for a 'NOT IN' and, in your case, such a case for disaster (couldn't return anything) that I presume that you typed it as fast as I read it initially? Being as lazy as he is :-) here is from Larry's message : Also, point her towards Metalink note 28934.1. It contains a good discussion. But I don't agree with the final conclusion to always use NOT EXISTS even though a NOT IN using a HASH AJ is sometimes much better. The only reason for that recommendation was their fear that many folks don't understand how a NOT IN handles nulls in the results set (returns no rows) differently than a NOT EXISTS. A good developer should know the difference. HTH, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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).
RE: Follow-up :Long running SQL Problem?
Hi, Thanks to those who take the trouble to reply. It's indeed enlightening. I've learnt a lot from you guys. Regds, New Bee -Original Message- From: Stephane Faroult [mailto:[EMAIL PROTECTED]] Sent: Thursday, April 04, 2002 3:54 AM To: Multiple recipients of list ORACLE-L Subject:Re: Follow-up :Long running SQL Problem? CHAN Chor Ling Catherine (CSC) wrote: 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 Larry has pointed to me off-list that your 'SQL A' query is indeed correlated - totally unusual for a 'NOT IN' and, in your case, such a case for disaster (couldn't return anything) that I presume that you typed it as fast as I read it initially? Being as lazy as he is :-) here is from Larry's message : Also, point her towards Metalink note 28934.1. It contains a good discussion. But I don't agree with the final conclusion to always use NOT EXISTS even though a NOT IN using a HASH AJ is sometimes much better. The only reason for that recommendation was their fear that many folks don't understand how a NOT IN handles nulls in the results set (returns no rows) differently than a NOT EXISTS. A good developer should know the difference. HTH, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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: 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).
RE: Long running SQL Problem?
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 50 + 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).
Re: Long running SQL Problem?
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 50 + 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).
Long running SQL Problem?
Title: Long running SQL Problem? 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 50 + 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 www.marshalsoftware.com
Re: Long running SQL Problem?
Hi, You could try the NOT EXIST flavour. It should be able to use indexes than Jack Denham Eva [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: (bcc: Jack van Zanen/nlzanen1/External/MEY/NL) [EMAIL PROTECTED] Subject: Long running SQL Problem? 27-03-2002 09:53 Please respond to 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 50 + 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 www.marshalsoftware.com == De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst Young, niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst Young. Ernst Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst Young does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. If you are not the intended recipient of this communication please return the communication to the sender and delete and destroy all copies. In carrying out its engagements, Ernst Young applies general terms and conditions, which contain a clause that limits its liability. A copy of these terms and conditions is available on request free of charge. === -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack van Zanen 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
RE: Long running SQL Problem?
Hi Denham, Suggestion 1) Perhaps you may create an index for table_1 (col1,col2) and table_2 (col3,col4) Suggestion 2) Try SELECT col1,col2 FROM Table_1 WHERE (0=(select count(*) from table_2 where col3=col1 and col4=col2)) Hope it helps. Regds, Catherine -Original Message- Sent: Wednesday, March 27, 2002 4:53 PM 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 50 + 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 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).
RE: Long running SQL Problem?
Should be better with select col1, col2 from table_1 minus select col3, col4 from table2 Iain Nicoll -Original Message- Sent: Wednesday, March 27, 2002 8: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 50 + 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 www.marshalsoftware.com _ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nicoll, Iain (Calanais) 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).
RE: Long running SQL Problem?
Title: Long running SQL Problem? 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-From: Denham Eva [mailto:[EMAIL PROTECTED]]Sent: Wednesday, March 27, 2002 10:53 AMTo: Multiple recipients of list ORACLE-LSubject: Long running SQL Problem? 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 50 + 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 www.marshalsoftware.com This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal - For more information please visit www.marshalsoftware.com
Re: Long running SQL Problem?
Try something like select a.col1||a.col2, b.col3||b.col4 from table1 a, table2 b where a.col1||a.col2 = b.col3||b.col4 (+)) WHERE b.col3||b.col4 IS NULL; I think that works. Denham Eva EvaDTo: Multiple recipients of list ORACLE-L @TFMC.co.za [EMAIL PROTECTED] Sent by: rootcc: Subject: Long running SQL Problem? 03/27/2002 03:53 AM Please respond to 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 50 + 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 www.marshalsoftware.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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).
Re: Long running SQL Problem? [stupid alternative to NOT IN]
ORACLE-L Digest -- Volume 2002, Number 086 From: Denham Eva [EMAIL PROTECTED] Date: Wed, 27 Mar 2002 10:58:23 +0200 Subject: Long running SQL Problem? ... Is there anyone who can give me a solution to this problem. get faster/more RAM, CPU, hard drives, etc? It is a sql that runs forever and I eventually have to kill it, both tables are large 50 + 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); these are indexed? probably no faster, but will probably barf out the initial chunks of output as it goes along, and thus seem slightly less boring to anyone observing the output during runtime: SELECT col1, col2 /* debug only: , col3, col4 */ FROM Table_1 t1, Table_2 t2 WHERE t1.col1 = t2.col3 (+) and t1.col2 = t2.col4 (+) and ( t2.col3 is null and t2.col4 is null ); btw, there is a similar alternative using exists, but I haven't found it to be any faster. regards, ep -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Eric D. Pierce 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).
