| Latest versions | fixlist |
| 11.1.0.7 |
 |
| 10.5.0.9 |
|
| 10.1.0.6 |
|
| 9.8.0.5 |
|
| 9.7.0.11 |
|
| 9.5.0.10 |
|
| 9.1.0.12 |
|
| Have problems? - contact us. | |
| Register for free |
|
| Contact form |
|
DB2 - Problem description
| Problem IC73249 | Status: Closed |
Joining to a VALUES clause may result in suboptimal plans. | |
| product: | |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 | |
| Problem description: | |
Using a JOIN between a VALUES clause and a table or sub-select might result in suboptimal query plans. If the VALUES clause contains multiple columns, the optimizer might not choose the best join method for satisfying the JOIN if the VALUES clause joins to multiple tables with a common local equality predicate as in the following example: SELECT ... FROM table(values(?,?),(?,?),(?,?),(?,?)) as a(a1, a2), b, c, d WHERE a.a1=b.b1 and a.a2=b.b2 and a.a1=c.c1 and a.a2=c.c2 and a.a1=d.d1 and a.a2=d.d2 and a.a3 = ? and b.b3=a.a3 and c.c3=a.a3 | |
| Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * All DB2 users * **************************************************************** * PROBLEM DESCRIPTION: * * Using a JOIN between a VALUES clause and a table or * * sub-select might result in suboptimal query plans. * **************************************************************** * RECOMMENDATION: * * Upgrade to DB2 9.7 Fix pack 5 or later * **************************************************************** | |
| Local Fix: | |
Use an IN predicate instead of a JOIN to a VALUES clause | |
| available fix packs: | |
DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows | |
| Solution | |
Upgrade to a Fix Pack containing the Fix | |
| Workaround | |
not known / see Local fix | |
| Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 13.12.2010 19.12.2011 19.12.2011 |
| Problem solved at the following versions (IBM BugInfos) | |
9.7.FP5 | |
| Problem solved according to the fixlist(s) of the following version(s) | |
| 9.7.0.5 |
|