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: Mark Leith <mark_at_cool-tools.co.uk>
Date: Fri, 09 Feb 2001 12:01:20 -0800
Message-ID: <F001.002B0B03.20010209112549@fatcity.com>

Jared,

Cool little trick with your SQL prompt there, care to share how you did it?

Cheers

Mark

-----Original Message-----
jkstill_at_cybcon.com
Sent: Friday, February 09, 2001 05:35
To: Multiple recipients of list ORACLE-L

On Thu, 8 Feb 2001, Peter Hazelton wrote:

> 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?

Query 1 is a simple set operation inside the database, and therefore is rather fast.

Query 2 requires doing a lookup in ptca for each row in inpatient; very ineffecient.

Somebody mentioned a join taking place, but this is actually an anti join.

If you had an appropriate index on ptca, a NOT EXISTS query would be much faster than the NOT IN.

Below you will find the execution paths for each query ( no indexes )

Query 1 ran in a few seconds.

Query 2 took several minutes.

Jared


09:09:58 jkstill_at_jks02 SQL>
09:09:58 jkstill_at_jks02 SQL> create table c1 as
09:09:58   2  select * from dba_objects;

Table created.

09:10:00 jkstill_at_jks02 SQL>
09:10:00 jkstill_at_jks02 SQL> create table c2 as
09:10:00   2  select * from c1;

Table created.

09:10:00 jkstill_at_jks02 SQL>
09:10:00 jkstill_at_jks02 SQL>
09:10:00 jkstill_at_jks02 SQL> delete from
09:10:00   2  c2 where rownum < 201;

200 rows deleted.

09:10:00 jkstill_at_jks02 SQL>
09:10:00 jkstill_at_jks02 SQL> commit;

Commit complete.

09:10:00 jkstill_at_jks02 SQL>
09:10:00 jkstill_at_jks02 SQL>
09:10:00 jkstill_at_jks02 SQL> set autotrace on
09:10:01 jkstill_at_jks02 SQL>
09:10:01 jkstill_at_jks02 SQL> select object_name, owner from c1
09:10:01   2  minus
09:10:01   3  select object_name, owner from c2
09:10:01   4  /

OBJECT NAME                    OWNER
------------------------------ ----------
/1001a851_ConstantDefImpl      SYS
/1005bd30_LnkdConstant         SYS

/10076b23_OraCustomDatumClosur SYS
...

200 rows selected.

Execution Plan


          0
SELECT STATEMENT Optimizer=CHOOSE

          1                  0

  MINUS
          2                  1

    SORT (UNIQUE)
          3                  2
      TABLE ACCESS (FULL) OF 'C1'


          4                  1

    SORT (UNIQUE)
          5                  4
      TABLE ACCESS (FULL) OF 'C2'






Statistics


          0  recursive calls
        200  rows processed

09:10:03 jkstill_at_jks02 SQL>
09:10:03 jkstill_at_jks02 SQL> select object_name, owner
09:10:03   2  from c1
09:10:03   3  where (object_name, owner) not in
09:10:03 4 ( select object_name, owner from c2 ) 09:10:03 5 /
OBJECT NAME                    OWNER
------------------------------ ----------
/1001a851_ConstantDefImpl      SYS
/1005bd30_LnkdConstant         SYS

/10076b23_OraCustomDatumClosur SYS
...

200 rows selected.

Execution Plan


          0
SELECT STATEMENT Optimizer=CHOOSE

          1                  0

  FILTER
          2                  1

    TABLE ACCESS (FULL) OF 'C1'
          3                  1

    TABLE ACCESS (FULL) OF 'C2' Statistics
          0  recursive calls
        200  rows processed

09:19:48 jkstill_at_jks02 SQL>

09:19:48 jkstill_at_jks02 SQL>
09:19:48 jkstill_at_jks02 SQL> spool off
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: jkstill_at_cybcon.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: Mark Leith
  INET: mark_at_cool-tools.co.uk

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 Fri Feb 09 2001 - 14:01:20 CST

Original text of this message

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