在查看两个表的连接的执行计划的时候，很可能DB2估计的返回行数和实际的行数相差较大，这个时候可以考虑使用统计视图来引导DB2做出正确的估值，制定更为高效的执行计划，提高SQL的运行效率。

--Improve cadinality estimate of DB2 Join with a statview
create view dw.stat_v1 as
(
select q1.*, q3.* from dw.FACT1 as q1,dw.PERIOD as q3
where (q3.period_key=q1.period_key));

alter view dw.stat_v1 enable query optimization;
runstats on table dw.stat_v1 with distribution default num_freqvalues 25
num_quantiles 50 allow write access tablesample bernoulli(25);

--Result of join was huge, hence SAMPLED STATISTICS were collected.

statistical view是否被用到了那？可以查看db2exfmt的输出信息来query

被用到的情况
Extended Diagnostic Information:
--------------------------------
Diagnostic Identifier: 2
Diagnostic Details: EXP0147W The following statistical view may have been used by the optimizer to estimate cardinalities: "DW "."STAT_V1".
Diagnostic Identifier: 4
Diagnostic Details: EXP0148W The following MQT or statistical view was considered in query matching: "DW "."STAT_V1".

Any statview that references any of the tables in the query will be considered.
如果没有被用到
Diagnostic Identifier:1
Diagnostic Details: EXP0073W The following MQT or statistical view was not eligible because one or more data filtering predicates from the query could not be matched with the MQT: "DW "."STAT_V2".
Diagnostic Identifier:3
Diagnostic Details: EXP0148W The following MQT or statistical view was considered in query matching: "DW "."STAT_V2".

如果类似的join在应用中多次出现，这个统计视图将会对很多SQL的性能起到改善的作用。