DB2 FOR LUW / DB2FORLUW / 950 - DB2

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.

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

Workaround: 
You may manually rewritten the query with the implied IN 
predicate as shown in the example. 
 
defect: wsdbu00554342

DB2 Version 9.5 Fix Pack 6a for Linux, UNIX, and Windows
 DB2 Version 9.5 Fix Pack 7 for Linux, UNIX, and Windows
 DB2 Version 9.5 Fix Pack 8 for Linux, UNIX, and Windows
 DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.5 Fix Pack 10 for Linux, UNIX, and Windows
 

Problem was first fixed in Version 9.5 Fix Pack 6

keiner bekannt / siehe Local-Fix

Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:

08.07.2009
17.05.2010
17.05.2010

9.5.FP6