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: optimal sql

Re: optimal sql

From: Alex Filonov <afilonov_at_yahoo.com>
Date: 9 Dec 2002 11:06:16 -0800
Message-ID: <336da121.0212091106.64ccfd8d@posting.google.com>


"Jim Kennedy" <kennedy-family_at_attbi.com> wrote in message news:<NK2J9.296467$QZ.44251_at_sccrnsc02>...
> Not exists stops when it finds the first match and not in gets the whole
> list.
> Jim
> "Christoph Seidel" <chris666.seidel_at_gmx.de> wrote in message
> news:at1ufn$vjojo$1_at_ID-143718.news.dfncis.de...
> > Martin Doherty wrote:
> > > NOT IN becomes very inefficient for many values.
> > >
> > > try NOT EXISTS instead.
> >
> > hm, where the hell is the difference?
> >
> >

If I understand it right, pk column is a primary key in the second table as well. In this case, there is no difference between not in and not exists. BTW, in some cases the most efficient statment would be:

select * from t1
where t1.pk in
  (select pk from t1
   minus
   select pk from t2)

If pk is indexed in both tables, subquery is very efficient because it uses index scan only. Received on Mon Dec 09 2002 - 13:06:16 CST

Original text of this message

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