RE: MINUS VS NOT IN

The reason the minus is so much faster is because it does a hash join. Try adding a /*+ hash_aj */ hint to the "not in" and you should get better response. Also, there is no need to put a distinct in the second select of the minus or the sub select of the "not in". Minus will do a sort distinct anyway. Here is the new sql.

select distinct icons

   from inpatient
 where icons not in (select /*+ hash_aj */ icons from ptca) /

> -----Original Message-----
> From: Peter Hazelton [SMTP:peterhazelton_at_hotmail.com]
> Sent: Thursday, February 08, 2001 3:03 PM
> To: Multiple recipients of list ORACLE-L
> Subject: MINUS VS NOT IN
>
> Hello all.
>
> I read a question concerning the use of the MINUS function earlier and I
> had
> not heard of it before today. After some experimenting with it, I was
> quite
> amazed at the speed of this function compared to using NOT IN.
>
> Considering the following:
>
> Query One
>
> select distinct icons from inpatient
> minus select distinct icons from ptca;
>
> Query 2
>
> select distinct icons from inpatient
> where icons NOT IN(select distinct icons from ptca)
>
> Query number one began to run in about 5 seconds whereas query 2 took
> forever to run. My question is why is the MINUS so much faster?
>
> My understanding of the NOT IN is that it probably builds the record set
> in
> the second part of the SQL query each time it comes to a new icons number
> in
> the first part of the query. Therefore, if there are one thousand records
> to
> be searched, it probably builds the record set one thousand times. Is this
>
> correct? If so, how does the MINUS function build and compare the record
> set?
>
> And finally, where do I get these wonderful little tidbits? Would a SQL
> tuning book exist that might talk about the strengths and limitations of
> different functions? I really appreciate your time and input.
>
> Petre Hazelton
> Halifax, Nova Scotia CANADA
> _________________________________________________________________________
> Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Peter Hazelton
> INET: peterhazelton_at_hotmail.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: Elliott, Patrick
  INET: Patrick.Elliott_at_bestbuy.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).