question on Hash Joins

[Veeru71]

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

[Serge Rielau]

Veeru71 wrote:

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

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

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Jean-Marc Blaise]

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

Cheers,

Jean-Marc

"Serge Rielau" <sr*****@ca.ibm.coma écrit dans le message de
news:52*************@mid.individual.net...

Veeru71 wrote:

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

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

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Serge Rielau]

Serge Rielau wrote:

Veeru71 wrote:

>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
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab