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

"Johnson, Michael" wrote:
>
> 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

I don't think that lying naked on a beach would help, but hints and the like could.
I presume that table1 is pretty big. Larry Elkins could tell you that the HASH_AJ hint inside the subquery could work wonders. Another usually efficient solution (which in facts often boils down to the same thing as the hash anti-join hint in terms of execution plan) would be

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

         b
    where a.f1 = b.n1 (+)
      and b.n1 is null

Something else which could be contemplated if f1 is indexed could be

     select a.f1, a.f2, a.f3, a.f4
     from (select f1
           from table1
           minus
           select n1
           from b) a2,
          table1 a
     where a.f1 = a2.f1

Might require the ORDERED hint. I cannot tell you 'this is the best', depends on volumes searched and volumes returned, but one of those things might work well in your case. And ignore any advice to use NOT EXISTS unless you have an additional pretty selective condition on table1.

-- 
Regards,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.com

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).