Z/OS DB2 V7::Nested Loop Join - to - Hybrid Join

Vijayababu Sriramulu

Z/OS DB2 V7::Nested Loop Join - to - Hybrid Join
Dear list,

We have a long running SQL in production. Here are the related
(Simplified) details.

Table-1 (Total number of rows in this table is 100)

Column1 decimal(10,0)
Column2 char(100)

Index-1 on Table-1 above

Column1 Asc

Table-2 (Total number of rows in this table is 41 million)

Column-3 decimal(10,0)
Column4 ...
Other columns exist..
..
..

Index-2 on Table-2 above (Primary index)
Column3 Asc

Here is my SQL

SELECT Column3, Other columns......
FROM Table-2 A
WHERE Column3 in
(SELECT Column1 FROM Table-1)

This was not using any join methods as shown by the METHOD column of
EXPLAIN with the following entries.


Method TABLE NAME Access Match-Cols INDEX NAME
------------------------------------------------------------------------
-----------------------------
0 Table2 N 1 Index-2
0 Table1 I 0 Index-1
3 (This is for the SORT due to the IN predicate)


Then I changed the SQL to go for a join, removing the IN predicate. This
forced the join to become a Nested loop join and the query performed
better.

SELECT Column3
FROM Table-2 A
WHERE exists
(SELECT 1 FROM Table-1 B where A.Column3 = B.Column1)

Method TABLE NAME Access Match-Cols INDEX NAME
------------------------------------------------------------------------
-----------------------------
0 Table1 I 0 Index-1
(Index Only Access)
1 Table2 I 1 Index-2

Now my questions are as follows.

1. Can this query be tuned further. I am pretty sure I have not tuned
this query to the Nth possible degree
2. What are the DB2 config parms this query will be depending on (like
bufferpool/sort pool sizes etc.)
3. Is it possible to change this query to hybrid join or a merge scan
join methods ?
4. Is it always true that Merge scan join will perform better than the
nested loop join and hybrid join can perform better than merge scan join
(Considering the list prefetch access in case of hybrid joins)

Please throw me some insight on this query.

As always, thanks in advance for all your time and suggestions.

Thanks & Regards
Vijay


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Richard Fazio

Re: Z/OS DB2 V7::Nested Loop Join - to - Hybrid Join
(in response to Vijayababu Sriramulu)
It seems that your query is based upon a very limited list of values (100 or less).

Personally, I would just do an inner join of the two, but why not take the "TABLE 1" out of the equation (and a join). Try selecting from "TABLE 2" with a hard coded list. Witness the I/O and CPU extracting from TABLE-2 as a base line. You should have a best case scenario to judge if the optimizer is working in your favor ...or not.

Perhaps there are other factors in the degradation (locking, buffers, extents...normal stuff).

In response to your questions:

#1 see above
#2 you can't tune your OS for one query...and we can't tune based upon seeing only one query (check for rid pool failures...If you run out of rid pool space, DB2 falls back to a tablespace scan).
#3 over the years I have learned that Hybrid join is not evil (any more). I have done some testing on this and IF (notice the big IF...I'd make it flash if I could) YOU HAVE ACCURATE AND COMPLETE STATISTICS....the hybrid join is probably the way to go (try turning off with a HINT...see how it performs).
#4 "Always" and "Never" are dangerous. So my answer: "Yes" if the MS join is faster, "No" if the "NL" join is faster, "Maybe" if they are close. There's no hard/fast rule on any access path. That's why IBM has several.

Best of luck,
fa

Rich Fazio
Information Architecture

TransUnion, LLC
Marketing Services, 8th Floor
555 West Adams St. Chicago, IL 60661
Phone (312) 985-3270 Fax (312) 466-6453

Talk to teach - Listen to learn
z
>>> [login to unmask email] 2005-11-29 4:41:36 AM >>>


Dear list,
We have a long running SQL in production. Here are the related (Simplified) details.
Table-1 (Total number of rows in this table is 100)
Column1 decimal(10,0)
Column2 char(100)
Index-1 on Table-1 above
Column1 Asc
Table-2 (Total number of rows in this table is 41 million)
Column-3 decimal(10,0)
Column4 *
Other columns exist..
..
..
Index-2 on Table-2 above (Primary index)
Column3 Asc
Here is my SQL
SELECT Column3, Other columns*...
FROM Table-2 A
WHERE Column3 in
(SELECT Column1 FROM Table-1)
This was not using any join methods as shown by the METHOD column of EXPLAIN with the following entries.

Method TABLE NAME Access Match-Cols INDEX NAME
-----------------------------------------------------------------------------------------------------
0 Table2 N 1 Index-2
0 Table1 I 0 Index-1
3 (This is for the SORT due to the IN predicate)
Then I changed the SQL to go for a join, removing the IN predicate. This forced the join to become a Nested loop join and the query performed better.
SELECT Column3
FROM Table-2 A
WHERE exists
(SELECT 1 FROM Table-1 B where A.Column3 = B.Column1)
Method TABLE NAME Access Match-Cols INDEX NAME
-----------------------------------------------------------------------------------------------------
0 Table1 I 0 Index-1 (Index Only Access)
1 Table2 I 1 Index-2
Now my questions are as follows.
1. Can this query be tuned further. I am pretty sure I have not tuned this query to the Nth possible degree
2. What are the DB2 config parms this query will be depending on (like bufferpool/sort pool sizes etc.)
3. Is it possible to change this query to hybrid join or a merge scan join methods ?
4. Is it always true that Merge scan join will perform better than the nested loop join and hybrid join can perform better than merge scan join (Considering the list prefetch access in case of hybrid joins)
Please throw me some insight on this query.
As always, thanks in advance for all your time and suggestions.
Thanks & Regards
Vijay --------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm