RE: Hash Anti Join Requirements

Greg,

I recognize that Guy ;-)

Someone else said back-channel that they thought Harrison's book mentioned the requirements. And since I had the book handy, I looked it up. Should have thought of looking there. Nonetheless, thanks for taking the time to type up the comments from the book. I appreciate it.

Item 1, CBO is a given for them. Item 2 I mentioned. Item 3, surely they don't correlate a NOT IN ;-) Item 4 about OR in the main query, I don't know that I had run into that, good thing to know. Item 5, always_anti_join (and always_semi_join) are set to HASH.

By the way, I mentioned in the original email that I though if you were joining tables in the sub-query, the HASH AJ couldn't be done. I proved myself wrong not long after with a few simple examples which I should have done prior to posting. I wonder if the case I ran into also had an OR in the main query that accounted for the inability to use the HASH_AJ? I'll have to go dig up that code up and see.

Now to convince more developers that a NOT IN isn't necessarily the kiss of death, hasn't been for quite some time, and there are times when it is preferred. But I also need to take care to point out the minor difference between NOT EXISTS and NOT IN and how nulls are handled and can cause different results -- had to help someone with that yesterday, why does NOT EXIST return results and the NOT IN doesn't!

And for what it's worth, always_anti_join and always_semi_join are undocumented parameters in 9i. And with a NOT IN, for example, the CBO will use stats and other criteria (if supplied) to decide whether to use an NL approach or a HASH AJ approach. Getting pretty interesting.

Regards,

Larry G. Elkins
elkinsl_at_flash.net
214.954.1781

> -----Original Message-----
> From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Greg Moore
> Sent: Tuesday, March 05, 2002 2:43 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: Hash Anti Join Requirements
>
>
> > working with some folks who are frightened by anti-joins
>
> Last Halloween I went as an anti-join. It was pretty scary.
>
> Harrison:
>
> "Performance from the hash antijoin was dramatically better than for any
> other [anti-join optimization] method we tried.
>
> ...
>
> To take advantage of Oracle's antijoin optimizations, the
> following must be
> true:
>
> - CBO optimization must be enabled
> - Antijoin columns must not be NULL, because of the table definition or a
> not null clause in the SQL
> - The subquery is not correlated
> - The parent query does not contain an OR clause
> - The db parm ALWAYS_ANTI_JOIN is set to MERGE or HASH, or a MERGE_AJ or
> HASH_AJ hint is in the subquery"
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Greg Moore

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