Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: MINUS VS NOT IN

Re: MINUS VS NOT IN

From: Charlie Mengler <charliem_at_mwh.com>
Date: Thu, 08 Feb 2001 14:25:14 -0800
Message-ID: <F001.002AFC16.20010208142603@fatcity.com>

Here is my speculation based upon my own experience. Many years ago I was a sysadmin whose system continuously had the CPU pegged at 100% busy. I determined that the primary culprit was a program the circuit board designers were running. The program compared a "net list" between a before & after change in layout of a circuit board. Each item in the net list was a source & destination pair for any given trace on the board. The tool that generated the lists was a GUI tool. However the designers made their changes based upon text files list which traces that needed to be changed.

The way the original program was coded was that it read the before net list for the 1st trace & read through all the traces for the after net list. If there was a match, the program did not list this particular trace. It then got the 2nd trace from the before net list & repeated the process. Once it had completed the pass through all the traces on the before net list, it had a report showing all the traces on the before board that did not exist on the after board. It then repeated the process for the after board to get a report of all traces on it that did not exist on the before board. Needless to say that this was not very efficient; because many, many, many passes through both lists were required.

I re-wrote the logic to read in both lists & order them in the process. Then I made a single pass through both lists comparing the each data pair against the other board. When the pairs matched each other I threw out both pairs. By only making one pass through each complete net list & was able to reduce a FOUR hour run to under TWO minutes!

I suspect that much the same happens when the MINUS is used. Oracle orders both collection or rows & then can make a single pass through each to produce the result set.

HTH & HAND! Peter Hazelton wrote:
>
> 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).

-- 
Charlie Mengler               Maintenance Warehouse  
charliem_at_mwh.com              10641 Scripps Summit Ct   
858-831-2229                  San Diego, CA 92131    
HOME DEPOT - The Big Boy's Toy store!!!!!!!!!!!!!!!!!
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Charlie Mengler
  INET: charliem_at_mwh.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).
Received on Thu Feb 08 2001 - 16:25:14 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US