home clear 64x64
en blue 200x116 de orange 200x116 info letter User
suche 36x36
Neueste VersionenFixList
11.1.0.7 FixList
10.5.0.9 FixList
10.1.0.6 FixList
9.8.0.5 FixList
9.7.0.11 FixList
9.5.0.10 FixList
9.1.0.12 FixList
Haben Sie Probleme? - Kontaktieren Sie uns.
Kostenlos registrieren anmeldung-x26
Kontaktformular kontakt-x26

DB2 - Problembeschreibung

Problem IC61900 Status: Geschlossen

ENABLE GENERATION OF IMPLIED IN-LIST PREDICATES FOR OR PREDICATE WHEN QUERY
ON A SINGLE TABLE.

Produkt:
DB2 FOR LUW / DB2FORLUW / 910 - DB2
Problembeschreibung:
We are enabling generatiuon of implied IN-list predicates for 
the OR predicate set when query on a single table.  The OR 
predicate is restricted to have only equal (=) and inlist (IN) 
predicate subterm therefore only implied IN-list predicate would 
be generated.  With the new IN-list generated, we have new 
opportunity to carry out IN-TO-JOIN transformation to enhance 
query performance. 
 
e.g. 
select a1,a2,a3 
from a 
where (a1 = 1 and a3 in ('aa','bb','cc')) or (a1 = 1 and a3 in 
('dd','ee')) or (a1 = 2 and a3 in ('ff')); 
 
Optimized query: 
SELECT ALL Q3.A1, Q3.A2, Q3.A3 
FROM A Q3 
WHERE SYSIBM.SINLIST(Q3.A3, 'aa', 'bb', 'cc', 'dd', 'ee', 'ff') 
AND 
SYSIBM.SINLIST(Q3.A1, 1, 2) AND 
(((Q3.A1 = 1) AND SYSIBM.SINLIST(Q3.A3, 'aa', 'bb', 'cc')) OR 
((Q3.A1 = 1) AND SYSIBM.SINLIST(Q3.A3, 'dd', 'ee'))) OR 
((Q3.A1 = 2) AND (Q3.A3 = 'ff')); 
 
Note that there are existing condition to carry IN-TO-JOIN 
transfromation, the new IN predicate is expected to comply to 
those existing condition for have IN-TO-JOIN resulted.
Problem-Zusammenfassung:
We are enabling generatiuon of implied IN-list predicates 
for 
the OR predicate set when query on a single table.  The OR 
 
predicate is restricted to have only equal (=) and inlist 
(IN) 
predicate subterm therefore only implied IN-list predicate 
would 
be generated.  With the new IN-list generated, we have new 
 
opportunity to carry out IN-TO-JOIN transformation to 
enhance 
query performance. 
 
 
 
e.g. 
 
select a1,a2,a3 
 
from a 
 
where (a1 = 1 and a3 in ('aa','bb','cc')) or (a1 = 1 and a3 
in 
('dd','ee')) or (a1 = 2 and a3 in ('ff')); 
 
 
 
Optimized query: 
 
SELECT ALL Q3.A1, Q3.A2, Q3.A3 
 
FROM A Q3 
 
WHERE SYSIBM.SINLIST(Q3.A3, 'aa', 'bb', 'cc', 'dd', 'ee', 
'ff') 
AND 
 
SYSIBM.SINLIST(Q3.A1, 1, 2) AND 
 
(((Q3.A1 = 1) AND SYSIBM.SINLIST(Q3.A3, 'aa', 'bb', 'cc')) 
OR 
((Q3.A1 = 1) AND SYSIBM.SINLIST(Q3.A3, 'dd', 'ee'))) OR 
 
((Q3.A1 = 2) AND (Q3.A3 = 'ff')); 
 
 
 
Note that there are existing condition to carry IN-TO-JOIN 
 
transfromation, the new IN predicate is expected to comply 
to 
those existing condition for have IN-TO-JOIN resulted.
Local-Fix:
Workaround: 
You may manually rewritten the query with the implied IN 
predicate as shown in the example. 
 
defect: wsdbu00554342
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 LUW v9.1 Fixpak 9
Workaround
keiner bekannt / siehe Local-Fix
Bug-Verfolgung
Vorgänger  : APAR is sysrouted TO one or more of the following: IC61901 IC61902 IC61909 IC62132 IT02159 IT02166 
Nachfolger : 
Weitere Daten
Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:
08.07.2009
13.04.2010
13.04.2010
Problem behoben ab folgender Versionen (IBM BugInfos)
9.0.,
9.1.
Problem behoben lt. FixList in der Version
9.1.0.9 FixList