Re: Hash join order

From: Stephan Bressler <stephan.bressler_at_siemens.com>
Date: Fri, 28 Mar 2003 10:57:12 +0100
Message-ID: <b6166s$8fv$1@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 Received on Fri Mar 28 2003 - 03:57:12 CST