Which of the method value given in a plan table row is better when a join is involved in tables?
- method=1, nested loop join
- method=2, merge scan join
- method=4, hybrid join
Asked
Active
Viewed 237 times
-1
Jens Mühlenhoff
- 14,565
- 6
- 56
- 113
-
Jens Muhlenhoff - Thank you for structuring my post. – Forums Data Apr 10 '13 at 14:33
-
Your question is pretty vague, you should edit it and describe what the data is (table schemas and indexes, count of rows, the sql query, etc.) – Jens Mühlenhoff Apr 10 '13 at 14:34
-
3In general none of them is "better", your real question is probably "What's the difference between them?". – Jens Mühlenhoff Apr 10 '13 at 14:40
-
Related question for SQL Server: http://stackoverflow.com/questions/2446927/sql-server-2008-join-hints – Jens Mühlenhoff Apr 10 '13 at 14:42
-
Are you talking about LUW or z? because hybrid does not exist in Z. Also, there are hash join, and in v10 Zig Zag – AngocA Apr 10 '13 at 15:34
-
@AngocA: [yeah, it does](http://pic.dhe.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db2z9.doc.perf/src/tpc/db2z_hybridjoinaccess.htm)? – bhamby Apr 10 '13 at 15:37
-
I understand it depends on various factors. Deviating a little from the question asked - where in the plan table can I see the overall execution time or individiual step execution time. Thanks – Forums Data Apr 10 '13 at 15:55
-
In answer to AngoCa - It is the Z – Forums Data Apr 10 '13 at 16:17
-
I typed wrong, hybrid does not exist in LUW, but in Z. – AngocA Apr 10 '13 at 20:33
1 Answers
0
The only "correct" answer is: it depends.
Read up on the Information Center article on interpreting access plans for more detail on how all the methods work.
bhamby
- 15,112
- 1
- 45
- 66