|
Problem IC63994
|
Status: Closed |
THE DB2 SQL QUERY OPTIMIZER MAY CHOOSE A POOR PERFORMING SORT-MERGE JOIN OPERATION UNDER CERTAIN CONDITIONS |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
In some cases, the DB2 SQL query optimizer (referred to
optimizer herein) may choose to perform a sort-merge join on an
effectively cartesian join which may not be an optimal decision
in some cases. For example, consider the following query:
select *
from T1, T2
where T1.A=1 and T1.A=T2.A and T1.B=T2.B and T1.C=T2.C
The two predicates "T1.A=1" and "T1.A=T2.A" imply a third
predicate, "T2.A=1". As a result, at the join operation, the
join
on T1.A=T2.A is effectively a cartesian product of two result
sets that have all 1s in the join columns.
The sort-merge join (MSJOIN) operation is limited to applying
only one of the three join predicates. This is clearly
indicated in the details of the MSJOIN operator when viewing the
explain of the query using the db2exfmt tool; e.g.
6) MSJOIN: (Merge Scan Join)
...
Predicates:
----------
42) Predicate used in Join
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 1
Predicate Text:
--------------
(Q1.A=Q2.A)
50) Residual Predicate
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 3.97033e-06
Predicate Text:
--------------
(Q1.B=Q2.B)
51) Residual Predicate
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 0.5
Predicate Text:
--------------
(Q1.C=Q2.C)
Of the 3 predicates, 1 is classified as "Predicate used in Join"
and the other two are "Residual".
In some cases, the optimizer may choose to perform the MSJOIN
using the effectively cartesian join predicate to perform the
sort-merge join, applying the potentially filtering predicates
as residuals. In some cases, this may not be an optimal
operation.
This APAR will improve the optimizer's handling of these
effectively cartesian join predicates. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* ALL *
****************************************************************
* PROBLEM DESCRIPTION: *
* In some cases, the DB2 SQL query optimizer (referred to *
* *
* optimizer herein) may choose to perform a sort-merge join on *
* an *
* effectively cartesian join which may not be an optimal *
* decision *
* in some cases. For example, consider the following query: *
* *
* select * *
* from T1, T2 *
* where T1.A=1 and T1.A=T2.A and T1.B=T2.B and T1.C=T2.C *
* *
* The two predicates "T1.A=1" and "T1.A=T2.A" imply a third *
* *
* predicate, "T2.A=1". As a result, at the join operation, *
* the *
* join *
* on T1.A=T2.A is effectively a cartesian product of two *
* result *
* sets that have all 1s in the join columns. *
* *
* The sort-merge join (MSJOIN) operation is limited to *
* applying *
* only one of the three join predicates. This is clearly *
* *
* indicated in the details of the MSJOIN operator when viewing *
* the explain of the query using the db2exfmt tool; e.g. *
* *
* 6) MSJOIN: (Merge Scan Join) *
* *
* ... *
* *
* Predicates: *
* ---------- *
* 42) Predicate used in Join *
* Comparison Operator: Equal (=) *
* Subquery Input Required: No *
* Filter Factor: 1 *
* *
* Predicate Text: *
* -------------- *
* (Q1.A=Q2.A) *
* 50) *
* Residual Predicate *
* Comparison Operator: Equal (=) *
* Subquery Input Required: No *
* Filter Factor: 3.97033e-06 *
* *
* Predicate Text: *
* -------------- *
* (Q1.B=Q2.B) *
* 51) *
* Residual Predicate *
* Comparison Operator: Equal (=) *
* Subquery Input Required: No *
* Filter Factor: 0.5 *
* *
* Predicate Text: *
* -------------- *
* (Q1.C=Q2.C) *
* *
* Of the 3 predicates, 1 is classified as "Predicate used in *
* Join" and the *
* other two are "Residual". In some cases, the optimizer may *
* choose to perform the MSJOIN *
* using the effectively cartesian join predicate to perform *
* the *
* sort-merge join, applying the potentially filtering *
* predicates *
* as residuals. In some cases, this may not be an optimal *
* *
* operation. *
* *
* This fix will improve the handling of these effectively *
* cartesian join predicates in the optimizer. *
****************************************************************
* RECOMMENDATION: *
* Upgrade to Version 9.7 Fix Pack 1 *
**************************************************************** |
| Local Fix: |
If you identify that the optimizer is choosing an effectively
cartesian join as the MSJOIN join predicate (e.g by viewing the
access plan using the visual explain or db2exfmt tool) and the
performance of the query execution is poor, a survey of the
cardinality estimates could indicate under estimation and
therefore collecting appropriate statistics may provide the
optimizer enough information to generate a better performing
access plan.
Another option, as a last resort, is to consider lowering the
query optimization level to 0 for the affected query using the
"current query optimization" special register, as follows:
set current query optimization 0;
<query>
set current query optimization <dft_queryopt>;
This will result in the optimizer using level 0 optimizations
for the affected query only, avoiding the consideration of a
MSJOIN operation altogether. |
| available fix packs: |
DB2 Version 9.7 Fix Pack 1 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 2 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
|
| Solution |
Problem was first fixed in Version 9.7 Fix Pack 1 |
| Workaround |
See LOCAL FIX |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 22.10.2009
12.01.2010
12.01.2010 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP1
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.1
|
 |
