It might be interesting to force a hash anti_join
instead of the default nested loops anti-join (which
is presumably what the 'not exists' is doing at
present) to see how the performance changes.
--
Jonathan Lewis
Host to The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Author of:
Practical Oracle 8i: Building Efficient Databases
See http://www.jlcomp.demon.co.uk/book_rev.html
Seminars on getting the best out of Oracle
See http://www.jlcomp.demon.co.uk/seminar.html
Screensaver or Lifesaver: http://www.ud.com
Use spare CPU to assist in cancer research.
Sybrand Bakker wrote in message ...
>
>"Vikas Agnihotri" <onlyforposting_at_yahoo.com> wrote in message
>news:77e87b58.0107041625.51bbbe37_at_posting.google.com...
>> Need to return all rows in Table A (200,000 rows) which do NOT exist
>> in Table B (10000 rows).
>>
>> Approach A:
>>
>> select * from tablea
>> minus
>> select * from tableb where pk=constant
>>
>> Approach B:
>> select * from tablea a
>> where not exists (select 1 from tableb b where a.pk=b.pk and
>> b.pk=constant)
>>
>> The results are the same but Approach A is an order of magnitude
>> faster!
>>
>> Why? I expected the NOT EXISTS approach would be faster versus doing
>> the MINUS with its implied sort/unique.
>>
>> Comments? Thanks
>
>No, in scenario A you are reading db_multiblock_read_count blocks at a
time,
>usually 8, so 64k ahead, with 1 I/O request.
>In scenario B you are processing single block I/O requests.
>
>Hth,
>
>Sybrand Bakker, Senior Oracle DBA
>
>
>
Received on Sat Jul 21 2001 - 16:29:57 CDT
