|
Problem IC68978
|
Status: Closed |
AVOID EXPANDING CARTESIAN JOIN PRODUCED BY AUTOMATICALLY REWRITING MULTIPLE LEVEL CORRELATION IN DPF |
| product: |
DB2 FOR LUW / DB2FORLUW / 950 - DB2 |
| Problem description: |
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. |
| Problem Summary: |
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. |
| Local Fix: |
|
| available fix packs: |
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
|
| Solution |
apar IC68978
fixed >= fpk8 |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 02.06.2010
30.06.2011
30.06.2011 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.5.0.8
|
 |
