CREATE MATERIALIZED VIEW

功能描述

使用CREATE MATERIALIZED VIEW语句创建一个物化视图，其为包含query查询结果的数据库对象。query查询的FROM子句可以命名表、视图和其他实例化视图。

创建增量物化视图时会自动创建物化视图日志，用户可在日志中查看相关内容。

注意事项

该功能仅在数据库兼容模式为Oracle时支持（即数据库初始化时指定DBCOMPATIBILITY='A'）。
用户可通过视图GS_MATVIEW可以查看上次刷新点。如下语句以物化视图test_mv_ic_1162985_1为例：
```
select * from gs_matview where matviewid =(select oid from pg_class where relname='test_mv_ic_1162985_1');
```
创建增量物化视图时会自动创建物化视图日志：
- 若先创建增量物化视图，再创建物化视图日志，由于创建物化视图时已经自动创建了物化视图日志，则会出现报错。
- 若先创建了物化视图日志，再创建增量物化视图，由于检测到物化视图日志已存在，则增量物化视图时不会创建物化视图日志，仅正常创建增量物化视图。
删除物化视图时：
- 若物化视图日志为跟随增量物化视图自动创建的，则删除物化视图也会随之删除物化视图日志。
- 若物化视图日志为手动创建的，则在删除物化视图时不会删除物化视图日志。
删除物化视图日志语法如下：
```
drop materialized view log on table_name;
```
删除基表时：
- 无论是自动创建还是手动创建的物化视图日志，都会被删除。
- 若删除时使用CASCADE则级联删除物化视图。否则出现报错。

语法格式

创建物化视图

create materialized view [ view_name ] REFRESH FAST|COMPLETE|FORCE ON DEMAND [start with ... next] AS { query_block};

创建物化视图日志

create materialized view log on table_name;

创建增量物化视图时会自动创建物化视图日志，用户也可以在创建增量物化视图之前手动创建物化视图日志。

参数说明

view_name

要创建的物化视图的名称（可以被模式限定）。

取值范围：字符串，要符合标识符的命名规范。
REFRESH

指定刷新方式的标识符。
complete|fast|force
- complete：代表创建全量物化视图。
- fast：代表创建增量物化视图。
- force：代表尝试创建增量物化视图，如果创建增量物化视图失败，则创建全量物化视图。
on demand

表示是按需刷新也即手动刷新。
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、查看系统表中最新生成的物化视图的属性。

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表示增量物化视图，f表示全量物化视图：

 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;

示例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、查看系统表中最新生成的物化视图的属性。

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表示增量物化视图，f表示全量物化视图：

 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;

VastbaseG100

CREATE MATERIALIZED VIEW

功能描述

注意事项

语法格式

参数说明

示例

相关链接

弹框头部