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 [EMAIL PROTECTED] 214.954.1781 > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]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: [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).
