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