Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: MINUS vs. NOT EXISTS

Re: MINUS vs. NOT EXISTS

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Sat, 21 Jul 2001 21:28:10 GMT
Message-ID: <tk7qnmp1rea3e5@beta-news.demon.nl>

"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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US