Re: Why the "where NOT EXISTS" does not work?

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Fri, 26 Oct 2001 20:25:50 +0200
Message-ID: <ttjdh7il3c9tc9@corp.supernews.com>

"Roy Sette" <kl7_at_rahul.net> wrote in message news:9rc8md$o4u$1_at_samba.rahul.net...
> Our distinguished colleague, "C Chang" scribed:
>
> >Which Proccess is more efficient?
>
> >select a from A_Table
> >minus
> >select a from B_table
>
> >or
>
> >select a from test1
> >where not exists
> >(select 1 from test2
> > where test1.a = test2.b)
>
>
> The second construction is a correlated subquery, which I've
> been told is inefficient, owing to exhaustive comparisons.
> Does the 'minus' set operator do any optimization? - not sure.
>
>
> --
> Roy
> kl7_at_rahul.net (USA)

The MINUS involves a sort merge operation

The general scenario for 1 is
perform a full table scan on table_a using db_file_multiblock_read_count (so read 64k at a time in a well-tuned database) do the same for table b
sort merge them removing duplicates

The second scenario performs lookups of individual keys, one block at a time.
The I/O for 1 will be way lower than the I/O for 2.

Hth,
Sybrand Bakker
Senior Oracle DBA Received on Fri Oct 26 2001 - 13:25:50 CDT