| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Avoiding Duplicates and Transitive Operations
Ian,
love those "caffeine free moments", don't you?
As the project manager for one of the databases I messed up (you try accidentally dropping a production tablespace in the middle of the day when you only do cold backups and are in noarchivelog mode) said:
"that concludes your mistakes for the next 5 years"
Nice to get it out of the way, no?
Rachel
>From: "MacGregor, Ian A." <ian_at_SLAC.Stanford.EDU>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: RE: Avoiding Duplicates and Transitive Operations
>Date: Sun, 29 Jul 2001 09:06:01 -0800
>
>Mr. MacGregor would like to recall his posts on this subject. His brain
>has failed regression testing. He feels that Real Application Clusters
>will solve his lapses into silliness.
>
>Ian MacGregor
>Stanford Linear Accelerator Center
>ian_at_slac.Stanford.edu
>
>-----Original Message-----
>Sent: Friday, July 27, 2001 4:56 PM
>To: Multiple recipients of list ORACLE-L
>
>
>Suppose you have the following tables:
>
>SQL> select * from test1;
>
> C1 C2
>--------- ----------
> 1 Hello
>
>
>SQL> select * from test2;
>
> C1 C2
>--------- ----------
> 1 Bye
> 1 So long
>
>and you wanted to get distinct records from test1 wherever test1.c1 =
>test2.1
>
>You could
>
>Select distinct a.c1, a.c2 from ian.test1 a, ian.TEST2 B
> where a.c1 = b.c1
>/
>
> C1 C2
>-------- ----------
> 1 Hello
>
>real: 1531
>
>execution Plan
>---------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE
> 1 0 SORT (UNIQUE)
> 2 1 MERGE JOIN
> 3 2 SORT (JOIN)
> 4 3 TABLE ACCESS (FULL) OF 'TEST2'
> 5 2 SORT (JOIN)
> 6 5 TABLE ACCESS (FULL) OF 'TEST1'
>
>However anytime you see a distinct clause, you should think, "Hey !!!!
>there's probably something wrong here."
>
>A better way to handle the query is
>
>SELECT C1, C2 FROM ian.TEST1
>WHERE EXISTS (SELECT C1 FROM ian.TEST2)
>/
>
>SQL> SQL> SELECT C1, C2 FROM ian.TEST1
> 2 WHERE EXISTS (SELECT C1 FROM ian.TEST2)
> 3 /
>
> C1 C2
>--------- ----------
> 1 Hello
>
> real: 1578
>
>Execution Plan
>----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE
> 1 0 FILTER
> 2 1 TABLE ACCESS (FULL) OF 'TEST1'
> 3 1 TABLE ACCESS (FULL) OF 'TEST2')
> 3 /
>
> C1 C2
>--------- ----------
> 1 Hello
>
> real: 1578
>
>Execution Plan
>----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE
> 1 0 FILTER
> 2 1 TABLE ACCESS (FULL) OF 'TEST1'
> 3 1 TABLE ACCESS (FULL) OF 'TEST2'
>
>Neither table is indexed . Because of the low number of records in them.
>
>What I want to know is if
>
>SQL> SELECT C1, C2 FROM ian.TEST1
> 2 WHERE EXISTS (SELECT C1 FROM ian.TEST2)
>
>
>undergoes a transitive operation to
>
> SELECT C1, C2 FROM ian.TEST1
> 2 WHERE EXISTS (SELECT C1 FROM ian.TEST2
> where test1.c1 = test2.c1)
>
>/
>------------------------------------------------------------------------------------------------------------------------
>
>More generally is there any tool out there which shows exactly what the
>optimizer does with a query?
>
>
>Ian MacGregor
>Stanford Linear Accelerator Center
>ian_at_slac.stanford.edu
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: MacGregor, Ian A.
> INET: ian_at_SLAC.Stanford.EDU
>
>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: MacGregor, Ian A.
> INET: ian_at_SLAC.Stanford.EDU
>
>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: Rachel Carmichael INET: carmichr_at_hotmail.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 Sun Jul 29 2001 - 17:33:15 CDT
 |
 |