|
Problem IC74857
|
Status: Closed |
DB2 OPTIMIZER MAY CHOOSE THE LESS OPTIMAL PLAN WHERE SUBQUERY HAS DISTINCT KEYWORD |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
Starting DB2 v9.7, relatively large query with multiple UNION
ALL clauses and subquery with DISTINCT keyword may have slow
performance than previous versions. This is a very uncommon
scenario which Optimizer has missed the opportunity to choose
the best plan.
User can check for following conditions
-There is a fairly big cartesian join below the outer join in
the access plan.
DB2 may push down a join (in this case a subquery with a GROUP
BY) that can magnify the size of the cartesian join which can
cause a huge SORT to remove duplicates.
For example, In previous version same query would have access
plan which shows:
join (predicate between T1 and T3)
/ \
outer join GB over T3
/ \
MEDIUM SORT T2
/
cartesian join
/ \
XXX T1
Where in v9.7, following access plan is chosen which degrades
the performance.
outer join
/ \
BIG SORT T2
/
join (predicate between T1 and T3)
/ \
join T1
/ \
XXX GB over T3
The fix for this APAR will enhance this so that DB2 Optimizer
will choose a much smaller SORT to remove duplicates and not
push down the subquery if the target is a cartesian join
join (predicate between T1 and T3)
/ \
outer join GB over T3
/ \
cartesian join T2
/ \
XXX SMALL SORT to remove duplicates from T1 |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* All. *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Problem Description above. *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 Version 9.7 Fix Pack 5. *
**************************************************************** |
| Local Fix: |
Rewrite the query to replace DISTINCT by GROUP BY clause. |
| available fix packs: |
DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 7 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 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
|
| Solution |
First Fixed in Version 9.7 Fix Pack 5. |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 04.03.2011
23.12.2011
23.12.2011 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP5
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.5
|
 |
