Re: Hash join order

From: Richard Kuhler <noone_at_nowhere.com>
Date: Fri, 28 Mar 2003 05:41:20 GMT
Message-ID: <4gRga.8018$Hx.2643210@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) */

    *
from

    table_b, 
    table_c,
    table_a 

where table_a.a_id = table_b.a_id

    and table_c.b_id = table_b.b_id
/

Thanks,
Richard Received on Thu Mar 27 2003 - 23:41:20 CST