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