Avoiding Duplicates and Transitive Operations

From: MacGregor, Ian A. <ian_at_SLAC.Stanford.EDU>
Date: Fri, 27 Jul 2001 15:49:17 -0700
Message-ID: <F001.003580FA.20010727155601@fatcity.com>

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

Received on Fri Jul 27 2001 - 17:49:17 CDT