DB2 10.5 for Linux, UNIX, and Windows

Statistics used from expression columns in statistical views

The query optimizer can use statistics from the expression columns in statistical views to obtain accurate cardinality estimates which results in better access plans.

For example, consider a query that uses the UCASE scalar function:

    SELECT * FROM t1, t2 WHERE t2.c2 = UCASE(t1.c1)

The query optimizer can use the statistics from a statistical view for these types of queries to obtain better access plans.

The earlier example query would benefit from a statistical view that includes the UCASE scalar function on column c1. The following example creates a view that includes the UCASE scalar function on column c1.

    CREATE VIEW sv AS (SELECT UCASE(c1) AS c1 FROM t1)
    ALTER VIEW sv ENABLE QUERY OPTIMIZATION
    RUNSTATS ON TABLE dba.sv WITH DISTRIBUTION

To obtain statistics for these types of queries, one side of the predicate must be an expression that matches an expression in the statistical view column definition exactly.

Here are some examples where the query optimizer does not use the statistics from a statistical view:

One side of the predicate in the query is an expression that is matched with more than one expression column in a statistical view:
```
create view SV14(c1, c2) as (select c1+c2, c1*c2 from t1 where c1 > 3);
alter view SV14 enable query optimization;
runstats on table schema.sv1 with distribution;
select * from t1 where (c1+c2) + (c1*c2) > 5 and c1 > 3;
```
Here the expression (c1+c2) + (c1*c2) matched to two columns in view SV14. The statistics of view SV14 for this expression are not used.
One side of the predicate in the query is an expression that is partially matched with an expression column in a statistical view:
```
create view SV15(c1, c2) as (select c1+c2, c1*c2 from t1 where c1 > 3);
alter view SV15 enable query optimization;
runstats on table schema.SV15 with distribution;
select * from t1 where (c1+c2) + 10 > 5 and c1 > 3;
```
Here the expression (c1+c2) + 10 is partially matched to c1+c2 in view SV15. The statistics of view SV15 for this expression are not used.

One side of the predicate in the query is indirectly matched to an expression column in a statistical view:

create view SV16(c1, c2) as (select c1+c2, c1*c2 from t1 where c1 > 3);
alter view SV16 enable query optimization;
runstats on table schema.SV16 with distribution;
select * from t3 left join table (select ta.c1 from t2 left join table 
  (select c1+c2,c3 from t1 where c1 > 3) as ta(c1,c3) on t2.c1 = ta.c3) as 
   tb(c1) on t3.c1= TB.C1;

Here the column TB.C1 indirectly matches the expression c1+c2 in view SV16. The statistics of view SV16 for this expression are not used.