RE: MINUS VS NOT IN

From: MacGregor, Ian A. <ian_at_SLAC.Stanford.EDU>
Date: Fri, 09 Feb 2001 13:13:17 -0800
Message-ID: <F001.002B0CCD.20010209131045@fatcity.com>

A few questions for Peter Hazelton Why the use of distinct in the set operation in query one when dupicates will be tossed by it? What's the purpose of the distinct in the subquery of query two?

For Jared, have you tried using a hash antijoin hint inside the subquery?

Ian MacGregor
Stanford Linear Accelerator Center
ian_at_slac.stanford.edu

-----Original Message-----
Sent: Friday, February 09, 2001 9:35 AM
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: MacGregor, Ian A.
  INET: ian_at_SLAC.Stanford.EDU

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 - 15:13:17 CST