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