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

Ryan wrote:

> 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?

I'm not sure what you expect but if it is raw speed don't use NOT IN ... use NOT EXISTS

Something like:

SELECT srvr_id
FROM servers s
WHERE NOT EXISTS (
   SELECT srvr_id
   FROM serv_inst i
   WHERE s.srvr_id = i.srvr_id);

And I seriously question the validity of anyone that says any THING is faster than any other THING. Almost always it depends on the data.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)