-
示例 -- 修改表的默认类型
gaussdb=# set enable_default_ustore_table=off;
-- 准备数据
CREATE TABLE t1(c1 int, c2 int);
INSERT INTO t1 VALUES(1, 1);
INSERT INTO t1 VALUES(2, 2);
-- 创建全量物化视图
gaussdb=# CREATE MATERIALIZED VIEW mv AS select count(*) from t1;
CREATE MATERIALIZED VIEW
-- 查询物化视图结果
gaussdb=# SELECT * FROM mv;
count
-------
2
(1 row)
-- 再次向物化视图中基表插入数据
gaussdb=# INSERT INTO t1 VALUES(3, 3);
-- 对全量物化视图做全量刷新
gaussdb=# REFRESH MATERIALIZED VIEW mv;
REFRESH MATERIALIZED VIEW
-- 查询物化视图结果
gaussdb=# SELECT * FROM mv;
count
-------
3
(1 row)
-- 删除物化视图，删除表
gaussdb=# DROP MATERIALIZED VIEW mv;
DROP MATERIALIZED VIEW
gaussdb=# DROP TABLE t1;
DROP TABLE
-
示例 -- 修改表的默认类型
gaussdb=# set enable_default_ustore_table=off;
--准备数据。
gaussdb=# CREATE TABLE t1(c1 int, c2 int);
gaussdb=# INSERT INTO t1 VALUES(1, 1);
gaussdb=# INSERT INTO t1 VALUES(2, 2);
--创建全量物化视图。
gaussdb=# CREATE MATERIALIZED VIEW mv AS select count(*) from t1;
CREATE MATERIALIZED VIEW
--查询物化视图结果。
gaussdb=# SELECT * FROM mv;
count
-------
2
(1 row)
--向物化视图中基表插入数据。
gaussdb=# INSERT INTO t1 VALUES(3, 3);
INSERT 0 1
--对全量物化视图做全量刷新。
gaussdb=# REFRESH MATERIALIZED VIEW mv;
REFRESH MATERIALIZED VIEW
--查询物化视图结果。
gaussdb=# SELECT * FROM mv;
count
-------
3
(1 row)
--删除物化视图，删除表。
gaussdb=# DROP MATERIALIZED VIEW mv;
DROP MATERIALIZED VIEW
gaussdb=# DROP TABLE t1;
DROP TABLE
-
语法格式 创建全量物化视图 CREATE MATERIALIZED VIEW [ view_name ] AS { query_block }; 全量刷新物化视图 REFRESH MATERIALIZED VIEW [ view_name ]; 删除物化视图 DROP MATERIALIZED VIEW [ view_name ]; 查询物化视图 SELECT * FROM [ view_name ];
-
示例 -- 准备数据
CREATE TABLE t1(c1 int, c2 int);
INSERT INTO t1 VALUES(1, 1);
INSERT INTO t1 VALUES(2, 2);
-- 创建全量物化视图
openGauss=# CREATE MATERIALIZED VIEW mv AS select count(*) from t1;
CREATE MATERIALIZED VIEW
-- 查询物化视图结果
openGauss=# SELECT * FROM mv;
count
-------
2
(1 row)
-- 再次向物化视图中基表插入数据
openGauss=# INSERT INTO t1 VALUES(3, 3);
-- 对全量物化视图做全量刷新
openGauss=# REFRESH MATERIALIZED VIEW mv;
REFRESH MATERIALIZED VIEW
-- 查询物化视图结果
openGauss=# SELECT * FROM mv;
count
-------
3
(1 row)
-- 删除物化视图
openGauss=# DROP MATERIALIZED VIEW mv;
DROP MATERIALIZED VIEW
-
语法格式 创建全量物化视图 CREATE MATERIALIZED VIEW [ view_name ] AS { query_block }; 全量刷新物化视图 REFRESH MATERIALIZED VIEW [ view_name ]; 删除物化视图 DROP MATERIALIZED VIEW [ view_name ]; 查询物化视图 SELECT * FROM [ view_name ];
-
示例 --准备数据。
openGauss=# CREATE TABLE t1(c1 int, c2 int);
openGauss=# INSERT INTO t1 VALUES(1, 1);
openGauss=# INSERT INTO t1 VALUES(2, 2);
--创建全量物化视图。
openGauss=# CREATE MATERIALIZED VIEW mv AS select count(*) from t1;
CREATE MATERIALIZED VIEW
--查询物化视图结果。
openGauss=# SELECT * FROM mv;
count
-------
2
(1 row)
--向物化视图中基表插入数据。
openGauss=# INSERT INTO t1 VALUES(3, 3);
INSERT 0 1
--对全量物化视图做全量刷新。
openGauss=# REFRESH MATERIALIZED VIEW mv;
REFRESH MATERIALIZED VIEW
--查询物化视图结果。
openGauss=# SELECT * FROM mv;
count
-------
3
(1 row)
--删除物化视图。
openGauss=# DROP MATERIALIZED VIEW mv;
DROP MATERIALIZED VIEW
-
示例 -- 准备数据CREATE TABLE t1(c1 int, c2 int);INSERT INTO t1 VALUES(1, 1);INSERT INTO t1 VALUES(2, 2);-- 创建全量物化视图gaussdb=# CREATE MATERIALIZED VIEW mv AS select count(*) from t1;CREATE MATERIALIZED VIEW-- 查询物化视图结果gaussdb=# SELECT * FROM mv; count ------- 2(1 row)-- 再次向物化视图中基表插入数据gaussdb=# INSERT INTO t1 VALUES(3, 3);-- 对全量物化视图做全量刷新gaussdb=# REFRESH MATERIALIZED VIEW mv;REFRESH MATERIALIZED VIEW-- 查询物化视图结果gaussdb=# SELECT * FROM mv; count ------- 3(1 row)-- 删除物化视图gaussdb=# DROP MATERIALIZED VIEW mv;DROP MATERIALIZED VIEW
-
语法格式 创建全量物化视图 CREATE MATERIALIZED VIEW [ view_name ] AS { query_block }; 刷新全量物化视图 REFRESH MATERIALIZED VIEW [ view_name ]; 删除物化视图 DROP MATERIALIZED VIEW [ view_name ]; 查询物化视图 SELECT * FROM [ view_name ];
