Re: How to get a Hash Anti-Join to take?

Hash anti joins are fastest if your inner resultset is large, if small, a NL anti might be better given correct indexing, memory constraints on hash, etc.

I would wager that your col1 and col2 columns are not null, which is one requirement for hash anti.

These don't have to be defined at the table level, Oracle can figure it out from the query, like so:

SELECT col1

   FROM TAB1
   WHERE COL1 NOT IN
 (SELECT /*+ HASH_AJ */ to_char(col2)

    from tab2
WHERE COL2 IS NOT NULL)
WHERE COL1 IS NOT NULL;   hth

Jack Silvey

rgaffuri_at_cox.net (Ryan Gaffuri) wrote in message news:<1efdad5b.0305021019.75cbe57b_at_posting.google.com>...
> Im using the Guy Harrison tuning book and he says the hash anti-join
> is the fastest. What am I doing wrong? Im doing a nested loop index
> lookup?
>
> This is what I Do:
>
> Alter Session Set always_anti_join = HASH
>
> SELECT 1
> FROM TAB1
> WHERE COL1 NOT IN
> (SELECT /*+ HASH_AJ */ to_char(col2)
> from tab2
>
> Dont have the plan table in front of me. Its just a nested loop join
> with Index lookups. Any idea why?
Received on Sun May 25 2003 - 08:37:13 CDT