RE: Avoiding Duplicates and Transitive Operations

From: MacGregor, Ian A. <ian_at_SLAC.Stanford.EDU>
Date: Sun, 29 Jul 2001 09:01:15 -0700
Message-ID: <F001.00358757.20010729090601@fatcity.com>

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