Re: Hash join order

From: Richard Kuhler <noone_at_nowhere.com>
Date: Fri, 28 Mar 2003 18:20:46 GMT
Message-ID: <2o0ha.8170$Hx.3647289@twister.socal.rr.com>

Stephan Bressler wrote:
> "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) */

Maybe I have a misunderstanding of the USE_HASH hint but that didn't work and doesn't seem correct. The Oracle documentation says "The USE_HASH hint causes Oracle to join each specified table with another row source, using a hash join." As I understand this, your hint actually says hash join TABLE_B to "another row source" AND hash join TABLE_A to "another row source". Oracle isn't interpreting that as hash join TABLE_A and TABLE_B to each other. So when you try that hint you get a plan like this ...

SELECT STATEMENT
  HASH JOIN
    HASH JOIN

      TABLE ACCESS (FULL) OF 'TABLE_C'
      TABLE ACCESS (FULL) OF 'TABLE_B'

    TABLE ACCESS (FULL) OF 'TABLE_A' Anybody else have an idea?

Richard Received on Fri Mar 28 2003 - 12:20:46 CST