Oracle-L: Re: Life without a correlated subquery

From: Daniel Fink <Daniel.Fink_at_Sun.COM>
Date: Mon, 10 Nov 2003 09:44:25 -0800
Message-ID: <F001.005D63AA.20031110094425@fatcity.com>

I did a quick test with autotrace and here are the results. The bottom line is that the inline query used 6 i/os, the subquery used 46. (I repeated this test multiple times, same result).

  1  select e1.rowid,
  2         e1.empno,
  3         e1.ename
  4  from emp e1,
  5       (select empno, min(rowid) min_rowid
  6        from emp
  7        group by empno) e2

8 where e1.empno = e2.empno
9* and e1.rowid != e2.min_rowid

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE 1 0 MERGE JOIN

   2    1     VIEW
   3    2       SORT (GROUP BY)
   4    3         TABLE ACCESS (FULL) OF 'EMP'
   5    1     FILTER
   6    5       SORT (JOIN)
   7    6         TABLE ACCESS (FULL) OF 'EMP'

Statistics

          0  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
       1096  bytes sent via SQL*Net to client
        651  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
         14  rows processed

SQL> l

  1  select e1.rowid,
  2         e1.empno,
  3         e1.ename

4 from emp e1
5 where e1.rowid not in (select min(e2.rowid) min_rowid

  6                         from emp e2
  7*                        where e1.empno = e2.empno)

SQL> / 14 rows selected.

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE 1 0 FILTER

   2    1     TABLE ACCESS (FULL) OF 'EMP'
   3    1     SORT (AGGREGATE)
   4    3       TABLE ACCESS (FULL) OF 'EMP'

Statistics

          0  recursive calls
          0  db block gets
         46  consistent gets
          0  physical reads
          0  redo size
       1096  bytes sent via SQL*Net to client
        651  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         14  rows processed

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Daniel Fink
  INET: Daniel.Fink_at_Sun.COM

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

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 Nov 10 2003 - 11:44:25 CST