|
Problem IC89174
|
Status: Closed |
GIVE HIGHER PRIORITY TO GROUP-BY MQT AND STATISTICAL VIEW IN OPTIMIZER PLAN COSTING DECISION |
| product: |
DB2 FOR LUW / DB2FORLUW / A10 - DB2 |
| Problem description: |
When compiling a query, optimizer can generate multiple access
plans that use different compilation of MQTs and use statistical
view to adjust the cardinality of matched query join operators.
Replicated MQT is commonly used in DPF and it can be enforced by
either registry control
"DB2_EXTENDED_OPTIMIZATION=FORCE_REPLICATED_MQT_MERGEA" or
optimizer guideline "<MQTENFORCE TYPE='REPLICATED'/>" . A
replicated MQT must have no aggregation. MQT having GROUP BY
operation is called group-by MQT. When a base table has both
replicated MQT and group-by MQT dependent on it, if replicated
MQT is enforced and group-by MQT is not enforced, optimizer used
to prune group-by MQT plan since it doesn't use the replicated
MQT which is enforced. However the enforcement of replicated MQT
should be in effect only at deciding among plans of base object
access including base table and non-group-by MQTs and should not
be applied to group-by MQT plan. This fix allows group-by MQT
plan to be used if its plan costing is not more expensive than
the base object access plan.
This fix also increases of chance of using statistical view in
optimizer planing. Query compiler attempts to substitute some
subquery block with group-by MQT and adjust the cardinality of
some other subquery block by using the matched statistical view.
The group-by MQT used to reduce optimizer's use of statistical
view. That is fixed by this APAR. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* ALL *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Upgrade to v101 fixpack 2 *
**************************************************************** |
| Local Fix: |
N/A |
| available fix packs: |
DB2 Version 10.1 Fix Pack 2 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 6 for Linux, UNIX, and Windows
|
| Solution |
Upgrade to v101 fixpack 2 |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 18.12.2012
22.04.2013
22.04.2013 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 10.1.0.2
|
 |
| 10.5.0.2
|
|
