DB2 - Problem description
Problem IT41505 | Status: Closed |
QUERY PERFORMANCE MAY VARY WHEN USING OUTER JOIN OPERATOR | |
product: | |
DB2 FOR LUW / DB2FORLUW / B10 - DB2 | |
Problem description: | |
When a query uses the outer join operator, the join order may occasionally change between different query compilations. While it does not affect the query results, it may occasionally result in a change in query plan and performance. For example, consider the following query create table t1 (c1 int); create table t2 (c1 int); create table t3 (c1 int); select * from T1, T2, T3 where T1.c1 = T2.c1(+) and T1.c1 = T3.c1(+); In the explain output for this query, the "Optimized Statement" would normally look like this: Optimized Statement: ------------------- SELECT Q3.$C1 AS "C1", Q3.$C0 AS "C1", Q4.C1 AS "C1" FROM (SELECT Q2.C1, Q1.C1 FROM USER.T1 AS Q1 LEFT OUTER JOIN USER.T2 AS Q2 ON (Q1.C1 = Q2.C1) ) AS Q3 LEFT OUTER JOIN USER.T3 AS Q4 ON (Q3.$C1 = Q4.C1) Occasionally, it may look like this: Optimized Statement: ------------------- SELECT Q3.$C1 AS "C1", Q4.C1 AS "C1", Q3.$C0 AS "C1" FROM (SELECT Q2.C1, Q1.C1 FROM USER.T1 AS Q1 LEFT OUTER JOIN USER.T3 AS Q2 ON (Q1.C1 = Q2.C1) ) AS Q3 LEFT OUTER JOIN USER.T2 AS Q4 ON (Q3.$C1 = Q4.C1) Notice the join order has changed from T1, T2, T3 to T1, T3, T2 | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * ALL * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to Db2 version 11.5.8.0 * **************************************************************** | |
Local Fix: | |
Change query to use standard outer join syntax, for the example query above: select * from T1 left outer join T2 on T1.C1=T2.C1 left outer join T3 on T1.C1=T3.C1; | |
Solution | |
Workaround | |
**************************************************************** * USERS AFFECTED: * * ALL * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to Db2 version 11.5.8.0 * **************************************************************** | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 13.07.2022 14.07.2022 08.09.2022 |
Problem solved at the following versions (IBM BugInfos) | |
Problem solved according to the fixlist(s) of the following version(s) |