Oracle-L: RE: Follow-up :Long running SQL Problem?

From: CHAN Chor Ling Catherine (CSC) <clchan_at_nie.edu.sg>
Date: Wed, 03 Apr 2002 17:58:20 -0800
Message-ID: <F001.0043A8FE.20020403175820@fatcity.com>

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:sfaroult_at_oriole.com]
		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: sfaroult_at_oriole.com

		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: 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 Wed Apr 03 2002 - 19:58:20 CST