Oracle-L: RE: Long running SQL Problem?

From: CHAN Chor Ling Catherine (CSC) <clchan_at_nie.edu.sg>
Date: Tue, 02 Apr 2002 21:13:19 -0800
Message-ID: <F001.0043982A.20020402211319@fatcity.com>

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:clchan_at_nie.edu.sg> ]
Sent: Wednesday, March 27, 2002 2:00 PM To: 'EvaD_at_TFMC.co.za'

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> www.marshalsoftware.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: CHAN Chor Ling Catherine (CSC)
  INET: clchan_at_nie.edu.sg

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: 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).

Received on Tue Apr 02 2002 - 23:13:19 CST