Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How to get a Hash Anti-Join to take?

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

From: Ryan <rgaffuri_at_cox.net>
Date: Sat, 03 May 2003 09:07:51 GMT
Message-ID: <HFLsa.12782$g41.821046@news1.east.cox.net>

"D.Y." <dyou98_at_aol.com> wrote in message news:f369a0eb.0305022211.1ca15dce_at_posting.google.com...
> 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.

ahhh..... not null thing never hit me.

daniel-- there is a special 'anti-join' plan that can get generated. Its supposed to have the best bench marks when doing anti-joins. Received on Sat May 03 2003 - 04:07:51 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US