DB2 FOR LUW / DB2FORLUW / 970 - DB2

Description: 
When the statistics of multiple statistical views are used by 
the optimizer, some cardinality estimates may be inaccurrate, as 
in the following example where the statistics of two statisical 
views 
 
-- generic statistical view 
-- 
CREATE VIEW FACT_DIM_SV AS 
SELECT f.*, d.DIM_KEY d 
FROM  FACT f, DIM d 
WHERE f.DIM_KEY = d.DIM_KEY; 
 
-- specific statistical view for queries with d.code=10 local 
predicate 
-- 
CREATE VIEW FACT_DIM_SV2 AS 
SELECT f.*, d.DIM_KEY d 
FROM  FACT f, DIM d 
WHERE f.DIM_KEY = d.DIM_KEY 
AND d.code = 10; 
 
are used by the optimizer when compiling a query such as: 
 
SELECT SUM(f.amount), d.code 
FROM FACT f, DIM d 
WHERE f.DIM_KEY = d.DIM_KEY 
AND d.code = 10 
AND d.era = 1;

INACCURATE CARDINALITY ESTIMATES FROM MULTIPLE ELIGIBLE 
STATISTICAL VIEWS THAT HAVE DIFFERENT NUMBER OF PREDICATES

Cardinality estimates may improve if the generic statistical 
view is dropped.  However, queries that do not contain the 
predicates of the remaining specific statistic view will not 
benefit from its statistics.

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
 

Problem was first fixed in version 9.7 Fixpak 1

Cardinality estimates may improve if the generic statistical 
view is dropped.  However, queries that do not contain the 
predicates of the remaining specific statistic view will not 
benefit from its statistics.

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

31.07.2009
19.12.2009
19.12.2009

9.7.