Re: Hash join order

Richard Kuhler wrote:
>
> Is there a significance to the order in which objects are listed in an
> execution plan for a HASH JOIN. For example, are these two plans really
> different ...
>
> SELECT STATEMENT
> HASH JOIN
> TABLE ACCESS (FULL) OF 'TABLE_A'
> TABLE ACCESS (FULL) OF 'TABLE_B'
>
> SELECT STATEMENT
> HASH JOIN
> TABLE ACCESS (FULL) OF 'TABLE_B'
> TABLE ACCESS (FULL) OF 'TABLE_A'
>
> Is this indicating which table will actually have the hash produced and
> which will be iterated through? If so, how can you force the order in
> situations where you are hash joining a table and a partial result (two
> tables joined first before the hash)? For example, I want to be able to
> switch between plans like this but can't seem to do it ...
>
> 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'
>
> Thanks in advance for the help.
>
> Thanks,
> Richard Kuhler

Yes there is a difference, you might wish to look at the ORDERED hint.

hth
connor

-- 
=========================
Connor McDonald
http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue"