DB2 - Problembeschreibung
Problem IC71516 | Status: Geschlossen |
DB2 QUERY OPTIMIZER MIGHT NOT ELIMINATE OUTER JOIN WHEN AN ALWAYS FALSE PREDICATE IS APPLIED IN THE ON CLAUSE | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 | |
Problembeschreibung: | |
The rewrite phase of the DB2 query optimizer might not eliminate an outer join when an always false predicate is applied in the on clause. The following query is a simple example of such a scenario: select t2.a from t1 left outer join t2 on 1=0; You can identify whether there are outer joins with false predicates in the optimized form of the SQL statement by using the EXPLAIN tool and examine the "Optimized Statement" for always false predicates such as "1=0". This fix will eliminate the redundant outer join, resulting in improved query execution performance. | |
Problem-Zusammenfassung: | |
**************************************************************** * USERS AFFECTED: * * ALL * **************************************************************** * PROBLEM DESCRIPTION: * * The rewrite phase of the DB2 query optimizer might not * * eliminate * * an outer join when an always false predicate is applied in * * the * * on clause. The following query is an example of such a * * scenario: * * * * * * select t2.a from t1 left outer join t2 on 1=0; * * * * * * * * You can identify whether there are outer joins with false * * predicates in the optimized form of the SQL statement by * * using * * the EXPLAIN tool and examine the "Optimized Statement" for * * * * always false predicates such as "1=0". This fix will * * eliminate * * the redundant outer join, resulting in improved query * * execution * * performance. * **************************************************************** * RECOMMENDATION: * * Upgrade to DB2 Version 9.7 Fix Pack 4 * **************************************************************** | |
Local-Fix: | |
You can manually rewrite the outer join to a single table access of the row preserving side of the join. | |
verfügbare FixPacks: | |
DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows | |
Lösung | |
Problem was first fixed in Version 9.7 Fix Pack 4 | |
Workaround | |
See Local Fix. | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 28.09.2010 28.04.2011 28.04.2011 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
9.7.FP4 | |
Problem behoben lt. FixList in der Version | |
9.7.0.4 |