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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
