By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,778 Members | 1,083 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,778 IT Pros & Developers. It's quick & easy.

question on Hash Joins

P: n/a
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

Jan 30 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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
Jan 30 '07 #2

P: n/a
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

Jan 30 '07 #3

P: n/a
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
Jan 31 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.