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).
