CREATE MATERIALIZED VIEW
功能描述
使用CREATE MATERIALIZED VIEW语句创建一个物化视图，其为包含query查询结果的数据库对象。query查询的FROM子句可以命名表、视图和其他实例化视图。
创建增量物化视图时会自动创建物化视图日志，用户可在日志中查看相关内容。
注意事项
该功能仅在数据库兼容模式为Oracle时支持（即数据库实例初始化时指定DBCOMPATIBILITY='A'）。
用户可通过视图GS_MATVIEW可以查看上次刷新点。其中matview_name为物化视图名：
SELECT * FROM GS_MATVIEW WHERE MATVIEWID =(SELECT OID FROM PG_CLASS WHERE RELNAME='matview_name');创建增量物化视图时会自动创建物化视图日志：
若先创建增量物化视图，再创建物化视图日志，由于创建物化视图时已经自动创建了物化视图日志，则会出现报错。
若先创建了物化视图日志，再创建增量物化视图，由于检测到物化视图日志已存在，则增量物化视图时不会创建物化视图日志，仅正常创建增量物化视图。
删除物化视图时：
若物化视图日志为跟随增量物化视图自动创建的，则删除物化视图也会随之删除物化视图日志。
若物化视图日志为手动创建的，则在删除物化视图时不会删除物化视图日志。
删除物化视图日志语法如下：
DROP MATERIALIZED VIEW LOG ON table_name;删除基表时：
无论是自动创建还是手动创建的物化视图日志，都会被删除。
若删除时使用
CASCADE则级联删除物化视图，否则出现报错。
语法格式
创建物化视图
CREATE MATERIALIZED VIEW [ view_name ] REFRESH FAST|COMPLETE|FORCE [ON DEMAND | ON COMMIT] [START WITH ... NEXT] AS { query_block};创建物化视图日志
CREATE MATERIALIZED VIEW LOG ON table_name;
创建增量物化视图时会自动创建物化视图日志，用户也可以在创建增量物化视图之前手动创建物化视图日志。
参数说明
view_name
要创建的物化视图的名称（可以被模式限定）。
取值范围：字符串，要符合标识符的命名规范。
REFRESH
指定刷新方式的标识符。
COMPLETE|FAST|FORCE
COMPLETE：代表创建全量物化视图。
FAST：代表创建增量物化视图。
FORCE：代表尝试创建增量物化视图，如果创建增量物化视图失败，则创建全量物化视图。
ON DEMAND
表示是按需刷新，即手动刷新。
ON COMMIT
表示在每次事务提交时更新物化视图。
要使ON COMMIT的属性生效，应开启enable_on_commit_matview参数。
START WITH…NEXT
START WITH…NEXT配合ON DEMAND一起使用，让用户可以指定物化视图的刷新时间间隔。
当仅指定 START WITH 不指定NEXT时表示仅自动刷新一次。
query_block
一个SELECT、TABLE或者VALUES命令。这个查询将在一个安全受限的操作中运行。
示例
示例1 增量物化视图自动刷新。
1、创建测试表并插入数据。
CREATE TABLE table_1162985( col1 INT, col2 VARCHAR(255), col3 TEXT, col4 INT ); INSERT INTO table_1162985 VALUES (4, 'Apple', 'Red', 55), (2, 'Banana', 'Yellow', 56), (1, 'Cherry', 'Red', 45), (3, 'Durian', 'Green', 35), (5, 'Apple', 'black', 66), (5, '', 'black', 66);2、创建物化视图。
创建物化视图，用于查询全部列的结果。
START WITH SYSDATE NEXT SYSDATE + 1/1440表示当前创建的系统时间1分钟后执行首次刷新任务，且后续持续定期刷新。
CREATE MATERIALIZED VIEW test_mv_ic_1162985_1 refresh FAST ON DEMAND START WITH SYSDATE NEXT SYSDATE + 1/1440 AS SELECT * FROM table_1162985;创建物化视图，用于查询部分列的结果。
CREATE MATERIALIZED VIEW test_mv_ic_1162985_2 REFRESH FAST ON DEMAND START WITH SYSDATE NEXT SYSDATE + 1/1440 as SELECT col1, col2 FROM table_1162985;
3、查看物化视图。
SELECT * FROM test_mv_ic_1162985_1; SELECT * FROM test_mv_ic_1162985_2;返回结果为如下：
col1 | col2 | col3 | col4 ------+--------+--------+------ 4 | Apple | Red | 55 2 | Banana | Yellow | 56 1 | Cherry | Red | 45 3 | Durian | Green | 35 5 | Apple | black | 66 5 | | black | 66 (6 rows) col1 | col2 ------+-------- 4 | Apple 2 | Banana 1 | Cherry 3 | Durian 5 | Apple 5 | (6 rows)4、查看系统表中最新生成的物化视图的属性，其中ivm字段的t表示增量物化视图，f表示全量物化视图。
SELECT IVM FROM gs_matview WHERE MATVIEWID = (SELECT OID FROM pg_class WHERE RELNAME LIKE 'test_mv_ic_1162985_1'); SELECT IVM FROM gs_matview WHERE MATVIEWID = (SELECT OID FROM pg_class WHERE RELNAME LIKE 'test_mv_ic_1162985_2');返回结果为：
ivm ----- t (1 row) ivm ----- t (1 row)5、修改查询列。执行如下语句更新，并执行匿名块等待1分钟。
UPDATE table_1162985 SET COL2 = 'peach' WHERE col1 = 3; BEGIN dbms_lock.sleep(60); END; /6、查看物化视图，数据得到刷新。
SELECT * FROM test_mv_ic_1162985_1; SELECT * FROM test_mv_ic_1162985_2;返回结果为如下：
col1 | col2 | col3 | col4 ------+--------+--------+------ 4 | Apple | Red | 55 2 | Banana | Yellow | 56 1 | Cherry | Red | 45 5 | Apple | black | 66 5 | | black | 66 3 | peach | Green | 35 (6 rows) col1 | col2 ------+-------- 4 | Apple 2 | Banana 1 | Cherry 5 | Apple 5 | 3 | peach (6 rows)7、环境清理。
DROP MATERIALIZED VIEW test_mv_ic_1162985_1; DROP MATERIALIZED VIEW test_mv_ic_1162985_2; DROP TABLE table_1162985;示例2 基表增删改表数据，增量和全量物化视图自动刷新。
1、创建测试表并插入数据。
CREATE TABLE table_1162973(col1 int,col2 int,col3 text); INSERT INTO table_1162973 VALUES(1,2,'test'),(1,3,'test2'),(2,2,'test3'),(3,4,'test4');2、创建增量和全量物化视图。
CREATE MATERIALIZED VIEW test_mv_ic_1162973 REFRESH FAST ON DEMAND START WITH SYSDATE NEXT SYSDATE + 1/1440 AS SELECT * FROM table_1162973; CREATE MATERIALIZED VIEW test_mv_1162973 REFRESH COMPLETE ON DEMAND START WITH SYSDATE NEXT SYSDATE + 1/1440 AS SELECT count(*) FROM table_1162973;START WITH SYSDATE NEXT SYSDATE + 1/1440表示当前创建的系统时间1分钟后执行首次刷新任务，且后续持续定期刷新。
3、查看物化视图数据。
SELECT * FROM test_mv_ic_1162973; SELECT * FROM test_mv_1162973;结果返回为如下：
col1 | col2 | col3 ------+------+------- 1 | 2 | test 1 | 3 | test2 2 | 2 | test3 3 | 4 | test4 (4 rows) count ------- 4 (1 row)4、查看系统表中最新生成的物化视图的属性，ivm字段的t表示增量物化视图，f表示全量物化视图。
SELECT IVM FROM GS_MATVIEW WHERE MATVIEWID = (SELECT OID FROM PG_CLASS WHERE RELNAME = 'test_mv_ic_1162973'); SELECT IVM FROM GS_MATVIEW WHERE MATVIEWID = (SELECT OID FROM PG_CLASS WHERE RELNAME = 'test_mv_1162973');返回结果为：
ivm ----- t (1 row) ivm ----- f (1 row)5、对基表进行DML操作，操作后执行匿名块等待1分钟。
INSERT INTO table_1162973 VALUES(6,6,'test6'); DELETE FROM table_1162973 WHERE col1 = 1; UPDATE table_1162973 SET col3='UPDATE' WHERE col1 = 2; --等待1分钟 BEGIN dbms_lock.sleep(60); END; /6、查看物化视图，数据得到刷新。
SELECT * FROM test_mv_ic_1162973; SELECT * FROM test_mv_1162973;返回结果为：
col1 | col2 | col3 ------+------+-------- 3 | 4 | test4 6 | 6 | test6 2 | 2 | UPDATE (3 rows) count ------- 3 (1 row)7、环境清理。
DROP MATERIALIZED VIEW test_mv_ic_1162973; DROP MATERIALIZED VIEW test_mv_1162973; DROP TABLE table_1162973;示例3: 设置物化视图为ON COMMIT刷新方式，在事务提交的同时更新物化视图。
1、开启enable_on_commit_matview参数，支持设置ON COMMIT修饰物化视图，从而设置事务提交时更新物化视图。
修改enable_on_commit_matview参数后需要重启数据库生效，可执行
vb_ctl restart。echo "enable_on_commit_matview=on" >> $PGDATA/postgresql.conf vb_ctl restart2、创建测试表。
CREATE TABLE table_1202561(col1 int,col2 int); INSERT INTO table_1202561 values(1,2),(1,3),(2,2),(3,4);3、分别创建增量和全量物化视图。
CREATE MATERIALIZED VIEW test_mv_1202561_1 REFRESH FAST ON COMMIT AS SELECT * FROM table_1202561; CREATE MATERIALIZED VIEW test_mv_1202561_2 REFRESH COMPLETE ON COMMIT AS SELECT * FROM table_1202561;4、查询基表和物化视图。
SELECT * FROM table_1202561; SELECT * FROM test_mv_1202561_1; SELECT * FROM test_mv_1202561_2;返回结果为：
col1 | col2 ------+------ 1 | 2 1 | 3 2 | 2 3 | 4 (4 rows) col1 | col2 ------+------ 1 | 2 1 | 3 2 | 2 3 | 4 (4 rows) col1 | col2 ------+------ 1 | 2 1 | 3 2 | 2 3 | 4 (4 rows)5、向测试表中插入数据，并查询基表和物化视图，数据将被刷新。
INSERT INTO table_1202561 VALUES(5,5); SELECT * FROM table_1202561; SELECT * FROM test_mv_1202561_1; SELECT * FROM test_mv_1202561_2;返回结果为：
col1 | col2 ------+------ 1 | 2 1 | 3 2 | 2 3 | 4 5 | 5 (5 rows) col1 | col2 ------+------ 1 | 2 1 | 3 2 | 2 3 | 4 5 | 5 (5 rows) col1 | col2 ------+------ 1 | 2 1 | 3 2 | 2 3 | 4 5 | 5 (5 rows)6、清理环境。对enable_on_commit_matview参数的修改在重启数据库后生效。
DROP MATERIALIZED VIEW test_mv_1202561_1; DROP MATERIALIZED VIEW test_mv_1202561_2; DROP TABLE table_1202561; ALTER SYSTEM SET enable_on_commit_matview TO off;相关链接
ALTER MATERIALIZED VIEW、DROP MATERIALIZED VIEW
