does
select
--
Niall Litchfield
Oracle DBA
Audit Commission UK
"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message
news:1071075338.477755_at_yasure...
>
> In my testing the execution plans are often different but that doesn't
> change the fact that it is a myth. I have a test case I use with my
> students that contains the following three statements:
>
> SELECT srvr_id
> FROM servers
> WHERE srvr_id IN (
> SELECT srvr_id
> FROM serv_inst);
>
> SELECT srvr_id
> FROM servers s
> WHERE EXISTS (
> SELECT srvr_id
> FROM serv_inst i
> WHERE s.srvr_id = i.srvr_id);
>
> SELECT DISTINCT srvr_id
> FROM servers
> WHERE srvr_id NOT IN (
> SELECT srvr_id
> FROM servers
> MINUS
> SELECT srvr_id
> FROM serv_inst);
>
> All have different plans and I don't think anyone would want to guess
> which has the lower cost.
>
what about
SELECT s.srvr_id
from servers s,serv_inst i
where s.srvr_id=i.srvr_id;
for you or
SELECT s.srvr_id
from servers s,serv_inst i
where s.srvr_id=i.srvr_id(+)
and i.srvr_id is null;
for the not in? (and yes I probably have got the outer join wrong).
--
Niall Litchfield
Oracle DBA
Audit Commission UK
Received on Thu Dec 11 2003 - 03:19:03 CST
