|
Problem IC65934
|
Status: Geschlossen |
QUERY PERFORMANCE MAY DEGRADE IN PRESENCE OF NON-EQUALITY PREDIC ATE ON MORE THAN ONE TABLE THAT CAN BE REWRITTEN TO ONE TABLE |
| Produkt: |
DB2 FOR LUW / DB2FORLUW / 910 - DB2 |
| Problembeschreibung: |
Query performance may degrade if it contains an non-equality
predicate which references more than one table, but which can be
rewritten to reference only one table using equivalence.
Conditions required for such a scenario are:
- Two or more tables referenced in a query
- An non-equality predicate that references two or more tables
(NONEQPRD)
- Column equivalence via one or more equi-joins that makes it
possible to rewrite NONEQPRD to reference only one table
A trivial query that can be used to demonstrate this is:
SELECT *
FROM T1, T2, T3
WHERE T1.C1 = T2.C1
AND T3.C2 = T2.C2
AND (T1.C1 = 'abcd' OR T3.C2 = 1 OR T2.C3 = 5) |
| Problem-Zusammenfassung: |
****************************************************************
* USERS AFFECTED: *
* EE or EEE *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description. *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 Version 9.1 Fix Pack 9 or later. *
**************************************************************** |
| Local-Fix: |
Manually rewrite any problematic non-equality predicates to
their
semantically equivalent forms for a single table.
For the query above, this can be done like so:
SELECT *
FROM T1, T2, T3
WHERE T1.C1 = T2.C1
AND T3.C2 = T2.C2
AND (T2.C1 = 'abcd' OR T2.C2 = 1 OR T2.C3 = 5) |
| verfügbare FixPacks: |
DB2 Version 9.1 Fix Pack 9 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 10 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 11 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 12 for Linux, UNIX and Windows
|
| Lösung |
First fixed in DB2 Version 9.1 Fix Pack 9. |
| Workaround |
keiner bekannt / siehe Local-Fix |
| Weitere Daten |
Datum - Problem gemeldet :
Datum - Problem geschlossen :
Datum - der letzten Änderung:
| 28.01.2010
19.04.2010
19.04.2010 |
| Problem behoben ab folgender Versionen (IBM BugInfos) |
9.1.FP9
|
| Problem behoben lt. FixList in der Version |
| 9.1.0.9
|
 |
