Hash Anti Join Requirements

From: Larry Elkins <elkinsl_at_flash.net>
Date: Mon, 04 Mar 2002 16:08:28 -0800
Message-ID: <F001.0041EEB9.20020304160828@fatcity.com>

Listers,

Some conditions have to be met before a HASH ANTI JOIN can be used (either by hint or by setting always_anti_join=hash). The ones I have come across are (1) the column in the NOT IN query must be NOT NULL (or constraining criteria specified that says NOT NULL for the column, and that's probably what you want anyway), and, (2) there cannot be more than one table in the NOT IN sub-query (and we have workarounds for that as well -- flip into an in-line view, use no_merge and use_hash if necessary, and use the outer-join and key is null anti-join trick).

Here's what the docs say about the conditions:

"The optimizer uses a nested loops algorithm for NOT IN subqueries by default, unless the initialization parameter ALWAYS_ANTI_JOIN is set to MERGE or HASH and various required conditions are met that allow the transformation of the NOT IN subquery into a sort-merge or hash anti-join..."

Note the "and various required conditions are met that allow the transformation...". I've never found a source that specified those conditions. I came up with the two conditions above based on experience but even then I don't know they are set in stone.

I ask because I am working with some folks who are frightened by anti-joins. And in their case, a lot of queries would benefit, and have been demonstrated, from the use HASH-AJ's. I would just like to be able to give them something more concrete about when a HASH-AJ is possible other than the two things I listed above (which may or may not be true) and the generic comment in the Oracle docs.

Regards,

Larry G. Elkins
The Elkins Organization Inc.
elkinsl_at_flash.net
214.954.1781

--

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). Received on Mon Mar 04 2002 - 18:08:28 CST