Mark,

    It's a pretty simple one, cause I do it all the time.  check out the 'set
prompt' command in SQL*Plus and 'set time on'.

Dick Goulet

____________________Reply Separator____________________
Author: "Mark Leith" <[EMAIL PROTECTED]>
Date:       2/9/2001 11:25 AM

Jared,

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

Cheers

Mark

-----Original Message-----
[EMAIL PROTECTED]
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@jks02 SQL>
09:09:58 jkstill@jks02 SQL> create table c1 as
09:09:58   2  select * from dba_objects;

Table created.

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

Table created.

09:10:00 jkstill@jks02 SQL>
09:10:00 jkstill@jks02 SQL>
09:10:00 jkstill@jks02 SQL> delete from
09:10:00   2  c2 where rownum < 201;

200 rows deleted.

09:10:00 jkstill@jks02 SQL>
09:10:00 jkstill@jks02 SQL> commit;

Commit complete.

09:10:00 jkstill@jks02 SQL>
09:10:00 jkstill@jks02 SQL>
09:10:00 jkstill@jks02 SQL> set autotrace on
09:10:01 jkstill@jks02 SQL>
09:10:01 jkstill@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@jks02 SQL>
09:10:03 jkstill@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@jks02 SQL>
09:19:48 jkstill@jks02 SQL>
09:19:48 jkstill@jks02 SQL> spool off

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mark Leith
  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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  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).

Reply via email to