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

"D.Y." wrote:

> It depends. NOT EXISTS works best when your driving table is a lot smaller
> than the filter table. If both tables are large, anti hash join indeed is
> much faster, because less I/Os will be required to join each row. Ryan is
> doing the right thing if tab1 and tab2 are large tables. But Oracle can ignore
> the hash_aj hint if certain constraints are not met, such as col1 or col2 not
> being mandatory columns, etc. In that case, the work around is to add a NOT
> NULL condition for the join columns.
>
> Daniel Morgan <damorgan_at_exxesolutions.com> wrote in message news:<3EB31FA2.233B7AF9_at_exxesolutions.com>...
> > 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.

My last sentence was: "Almost always it depends on the data."

That was my point. A blanket statement that some 'thing' is faster is almost always incorrect unless specific conditions are specified.

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