之前我们整理了DM DCP 的相关内容：

达梦 DM8 DCP 备考笔记(1) — 理论考试 说明
https://www.cndba.cn/dave/article/108708
达梦 DM8 DCP 备考笔记(2) — 分区表操作
https://www.cndba.cn/dave/article/108710
达梦 DM8 DCP 备考笔记(3) — 外部表操作
https://www.cndba.cn/dave/article/108711
达梦 DM8 DCP 备考笔记(4) — 同义词操作
https://www.cndba.cn/dave/article/108712

http://www.cndba.cn/dave/article/108713

http://www.cndba.cn/dave/article/108713http://www.cndba.cn/dave/article/108713http://www.cndba.cn/dave/article/108713

本篇我们来看下达梦数据库中的物化视图的操作。 相关理论说明参考《SQL语言使用手册》的第7章:物化视图。

1.创建手动更新的物化视图

SQL> create materialized view cndba.mv1 as select * from cndba.t1;
executed successfully
used time: 25.638(ms). Execute id is 55647.

SQL> select count(1) from mv1;

LINEID     COUNT(1)
---------- --------------------
1          299

used time: 1.612(ms). Execute id is 55648.

SQL> refresh materialized view cndba.mv1;
executed successfully
used time: 12.434(ms). Execute id is 55649.

SQL> delete from t1 where rownum<50;
affect rows 49

used time: 1.102(ms). Execute id is 55650.
SQL> commit;
executed successfully
used time: 5.277(ms). Execute id is 55651.

SQL>  select count(1) from mv1;

LINEID     COUNT(1)
---------- --------------------
1          299

used time: 1.129(ms). Execute id is 55652.
SQL> select count(1) from t1;

LINEID     COUNT(1)
---------- --------------------
1          250

used time: 0.788(ms). Execute id is 55653.

SQL> refresh materialized view cndba.mv1;
executed successfully
used time: 7.685(ms). Execute id is 55654.
SQL> select count(1) from mv1;

LINEID     COUNT(1)
---------- --------------------
1          250

used time: 0.983(ms). Execute id is 55655.
SQL>

2. 创建自动更新的物化视图

SQL> create materialized view log on cndba.t1;
executed successfully
used time: 19.591(ms). Execute id is 55656.

SQL> create materialized view cndba.mv2 refresh complete on commit  as select * from cndba.t1;
executed successfully
used time: 13.725(ms). Execute id is 55657.

SQL> select count(1) from mv2;

LINEID     COUNT(1)
---------- --------------------
1          250

used time: 0.911(ms). Execute id is 55658.
SQL> delete from t1 where rownum<20;
affect rows 19

used time: 1.609(ms). Execute id is 55659.
SQL> commit;
executed successfully
used time: 2.827(ms). Execute id is 55660.
SQL> select count(1) from mv2;

LINEID     COUNT(1)
---------- --------------------
1          231

used time: 0.901(ms). Execute id is 55661.
SQL>

3. 创建快速更新物化视图

SQL> create materialized view cndba.mv3 refresh fast on commit as select * from cndba.t1;
executed successfully
used time: 33.014(ms). Execute id is 55662.
SQL> select count(1) from mv3;

LINEID     COUNT(1)
---------- --------------------
1          231

used time: 0.981(ms). Execute id is 55663.
SQL>

注意：使用 FAST刷新之前，必须先建好物化视图日志。http://www.cndba.cn/dave/article/108713http://www.cndba.cn/dave/article/108713

版权声明：本文为博主原创文章，未经博主允许不得转载。