Oracle-L: The results are in ... re: not thinking on a deserted nude beach

From: Johnson, Michael <Michael.Johnson_at_oln-afmc.af.mil>
Date: Mon, 20 May 2002 15:18:23 -0800
Message-ID: <F001.004655DD.20020520151823@fatcity.com>

I ran the query over again and here are the results. It appears the anti join solution is the one. suit yourself. I like Jareds / Larrys solution note : Not Exists produced the same result as Not In as the index remained suppressed !

Original Query

Execution Plan

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=15 Card=106 Bytes=19
          398)

   1    0   FILTER
   2    1     TABLE ACCESS (FULL) OF 'table2' (Cost=15 Card=106
           Bytes=19398)

   3    1     INDEX (RANGE SCAN) OF 'table1_PK' (UNIQUE) (
          Cost=2 Card=2 Bytes=18)

Statistics

          0  recursive calls
          2  db block gets
       6468  consistent gets
          0  physical reads
          0  redo size
     322140  bytes sent via SQL*Net to client
       2498  bytes received via SQL*Net from client
         97  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1410  rows processed

the Anti Join (+) solution ....

Execution Plan

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=50 Card=2739 Bytes=5
          50539)

   1    0   FILTER
   2    1     HASH JOIN (OUTER)
   3    2       TABLE ACCESS (FULL) OF 'Table2' (Cost=15 Card=2
          103 Bytes=384849)

   4    2       TABLE ACCESS (FULL) OF 'Table1' (Cost=14 C
          ard=3361 Bytes=60498)

Statistics

          0  recursive calls
          4  db block gets
        450  consistent gets
          0  physical reads
          0  redo size
     322140  bytes sent via SQL*Net to client
       2489  bytes received via SQL*Net from client
         97  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1410  rows processed

the inline view using minus solution

Execution Plan

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=173 Card=7037 Bytes=
          1351104)

   1    0   HASH JOIN (Cost=173 Card=7037 Bytes=1351104)
   2    1     VIEW (Cost=145 Card=5464 Bytes=49176)
   3    2       MINUS
   4    3         SORT (UNIQUE)
   5    4           TABLE ACCESS (FULL) OF 'Table2' (Cost=15 Ca
          rd=2103 Bytes=18927)

   6    3         SORT (UNIQUE)
   7    6           TABLE ACCESS (FULL) OF 'Table1' (Cost=
          14 Card=3361 Bytes=30249)

   8    1     TABLE ACCESS (FULL) OF 'Table2' (Cost=15 Card=210
          3 Bytes=384849)

Statistics

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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Johnson, Michael 
  INET: Michael.Johnson_at_oln-afmc.af.mil

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 May 20 2002 - 18:18:23 CDT