DB2 Version 10.1 for Linux, UNIX, and Windows

FP4: Optimizing data warehouse queries

A number of improvements have been made to optimize the performance of data warehouse queries.

Some data warehouse queries are now returned sooner due to compile time improvements. In addition, the following types of queries have also been optimized:

Queries with a stack of outer joins or NOT EXISTS predicates that are converted to anti-joins. Here is an example of the joins reordered for better performance:
```
WITH VW02 as (SELECT * FROM  T3 LEFT OUTER JOIN T4 ON T3.C3 = T4.C4)
     SELECT * FROM T1 LEFT JOIN VW02 on T1.C3 = VW02.C5
     WHERE T1.C0 = 5
```

Queries with multiple query blocks that share a common process, other than some differences in the aggregates. Here is an example:

 WITH S1 AS (SELECT S.storeid, MAX(sales) as maxsales 
                                  FROM store S,   fact F, datedim D 
                                  WHERE S.storeid = F.storeid and
                                                   F.dateid = D.dateid and 
                                                   D.month = 10 group by  S.storeid),
                    S2 AS (SELECT S.storeid, MIN(sales) as minsales 
                                  FROM  store S, fact F, datedim D 
                                 WHERE S.storeid = F.storeid and 
                                                   F.dateid = D.dateid and 
                                                   D.month = 10 group by S.storeid )
       SELECT maxsales, minsales from S1, S2 where S1.storeid = S2.storeid;

Queries that have expressions with join predicates that use FULL OUTER JOIN, UNION, or GROUP BY. In these situations, the optimizer may use a merge scan join, such as in the following:

MERGE INTO (
   SELECT C1, C2
   FROM T1
   WHERE C2 ='N') T USING (
   SELECT RTRIM(C11)||LTRIM(C12) AS C1,
          MAX(C2) C2
   FROM T2
   GROUP BY C11, C12) S ON T.C1 = S.C1
WHEN MATCHED
THEN
UPDATE SET T.C2 = S.C2;

Queries that feature a predicate push down through the window aggregate functions, such as the following:
```
 select * from (select c2, sum(c1) over (partition by c1,c2) from t1) where c2 = 1
```

For more information about optimizing data warehouse queries, see http://pic.dhe.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.dwe.navigate.doc/tparentransfrmdata.html.