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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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
[EMAIL PROTECTED] 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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
