DB2 FOR LUW / DB2FORLUW / 950 - DB2

In DPF DB2 compiler can transform correlated subquery to 
semijoin in order to improve query performance. If a group-by 
subquery (called subQL1) and this subquery's subquery (called 
subQL2) both reference some (not necessarily the same) column 
from its outer block (called outBlock), and if the subquery has 
at least one join table (called noCorrJoinTab) that has no join 
predicate to the outer block, DB2 may rewrite the query to such 
semi-join that includes a SELECT DISTINCT common table 
expression (CSE) that has Cartesian join on noCorrJoinTab. 
An example query is: 
select c1, c2, maxb5 
from c, 
table  (select max(b5) maxb5 
        from a, b 
        where a1 = b1 and 
              b2 = c1 and 
              a1 = (select max(b4) from b where b3 = c2) ) as t; 
subQL1 = "select max(b5) maxb5 from a, b where a1 = b1 and b2 = 
c1 and a1 = (select max(b4) from b where b3 = c2)" 
subQL2 = "select max(b4) from b where b3 = c2" 
outBlock = table c 
noCorrJoinTab = table a 
In db2exfmt explain plan file, the optimized statement show a 
SELECT DISTINCT CSE with Cartesian join on table a. 
The fix will resolve the expanding Cartesian join. It splits CSE 
and hence prevents TEMP in the query access plan. It may also 
access up to one row from the Cartesian join table so that the 
expanding Cartesian join is avoided.

In DPF DB2 compiler can transform correlated subquery to 
semijoin in order to improve query performance. If a group-by 
subquery (called subQL1) and this subquery's subquery (called 
subQL2) both reference some (not necessarily the same) column 
from its outer block (called outBlock), and if the subquery has 
at least one join table (called noCorrJoinTab) that has no join 
predicate to the outer block, DB2 may rewrite the query to such 
semi-join that includes a SELECT DISTINCT common table 
expression (CSE) that has Cartesian join on noCorrJoinTab. 
An example query is: 
select c1, c2, maxb5 
from c, 
table  (select max(b5) maxb5 
        from a, b 
        where a1 = b1 and 
              b2 = c1 and 
              a1 = (select max(b4) from b where b3 = c2) ) as t; 
subQL1 = "select max(b5) maxb5 from a, b where a1 = b1 and b2 = 
c1 and a1 = (select max(b4) from b where b3 = c2)" 
subQL2 = "select max(b4) from b where b3 = c2" 
outBlock = table c 
noCorrJoinTab = table a 
In db2exfmt explain plan file, the optimized statement show a 
SELECT DISTINCT CSE with Cartesian join on table a. 
The fix will resolve the expanding Cartesian join. It splits CSE 
and hence prevents TEMP in the query access plan. It may also 
access up to one row from the Cartesian join table so that the 
expanding Cartesian join is avoided.

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
 

apar IC68978 
fixed >= fpk8

keiner bekannt / siehe Local-Fix

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

02.06.2010
30.06.2011
30.06.2011