DB2 - Problem description
Problem IT27763 | Status: Closed |
SUBOPTIMAL PERFORMANCE WITH QUERIES CONTAINING OUTER JOINS AND OLAP FUNCTIONS | |
product: | |
DB2 FOR LUW / DB2FORLUW / B10 - DB2 | |
Problem description: | |
Suboptimal performance might be observed when running a query with the following characteristics: 1. the query contains at least one outer join 2. the row preserving side column referenced in an outer join predicate can be sourced down to a constant 3. between the outer join and the constant, there is a sub-select containing OLAP functions Example queries: Query 1: with valueBox (const) as (VALUES (1)) select * from (select row_number() over() as t1_1, t12, const from t1, valueBox) t1_value left join t2 on t1_value.t12=t2.t22 and t1_value.const = t2.t21 Query 2: with valueBox (const) as (VALUES (1)) select * from (select row_number() over() as t1_1, t12, const from t1, valueBox) t1_value left join t2 on t1_value.t12=t2.t22 and t1_value.const = 1 work-around: manually rewrite the query to replace the row preserving column in the outer join predicate with the constant, and manually eliminate the outer join predicate if possible. For ex., the example queries can be rewritten to: Rewritten query 1: with valueBox (const) as (VALUES (1)) select * from (select row_number() over() as t1_1, t12, const from t1, valueBox) t1_value left join t2 on t1_value.t12=t2.t22 and 1 = t2.t21 -- manually replace t1_value.const with "1" Rewritten query 2: with valueBox (const) as (VALUES (1)) select * from (select row_number() over() as t1_1, t12, const from t1, valueBox) t1_value left join t2 on t1_value.t12=t2.t22 -- manually eliminate "t1_value.const = 1" as it is an always true predicate | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * ALL * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to Db2 11.1 Mod 4 Fixpack 5 or higher * **************************************************************** | |
Local Fix: | |
Query rewrite as explained above. | |
Solution | |
Workaround | |
not known / see Local fix | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 14.01.2019 16.01.2020 16.01.2020 |
Problem solved at the following versions (IBM BugInfos) | |
Problem solved according to the fixlist(s) of the following version(s) |