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

Mike,

WheretheheckisOLN-AFMC? Okie City?

Anyway, it seems to me that the non-unique index on a.f1 is a waste of space as a.f1 is the first field of the primary key index.

Have you tried a hint?

	select  /*+ index(table1 Table1_PK_indexname) */ 
	a.f1, a.f2, a.f3, a.f4 
	from table1 a
	where a.f1 not in
         ( select b.n1 from b );

My assumption is that you have a performance problem. How many records and how long does it take? Maybe show us the entire explain plan?

Jerry Whittle
ACIFICS DBA
NCI Information Systems Inc.
jerome.whittle_at_scott.af.mil
618-622-4145

> -----Original Message-----
> From: Johnson, Michael [SMTP:Michael.Johnson_at_oln-afmc.af.mil]
>
> 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: Whittle Jerome Contr NCI
  INET: Jerome.Whittle_at_scott.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).