|
Problem IC98814
|
Status: Closed |
ENHANCE QUERY PERFORMANCE THROUGH PUSHDOWN OF RANGE JOINS THROUGH UNION ALL VIEW(S) |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
The DB2 query rewrite phase of optimization might be able to
improve the performance of an SQL statement compiled using
optimization class 5 if it includes a join with a view that is
defined with a union all operation, such as
CREATE VIEW view1
AS
SELECT ... FROM tabA ...
UNION ALL
SELECT ... FROM tabB ...
and the statement references multiple columns from the view in a
range join with a base table, such as
SELECT
...
FROM view1, tabC ...
WHERE tabC.x between view1.y and view1.z ...
You can identify this scenario by capturing an EXPLAIN of the
statement and verifying in the optimized statement section or
access plan that the join is performed after the UNION
operation.
This enhancement is only available under registry variable
control. To enable it, issue:
db2set
DB2_UNION_OPTIMIZATION="[<other-values>,]ENABLE_UA_RANGE_JPPD=ON
[,<other-values>]"
and restart the database manager for settings to take effect. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* all users *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Upgrade to v9.7 FP10 or higher *
**************************************************************** |
| Local Fix: |
Use optimization class 7 or higher. |
| Solution |
|
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 17.01.2014
09.12.2014
09.12.2014 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP10
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.10
|
 |
