【达梦】物化视图（实操总结）

一、测试数据准备

CREATE USER DCP IDENTIFIED BY 123456789;

GRANT DBA TO DCP;

CREATE TABLE "DCP"."TEST"
(
"ID" INT PRIMARY KEY,
"TEST_DATE" DATE) STORAGE(ON "MAIN", CLUSTERBTR) ;

INSERT INTO "DCP"."TEST" SELECT LEVEL,SYSDATE() FROM DUAL CONNECT BY LEVEL<=10;

COMMIT;

二、参数介绍及实操

填充模式：
1. BUILD IMMEDIATE：立即填充数据。
例子：

CREATE MATERIALIZED VIEW DCP.MATERIALIZED_VIEW_9
BUILD immediate
REFRESH FORCE
ON COMMIT
WITH PRIMARY KEY
DISABLE QUERY REWRITE
AS
SELECT * FROM DCP.TEST;
select * from "DCP".MATERIALIZED_VIEW_9

2. BUILD DEFERRED：延迟填充数据。
例子：

CREATE MATERIALIZED VIEW DCP.MATERIALIZED_VIEW_4
BUILD DEFERRED
REFRESH FORCE
ON COMMIT
WITH PRIMARY KEY
DISABLE QUERY REWRITE
AS
SELECT * FROM DCP.TEST;
select * from "DCP".MATERIALIZED_VIEW_4;

刷新模式：
1. FAST 根据相关表上的数据更改记录进行增量刷新。
普通 DML 操作生成的记录存在 于物化视图日志。使用 FAST 刷新之前，必须先建好物化视图日志。
例子：

CREATE MATERIALIZED VIEW LOG ON DCP.TEST
WITH primary key
PURGE START WITH SYSDATE NEXT SYSDATE+1;

CREATE MATERIALIZED VIEW DCP.MATERIALIZED_VIEW_10
BUILD immediate
REFRESH FAST
ON DEMAND
WITH primary key
DISABLE QUERY REWRITE
AS
SELECT * FROM DCP.TEST;

INSERT INTO DCP.TEST VALUES(14,SYSDATE);
COMMIT;

select * from "DCP".MATERIALIZED_VIEW_10;

REFRESH MATERIALIZED VIEW "DCP".MATERIALIZED_VIEW_10 FAST;

2. COMPLETE 通过执行物化视图的定义脚本进行完全刷新。
例子：

CREATE MATERIALIZED VIEW DCP.MATERIALIZED_VIEW_11
BUILD immediate
REFRESH complete
ON DEMAND
WITH primary key
DISABLE QUERY REWRITE
AS
SELECT * FROM DCP.TEST;

INSERT INTO DCP.TEST VALUES(15,SYSDATE);
COMMIT;

select * from "DCP".MATERIALIZED_VIEW_11;

3. FORCE 默认选项。当快速刷新可用时采用快速刷新，否则采用完全刷新。
例子：

CREATE MATERIALIZED VIEW DCP.MATERIALIZED_VIEW_12
BUILD immediate
REFRESH force
ON DEMAND
WITH primary key
DISABLE QUERY REWRITE
AS
SELECT * FROM DCP.TEST;

INSERT INTO DCP.TEST VALUES(16,SYSDATE);
COMMIT;

REFRESH MATERIALIZED VIEW "DCP".MATERIALIZED_VIEW_12 force;

select * from "DCP".MATERIALIZED_VIEW_12;

刷新时机：
1、ON COMMIT 在相关表上视图提交时进行快速刷新。
刷新是由异步线程执行的，因此 COMMIT 执行结束后可能需要等待一段时间物化视图数据才是最新的。

约束： 含有对象类型的不支持； 包含远程表的不支持。

例子：

CREATE MATERIALIZED VIEW DCP.MATERIALIZED_VIEW_13
REFRESH FORCE
ON COMMIT
WITH PRIMARY KEY
DISABLE QUERY REWRITE
AS
SELECT * FROM DCP.TEST;

INSERT INTO DCP.TEST VALUES(17,SYSDATE);
COMMIT;

select * from "DCP".MATERIALIZED_VIEW_13;

2. START WITH … NEXT START WITH 用于指定首次刷新物化视图的时间，NEXT 指定自动刷新的间隔
如果省略 START WITH 则首次刷新时间为当前时间加上 NEXT 指定的间隔； 如果指定 START WITH 省略 NEXT 则物化视图只会刷新一次； 如果二者都未指定物化视图不会自动刷新。

例子:

 --一分钟一刷新
 CREATE MATERIALIZED VIEW DCP.MATERIALIZED_VIEW_14
REFRESH FORCE
START WITH SYSDATE NEXT TIMESTAMPADD(SQL_TSI_MINUTE,1,SYSDATE)
WITH PRIMARY KEY
ENABLE QUERY REWRITE
AS
SELECT * FROM DCP.TEST;

INSERT INTO DCP.TEST VALUES(18,SYSDATE);
COMMIT;

select * from "DCP".MATERIALIZED_VIEW_14;

3. ON DEMAND 由用户通过 REFRESH 语法进行手动刷新。
如果指定了 START WITH 和 NEXT 子句就没有必要指定 ON DEMAND。

例子：

CREATE MATERIALIZED VIEW DCP.MATERIALIZED_VIEW_15
REFRESH FORCE
on demand
WITH PRIMARY KEY
ENABLE QUERY REWRITE
AS
SELECT * FROM DCP.TEST;

INSERT INTO DCP.TEST VALUES(19,SYSDATE);
COMMIT;

REFRESH MATERIALIZED VIEW "DCP".MATERIALIZED_VIEW_15 FORCE;--刷新

select * from "DCP".MATERIALIZED_VIEW_15;

4. NEVER REFRESH 物化视图从不进行刷新。
可以通过 ALTER MATERALIZED VIEW <物化视图 名> FRESH 进行更改。

例子：

CREATE MATERIALIZED VIEW DCP.MATERIALIZED_VIEW_16
BUILD immediate
NEVER REFRESH
DISABLE QUERY REWRITE
AS
SELECT * FROM DCP.TEST;

INSERT INTO DCP.TEST VALUES(20,SYSDATE);
COMMIT;

select * from "DCP".MATERIALIZED_VIEW_16;

ALTER MATERIALIZED VIEW "DCP".MATERIALIZED_VIEW_16 REFRESH COMPLETE;--修改
REFRESH MATERIALIZED VIEW "DCP".MATERIALIZED_VIEW_16 FORCE;--刷新

select * from "DCP".MATERIALIZED_VIEW_16;

刷新选项 ：
1、WITH PRIMARY KEY 默认选项。
（1）只能基于单表。

（2）必须含有PRIMARY KEY 约束，选择列必须直接含有所有的PRIMARY KEY（UPPER(col_name)的形式不可接受） 。

（3）不能含有对象类型。

例子：

CREATE MATERIALIZED VIEW DCP.MATERIALIZED_VIEW_17
BUILD immediate
REFRESH force
on commit
with primary key
DISABLE QUERY REWRITE
AS
SELECT * FROM DCP.TEST;

INSERT INTO DCP.TEST VALUES(21,SYSDATE);
COMMIT;

select * from "DCP".MATERIALIZED_VIEW_17;

2. WITH ROWID
（1）只能基于单表。

（2）不能含有对象类型 。

（3）如果使用 WITH ROWID 的同时使用快速刷新，则必须将 ROWID 提取 出来，和其他列名一起，以别名的形式显示 。

例子：

CREATE MATERIALIZED VIEW LOG ON DCP.TEST
WITH ROWID
PURGE START WITH SYSDATE NEXT SYSDATE+1;

CREATE MATERIALIZED VIEW DCP.MATERIALIZED_VIEW_18(A,B,X)
BUILD immediate
REFRESH fast
ON DEMAND
WITH ROWID
DISABLE QUERY REWRITE
AS
SELECT *,ROWID AS X FROM DCP.TEST;

INSERT INTO DCP.TEST VALUES(22,SYSDATE);
COMMIT;

select * from "DCP".MATERIALIZED_VIEW_18;

REFRESH MATERIALIZED VIEW "DCP".MATERIALIZED_VIEW_18 FAST;