DB2 - Problembeschreibung
Problem IC71258 | Status: Geschlossen |
DB2 QUERY OPTIMIZER MIGHT CHOOSE A NON-OPTIMAL ACCESS PLAN FOR QUERIES INVOLVING JOINS ON SKEWED DATA. | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / 950 - DB2 | |
Problembeschreibung: | |
DB2 might be encountering this problem if the SQL statement is not performing optimally, one or more joins in the query are on columns with significant skew in one or both sides of the join, and neither column referenced in the join is unique, or close to unique. Under this condition, the DB2 Optimizer might underestimate the cardinality which might result in a less than optimal query access plan. To solve this problem, in most cases, a statistical view can be created on the join to provide the optimizer the appropriate information to address for the skew. This fix will improve the optimizer's cardinality estimation to make use of single table column distribution statistics to estimate the skew in the join, for each individual equality join predicate. The following is an example of a query with an equality join predicate that is eligible under these conditions: SELECT ... FROM ... T1, T2, ... WHERE ... T1.X=T2.X A statistical view will provide more accurate information as well as more flexibility in cases such as the following that will not be addressed with this improvement: 1. There are multiple columns involved in the join, and the columns are statistically correlated; 2. Joins involving non-equality predicates such as "T1.DATE >= T2.START_DATE AND T1.DATE <= T2.END_DATE"; 3. Local predicates are applied on either side of the join that correlates to the data in the join column such as "T1.X IN (x,y,z) AND T1.Y=T2.Y" | |
Problem-Zusammenfassung: | |
**************************************************************** * USERS AFFECTED: * * ALL * **************************************************************** * PROBLEM DESCRIPTION: * * DB2 might be encountering this problem if the SQL statement * * is * * not performing optimally, one or more joins in the query are * * on * * columns with significant skew in one or both sides of the * * join, * * and neither column referenced in the join is unique, or * * close to * * unique. Under this condition, the DB2 Optimizer might * * underestimate the cardinality which might result in a less * * than * * optimal query access plan. * * To solve this problem, in most cases, a statistical view can * * be * * created on the join to provide the optimizer the appropriate * * information to address for the skew. This fix will improve * * the * * optimizer's cardinality estimation to make use of single * * table * * column distribution statistics to estimate the skew in the * * join, * * for each individual equality join predicate. The following * * is * * an example of a query with an equality join predicate that * * is * * eligible under these conditions: * * SELECT ... * * FROM ... T1, T2, ... * * WHERE ... T1.X=T2.X * * * * A statistical view will provide more accurate information as * * well as more flexibility in cases such as the following that * * will not be addressed with this improvement: * * 1. There are multiple columns involved in the join, and the * * columns are statistically correlated; * * 2. Joins involving non-equality predicates such as "T1.DATE * * >= * * T2.START_DATE AND T1.DATE <= T2.END_DATE"; * * 3. Local predicates are applied on either side of the join * * that * * correlates to the data in the join column such as "T1.X * * IN * * (x,y,z) AND T1.Y=T2.Y" * **************************************************************** * RECOMMENDATION: * * Upgrade to DB2 version 9.5 Fix Pack 9 * **************************************************************** | |
Local-Fix: | |
Create a statistical view for the affected join(s) to provide the optimizer the relevant information to account for the data skew. | |
verfügbare FixPacks: | |
DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows | |
Lösung | |
Problem was first fixed in version 9.5 Fix Pack 9 | |
Workaround | |
see LOCAL FIX | |
Bug-Verfolgung | |
Vorgänger : APAR is sysrouted TO one or more of the following: IC84160 Nachfolger : | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 16.09.2010 08.03.2012 08.03.2012 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
9.5.FP9 | |
Problem behoben lt. FixList in der Version | |
9.5.0.9 |