|
Problem IC65934
|
Status: Closed |
QUERY PERFORMANCE MAY DEGRADE IN PRESENCE OF NON-EQUALITY PREDIC ATE ON MORE THAN ONE TABLE THAT CAN BE REWRITTEN TO ONE TABLE |
| product: |
DB2 FOR LUW / DB2FORLUW / 910 - DB2 |
| Problem description: |
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 Summary: |
****************************************************************
* 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) |
| available fix packs: |
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
|
| Solution |
First fixed in DB2 Version 9.1 Fix Pack 9. |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 28.01.2010
19.04.2010
19.04.2010 |
| Problem solved at the following versions (IBM BugInfos) |
9.1.FP9
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.1.0.9
|
 |
