DB2 FOR LUW / DB2FORLUW / 970 - DB2

DB2 Query ReWrite (QRW) may not completely optimize a query 
containing a local predicate in the on clause of a left or right 
outer join (LOJ) from the row-preserving (RP) side. 
. 
This optimization may be overlooked if the following conditions 
are satisfied: 
- The producer of the RP side of the LOJ is a UNION or UNION ALL 
(UA) 
- The local predicate in the on clause is subsumed or made 
redundant by a local predicate in the RP side 
. 
An example scenario follows: 
CREATE TABLE T1 (C1 INT); 
CREATE TABLE T2 LIKE T1; 
CREATE TABLE T3 LIKE T1; 
CREATE TABLE T4 LIKE T1; 
. 
SELECT Q.C1 
FROM   (SELECT C1 FROM T2 
        UNION ALL 
        SELECT C1 FROM T3 
        UNION ALL 
        SELECT C1 FROM T4 
       ) AS Q (C1) 
       LEFT OUTER JOIN T1 
       ON (Q.C1 > 1         -- <-- P1 
           AND Q.C1 = T1.C1 -- <-- P2 
          ) 
WHERE  Q.C1 > 1;            -- <-- P3 -- makes P1 redundant

DB2 Query ReWrite (QRW) may not completely 
optimize a query 
containing a local predicate in the on clause 
of a left or right 
outer join (LOJ) from the row-preserving (RP) 
side. 
. 
This optimization may be overlooked if the 
following conditions 
are satisfied: 
- The producer of the RP side of the LOJ is a UNION or UNION ALL 
(UA) 
- The local predicate in the on clause is subsumed or made 
redundant by a local predicate in the RP side 
. 
An example scenario follows: 
CREATE TABLE T1 (C1 INT); 
CREATE TABLE T2 LIKE T1; 
CREATE TABLE T3 LIKE T1; 
CREATE TABLE T4 LIKE T1; 
. 
SELECT Q.C1 
FROM   (SELECT C1 FROM T2 
        UNION ALL 
        SELECT C1 FROM T3 
        UNION ALL 
        SELECT C1 FROM T4 
       ) AS Q (C1) 
       LEFT OUTER JOIN T1 
       ON (Q.C1 > 1         -- <-- P1 
           AND Q.C1 = T1.C1 -- <-- P2 
          ) 
WHERE  Q.C1 > 1;            -- <-- P3 -- makes P1 redundant

Manually alter the query by removing the redundant local 
predicate in the on clause of the outer join, e.g., for the 
query above, this may be done like so: 
. 
SELECT Q.C1 
FROM   (SELECT C1 FROM T2 
        UNION ALL 
        SELECT C1 FROM T3 
        UNION ALL 
        SELECT C1 FROM T4 
       ) AS Q (C1) 
       LEFT OUTER JOIN T1 
       ON (Q.C1 = T1.C1)    -- <-- P2 
WHERE  Q.C1 > 1;            -- <-- P3

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
 

module engn_sqnr 
fixed >= v97 fpk1

Manually alter the query by removing the redundant local 
predicate in the on clause of the outer join, e.g., for the 
query above, this may be done like so: 
. 
SELECT Q.C1 
FROM   (SELECT C1 FROM T2 
        UNION ALL 
        SELECT C1 FROM T3 
        UNION ALL 
        SELECT C1 FROM T4 
       ) AS Q (C1) 
       LEFT OUTER JOIN T1 
       ON (Q.C1 = T1.C1)    -- <-- P2 
WHERE  Q.C1 > 1;            -- <-- P3

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

20.06.2009
11.02.2010
11.02.2010

9.7.FPk1