Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

SQL: MINUS vs. NOT EXISTS

1,162 views
Skip to first unread message

Vikas Agnihotri

unread,
Jul 4, 2001, 8:25:10 PM7/4/01
to
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

Sybrand Bakker

unread,
Jul 5, 2001, 12:22:17 AM7/5/01
to

"Vikas Agnihotri" <onlyfor...@yahoo.com> wrote in message
news:77e87b58.01070...@posting.google.com...

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

unread,
Jul 6, 2001, 4:35:23 AM7/6/01
to

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

0 new messages