|
Problem IC63668
|
Status: Geschlossen |
INCORRECT RESULTS WHEN ORDERED COLUMN GROUP OR PREDICATE CAN BE USED AS INDEX KEYS |
| Produkt: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problembeschreibung: |
The DB2 query optimizer can apply ordered column group OR
predicates as
index start/stop key predicates for more efficient access. For
example the
following predicate
(a1>1) or (a1=1 and a2>2) or (a1=1 and a2=2 and a3>=3)
defines a column group (a1, a2, a3) and the inclusive
condition on the key values (1, 2, 3). So if there is an
index (a1 asc, a2 asc, a3 asc), then the optimizer can apply
the composite inclusive key above for better filtering and
minimal data read from the index.
Under certain cases, when there are other predicates applicable
on a subset
of the key columns, you may encounter incorrect results. For
example, the
following set of predicates includes a join between tables A and
B that
applies to the first 2 key columns:
a1=b1 AND a2=b2 AND ((a1>1) or (a1=1 and a2>2) or (a1=1 and a2=2
and a3>=3))
If a nested loop join (NLJOIN) operation is chosen with A on the
inner, you may
experience incorrect results. |
| Problem-Zusammenfassung: |
The DB2 query optimizer can apply ordered column group OR
predicates as
index start/stop key predicates for more efficient access. For
example the
following predicate
(a1>1) or (a1=1 and a2>2) or (a1=1 and a2=2 and a3>=3)
defines a column group (a1, a2, a3) and the inclusive
condition on the key values (1, 2, 3). So if there is an
index (a1 asc, a2 asc, a3 asc), then the optimizer can apply
the composite inclusive key above for better filtering and
minimal data read from the index.
Under certain cases, when there are other predicates applicable
on a subset
of the key columns, you may encounter incorrect results. For
example, the
following set of predicates includes a join between tables A and
B that
applies to the first 2 key columns:
a1=b1 AND a2=b2 AND ((a1>1) or (a1=1 and a2>2) or (a1=1 and a2=2
and a3>=3))
If a nested loop join (NLJOIN) operation is chosen with A on the
inner, you may
experience incorrect results. |
| Local-Fix: |
Optimization profiles may be used to change the join operation
or reverse
the order of the join to avoid an access plan that leads to the
incorrect results. |
| verfügbare FixPacks: |
DB2 Version 9.7 Fix Pack 1 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 2 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
|
| Lösung |
apar: ic63668
module: engn_sqno
fixed >= v97 fpk1 |
| Workaround |
Optimization profiles may be used to change the join operation
or reverse
the order of the join to avoid an access plan that leads to the
incorrect results. |
| Weitere Daten |
Datum - Problem gemeldet :
Datum - Problem geschlossen :
Datum - der letzten Änderung:
| 08.10.2009
02.12.2009
02.12.2009 |
| Problem behoben ab folgender Versionen (IBM BugInfos) |
9.7.FPk1
|
| Problem behoben lt. FixList in der Version |
| 9.7.0.1
|
 |
