Re: Hash join order

From: David Fitzjarrell <oratune_at_msn.com>
Date: 28 Mar 2003 11:56:23 -0800
Message-ID: <32d39fb1.0303281156.428beaa4@posting.google.com>

"Stephan Bressler" <stephan.bressler_at_siemens.com> wrote in message news:<b6166s$8fv$1_at_news.mch.sbs.de>...
> "Richard Kuhler" <noone_at_nowhere.com> wrote in message
> news:4gRga.8018$Hx.2643210_at_twister.socal.rr.com...
> > Connor McDonald wrote:
> > > > SELECT STATEMENT
> > > > HASH JOIN
> > > > TABLE ACCESS (FULL) OF 'TABLE_A'
> > > > NESTED LOOPS
> > > > TABLE ACCESS (FULL) OF 'TABLE_B'
> > > > TABLE ACCESS (BY INDEX ROWID) OF 'TABLE_C'
> > > > INDEX (UNIQUE SCAN) OF 'TABLE_C_IDX'
> > > >
> > > > SELECT STATEMENT
> > > > HASH JOIN
> > > > NESTED LOOPS
> > > > TABLE ACCESS (FULL) OF 'TABLE_B'
> > > > TABLE ACCESS (BY INDEX ROWID) OF 'TABLE_C'
> > > > INDEX (UNIQUE SCAN) OF 'TABLE_C_IDX'
> > > > TABLE ACCESS (FULL) OF 'TABLE_A'
> > > >
> > > Yes there is a difference, you might wish to look at the ORDERED hint.
> >
> > I'm very familiar with the ORDERED hint but I don't see how you can
> > achieve the first plan with this hint. The problem is the ordered hint
> > does not seem sufficient to say ...
> >
> > Start with TABLE_A for the HASH JOIN
> > For the 2nd table of the HASH JOIN take the view from the join of
> > TABLE_B and TABLE_C.
> >
> > An order of TABLE_B, TABLE_C, TABLE_A gives the second plan from above.
> > An ORDER of TABLE_A, TABLE_B, TABLE_C gives a plan that looks something
> > like this ...
> >
> > SELECT STATEMENT
> > TABLE ACCESS (BY INDEX ROWID) OF 'TABLE_C'
> > NESTED LOOPS
> > HASH JOIN
> > TABLE ACCESS (FULL) OF 'TABLE_A'
> > TABLE ACCESS (FULL) OF 'TABLE_B'
> > INDEX (RANGE SCAN) OF 'TABLE_C_I1'
> >
> > I've tried dozens of different hint combinations. Unfortunately, I'm
> > pretty much convinced it's just impossible to produce the first plan
> > from above. Here's the test script if someone wants to prove me wrong
> > ...
> >
> >
> > create table table_a (a_id number);
> > create table table_b (b_id number, a_id number);
> > create table table_c (c_id number, b_id number);
> >
> > create index table_c_i1 on table_c(b_id);
> >
> > set autotrace trace explain
> >
> > select /*+ ordered use_hash(table_a) use_nl(table_c) */
> > *
> Hi,
> try
> /*+ use_hash(table_b, table_a) */
>
> Stephan

13:52:55 DBTECH> l
  1 select /*+ ordered use_hash(table_a, table_b) use_nl(table_c) */   2 *
  3 from

  4      table_b,
  5      table_c,
  6      table_a
  7  where table_a.a_id = table_b.a_id
  8*     and table_c.b_id = table_b.b_id

13:53:57 DBTECH> / Execution Plan

---

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=166 Card=164 Bytes=1
          0660)

   1    0   HASH JOIN (Cost=166 Card=164 Bytes=10660)
   2    1     NESTED LOOPS (Cost=165 Card=164 Bytes=8528)
   3    2       TABLE ACCESS (FULL) OF 'TABLE_B' (Cost=1 Card=164 Byte
          s=4264)

   4    2       TABLE ACCESS (FULL) OF 'TABLE_C' (Cost=1 Card=164 Byte
          s=4264)

   5    1     TABLE ACCESS (FULL) OF 'TABLE_A' (Cost=1 Card=164 Bytes=
          2132)

13:54:19 DBTECH> I do believe this is what you want, Richard. I'd come across this just before I saw Stephen's suggestion.

David Fitzjarrell Received on Fri Mar 28 2003 - 13:56:23 CST