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" <ORACLE-L_at_fatcity.com> 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: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 <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).

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Greg Moore
  INET: sqlgreg_at_pacbell.net

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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Kimberly Smith
  INET: ksmith2_at_myfirstlink.net

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