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

> -----Original Message-----
>
> In fact, I would be
> curious to compare the join on the inline-view with a MINUS to the hash
> anti-join. Assuming all correct indexes, it is comparing two index fast
> full scans plus sort plus index search to two table full scans (more or
> less).

There was a time when I used that approach. Not talking about MINUS in general since I still find cases for that, but flipping a NOT IN / NOT EXISTS into an IN or EXISTS sitting on top of a MINUS in a sub-query, just like in your example (except you used an in-line view):

     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

But this was back in 5, 6, and early 7 and I had to use a sub-query since in-line views weren't there yet (7.2, officially?). And though the in-line view gives us more join methods to choose from, I still haven't been able to produce a case where it was the best. The biggest thing I see is the double hit on table1 (table scan or it's indexes (full or fast full)). May have had something to do with the way CBO handle NOTS back in the earlier versions? NOT real sure.

Anyway, I remember a long time ago recommending the above (but using a sub-query). And I think it was Tim Sawmiller who asked why the heck I did it that way. I said because it's "faster" than a NOT IN / NOT EXISTS. He asked for an example, and I couldn't provide one. His point was well taken (we were up to V7 by then and I'm guessing pre 7.2 since I didn't try the in-line view route). In fact, someone else also recommended the approach, and he had asked both of us to provide an example, and neither one of us could make it the faster approach. So yeah, I started to re-think that approach.

FWIW, I had to tune a package not too long ago that used the construct above for all their anti-joins. Most were running for minutes, anywhere from 5 minutes to 30 minutes. Going to a NOT IN (with always_anti_join set to hash) dropped all of them to a matter of seconds (and I think I had to go to a correlated NOT EXISTS on a couple others due to their nature, and the outer join null key for a couple of others since they didn't meet the requirement for a hash-aj). But knowing the technique above has been useful in the past, I have tried on several occasions to build test cases where it beat all other approaches. I haven't been able to build one. Obviously that doesn't mean there aren't cases where it could be the best, maybe just my lack of successfully thinking of and building a case where it would be the best. So, I am still curious if there are still conditions, and what those conditions are, where that approach is "the best". I just haven't been able to come up with one -- maybe another sign of my aging and losing my faculties. But at least I didn't work on V4 or prior like some of the *really* old folks on the list ;-)

Larry G. Elkins
elkinsl_at_flash.net

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Larry Elkins
  INET: elkinsl_at_flash.net

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