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

From: Ryan <rgaffuri_at_cox.net>
Date: Fri, 02 May 2003 21:45:16 GMT
Message-ID: <MFBsa.10720$g41.530456@news1.east.cox.net>

here is the query ane explain plan....

1 SELECT 1
2 FROM tab1

where col1 not in
4 (select /*+ HASH_AJ */ to_char(col2) 5 from tab2)

SQL> / Execution Plan

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=499 Card=97653 Bytes
          =683571)

   1    0   COUNT (STOPKEY)
   2    1     FILTER
   3    2       INDEX (FAST FULL SCAN) OF 'TAB1.COL1 Index
          ' (NON-UNIQUE) (Cost=499 Card=97653 Bytes=683571)

   4    2       INDEX (FULL SCAN) OF 'COL2.TAB2 Index (UNIQUE) (Cost=43
          51 Card=97627 Bytes=488135)

Statistics

          0  recursive calls
          4  db block gets
       4366  consistent gets
        807  physical reads
          0  redo size
        186  bytes sent via SQL*Net to client
        248  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

"Ryan Gaffuri" <rgaffuri_at_cox.net> 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 Fri May 02 2003 - 16:45:16 CDT