Re: Hash join order

If you want this:

> 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
> /

to produce this plan:

> > > 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'
then you need to include the hint

    swap_join_inputs(table_a)
in the select statement (you may also need to include an index(table_c) for the test case you published).

Alternatively you need write an inline view which joins table_b and table_c with a no_merge hint - but this means the B/C join will (probably) be turned into a physical result set and dumped to disc before being run through table_a.

The effect of the hints are:

    The only JOIN ORDER in the 10053 trace will be

            table_b table_c table_a

    When joining table_c to table_b, Oracle will try     nothing but a nested loop (and index if hinted)

    When joining table_a to the previous result set,     Oracle will use a hash join. But because of the     explicit swap hint, table_a will be used as the build     (hash) table, not the probe table.

Consequently you will get a plan that LOOKs as if it could only be generated from the order

    table_a, table_b, table_c

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

For one-day tutorials:
(see http://www.jlcomp.demon.co.uk/tutorial.html )

____UK_______April 8th
____UK_______April 22nd
____Denmark May 21-23rd
____USA_(FL)_May 2nd

Next dates for the 3-day seminar:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____UK_(Manchester)_May
____Estonia___June (provisional)
____USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

• Original Message ----- From: "Richard Kuhler" <noone_at_nowhere.com> Newsgroups: comp.databases.oracle.server Sent: 28 March 2003 05:41 Subject: Re: Hash join order