question on Hash Joins

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Veeru71 - 30 Jan 2007 13:01 GMT

I am not able to really understand the explain plan output in case of
hash joins.
When tables A & B are being joined, how do I know which table is
being
hashed during first phase (build phase) ?

Is it true that the indexes will never be used in case of hash joins
e?
(I aways see "Relation Scan" in the explain output).
Thanks
Dave

Reply to this Message

Serge Rielau - 30 Jan 2007 14:59 GMT

> I am not able to really understand the explain plan output in case of
> hash joins.
[quoted text clipped - 7 lines]
> Thanks
> Dave

In db2exfmt the left branch is the build, the right branch is the probe.
If indices were considered beneficial to compute the join, then DB2
would most likely pick a nested loop (with an index probe) or a merge
join (based on the index order).
The only scenario I can imagine where an index scan may win would be a
covering index. (ISCAN without a FETCH).

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

Jean-Marc Blaise - 30 Jan 2007 23:23 GMT

Maybe an index would also be picked up if it is a block index of a MDC.

Cheers,

Jean-Marc

> > I am not able to really understand the explain plan output in case of
> > hash joins.
[quoted text clipped - 17 lines]
> Cheers
> Serge

Reply to this Message

Serge Rielau - 31 Jan 2007 12:53 GMT

>> I am not able to really understand the explain plan output in case of
>> hash joins.
>> When tables A & B are being joined, how do I know which table is being
>> hashed during first phase (build phase) ?
> In db2exfmt the left branch is the build, the right branch is the probe.

I stand corrected: RIGHT = BUILD, LEFT = PROBE. Like an NL join where
the inner is temped (built) and the outer then probes the inner...
Thanks to Miro for pointing that out.

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message