Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: RE: MINUS VS NOT IN
Mark,
The start time (when you press ENTER also matters here)... take care.
How about 'set timing on'?
Regards,
- Bhat
-----Original Message----- From: dgoulet_at_vicr.com [mailto:dgoulet_at_vicr.com] Sent: Saturday, February 10, 2001 4:05 AM To: Multiple recipients of list ORACLE-L Subject: Re:RE: MINUS VS NOT IN 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" <mark_at_cool-tools.co.uk> Date: 2/9/2001 11:25 AM Jared, Cool little trick with your SQL prompt there, care to sharehow 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:faster?
> 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
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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: dgoulet_at_vicr.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: INET: LBhat_at_LEVI.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).Received on Mon Feb 12 2001 - 00:59:12 CST
![]() |
![]() |