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
[EMAIL PROTECTED]
-----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@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: MacGregor, Ian A.
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).
