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.
Received on Sat May 03 2003 - 01:11:41 CDT
