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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).