Oracle-L: RE: Do you ever have days where you dont want to think ?

From: Nicoll, Iain (Calanais) <iain.nicoll_at_calanais.com>
Date: Fri, 17 May 2002 14:13:25 -0800
Message-ID: <F001.00463CAB.20020517141325@fatcity.com>

I'm not sure an index would ever be used with not in (in seems to be bad enough). Not exists would probably be quicker though it'd probably be reasonable still to do a full table scan of a.

Personally I prefer the likes of minus though it'd be a bit convoluted here e.g.

select a.f1, a.f2, a.f3, a.f4
from a,

(select a.f1

     from table1 a
     minus
     select b.n1 
     from b) s

where a.f1 = s.f1

-----Original Message-----
Sent: Friday, May 17, 2002 10:23 PM
To: Multiple recipients of list ORACLE-L

I just just wanna go lie on a beach naked on some remote island far far away and not think of anything for a month.

Here is the issue.....

I have a query that looks like this ...

select a.f1, a.f2, a.f3, a.f4 from table1 a where a.f1 not in
( select b.n1 from b );

there is a primary key index on b.n1
there is a concatenated primary key index on a.f1,a.f2,a.f3 there is a non-unique index on a.f1

the query shows that the index is being used on table b, but no indexes are being used on table a.

Mike
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Johnson, Michael
INET: Michael.Johnson_at_oln-afmc.af.mil

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Nicoll, Iain (Calanais)
INET: iain.nicoll_at_calanais.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------