关于db2物化视图增量刷新的功能

增量刷新的原理是借用一个staging table。

收起

物化视图主要用在汇总统计等复杂SQL查询中。当SQL自动匹配MQT（全集或者子集）时，优化器采用MQT，在db2diag.log中有类似信息：
Extended Diagnostic Information:
--------------------------------
Diagnostic Identifier: 2
Diagnostic Details: EXP0022W Index has no statistics. The
index "CMCCAIN "."MQTIX1" has not had runstats run on it.
This can lead to poor cardinality and predicate filtering
estimates.
Diagnostic Identifier: 1
Diagnostic Details: EXP0020W Table has no statistics. The
table "CMCCAIN "."MQT1" has not had runstats run on it. This
may result in a sub-optimal access plan and poor performance.
Diagnostic Identifier: 3
Diagnostic Details: EXP0148W The following MQT or
statistical view wasconsidered in query matching: "CMCCAIN
"."MQT1".
Diagnostic Identifier: 4
Diagnostic Details: EXP0149W The following MQT was used
(from those considered) in query matching: "CMCCAIN "."MQT1".


物化视图增量刷新的例子

1. Define the MQT with deferred refresh. The MQT is based on summary
information from two tables.
Create table mqt2 as (
SELECT Teller.TELLER_ID, sum(HISTORY.BALANCE) as total_balance,
TELLER.TELLER_NAME , count(*) as transactions
FROM INST411.HISTORY AS HISTORY, INST411.TELLER AS TELLER
WHERE HISTORY.TELLER_ID = TELLER.TELLER_ID
GROUP BY TELLER.TELLER_ID, TELLER.TELLER_NAME )
data initially deferred refresh deferred in tp1sms ;
2. Create a staging table for the MQT.
CREATE TABLE stage2 for mqt2 propagate immediate in tp1sms ;
3. Use SET INTEGRITY to refresh the MQT and resolve Set Integrity Pending
for the staging table.
SET INTEGRITY FOR mqt2,stage2 IMMEDIATE CHECKED
4. Use RUNSTATS on the MQT to provide the optimizer current statistics
RUNSTATS on table inst411.mqt2
5. Use REFRESH TABLE to incrementally update the MQT using the staging
table. Use runstats to get current statistics for staging table first.
RUNSTATS on table inst411.stage2
REFRESH TABLE inst411.mqt2收起