DB2 - Problembeschreibung
Problem IT07432 | Status: Geschlossen |
QUERIES WITH "CASE WHEN" USING SUBQUERIES MAY LEAD TO PERFORMANCE ISSUE. | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 | |
Problembeschreibung: | |
Queries in the following form may show bad performance when a CASE WHEN condition contains subqueries: SELECT COUNT(*) FROM (SELECT T1.COL1, T1.COL2, T1.COL3, T1.COL4, T1.COL5, T1.COL6, T1.COL7, T1.COL8 FROM TAB01 T1 INNER JOIN TAB02 T2 INNER JOIN session.#tt4 T3 ON 1=1 ON (T1.COL2 = T2.COL2) WHERE ((T2.COL6 = 'My string' AS CHAR(16)FOR BIT DATA))) AND CASE WHEN (T3.COL55 > CAST(2 AS NUMERIC(1, 0))) THEN CAST(x'01' AS CHAR(1) FOR BIT DATA) ELSE CASE WHEN (((T2.MyCol_A IN (SELECT T4.MyCol_B AS MyCol_B FROM session.#tt5 T4)) OR (T2.MyCol_C IN (SELECT T5.MyCol_B AS MyCol_B FROM session.#tt5 T5))) OR (T2.MyCol_D IN (SELECT T6.MyCol_B AS MyCol_B FROM session.#tt5 T6))) THEN CAST(x'01' AS CHAR(1) FOR BIT DATA) ELSE CAST(x'00' AS CHAR(1) FOR BIT DATA) END END = CAST(x'01' AS CHAR(1) FOR BIT DATA)) The suboptimal performance arises when there are a lot of rows being evaluated and they all need to evaluate all subexpressions in the case statement. This apar fix will introduce an internal optimization by generating extra boolean predicates that can be evaluated prior to having to execute the relatively expensive subselects. | |
Problem-Zusammenfassung: | |
**************************************************************** * USERS AFFECTED: * * all * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * upgrade to version 9.7 Fix Pack 11 * **************************************************************** | |
Local-Fix: | |
Lösung | |
First fixed in version 9.7 Fix Pack 11 | |
Workaround | |
keiner bekannt / siehe Local-Fix | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 02.03.2015 08.10.2015 08.10.2015 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
9.7.FP11 | |
Problem behoben lt. FixList in der Version | |
9.7.0.11 |