Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: MINUS vs. NOT EXISTS
"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:28:10 CDT