创建嵌套物化视图
DWS支持创建嵌套物化视图,嵌套物化视图是指在物化视图的定义中使用其他物化视图的过程。 通过嵌套物化视图,可以将多个物化视图组合在一起,形成更复杂和综合的查询结果。
通过GUC参数mv_max_cascade_depth指定嵌套物化视图的最大深度,为了刷新性能上的考虑,当前最大深度为10,默认值为3。
使用约束
1. 不支持预聚合物化视图嵌套。
2. 不支持基于物化视图创建预聚合物化视图。
3. 不支持非定时刷新物化视图指定或者修改刷新方式。
4. 嵌套物化视图最大深度为10,暂不支持更深的嵌套层数。
5. 升级场景不支持创建嵌套物化视图。
语法格式
创建嵌套物化视图的语法基本同物化视图的语法一致,物化视图的定义语句中允许基表是物化视图。增加refresh_behavior用于定时刷新物化视图指定刷新方式。
CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] materialized_view_name [ ( column_name [, ...] ) ] [ BUILD { DEFERRED | IMMEDIATE } ] [ REFRESH [ refresh_behavior] [ COMPLETE ] [ ON DEMAND ] [ [ START WITH (timestamptz) ] | [ EVERY (interval) ] ] ] [ { ENABLE | DISABLE } QUERY REWRITE ] [ WITH ( {storage_parameter = value} [, ... ] ) ] [ TABLESPACE tablespace_name ] [ DISTRIBUTE BY { REPLICATION | ROUNDROBIN | { HASH ( column_name [,...] ) } } ] [ PARTITION BY {(column_name) | date_trunc('time_unit', column_name)} ] AS query;
查看嵌套物化视图
\d+ pg_matview_depend Table "pg_catalog.pg_matview_depend" Column | Type | Modifiers | Storage | Stats target | Description -------------------+-----------+-----------+---------+--------------+------------- matviewid | oid | not null | plain | | relid | oid | not null | plain | | partids | oidvector | not null | plain | | contain_entire_rel | boolean | not null | plain | | Indexes: "pg_matview_depend_mvid_relid_index" UNIQUE, btree (matviewid, relid) TABLESPACE pg_default "pg_matview_depend_relid_index" btree (relid) TABLESPACE pg_default Replica Identity: NOTHING Has OIDs: no
示例
CREATE TABLE t1(a int, b int, c int); CREATE TABLE t2(a int, b int, c int); CREATE MATERIALIZED VIEW mv1 ENABLE QUERY REWRITE as select * from t1 WHERE a = 1; CREATE MATERIALIZED VIEW mv2 ENABLE QUERY REWRITE as select * from t2 WHERE b = 1; CREATE MATERIALIZED VIEW mv3 ENABLE QUERY REWRITE as select * from mv2 WHERE b = 1; CREATE MATERIALIZED VIEW mv4 ENABLE QUERY REWRITE as select t1.a as t1a,t1.b as t1b,t1.c as t1c,t2.a as t2a,t2.b as t2b,t2.c as t2c from t1 left join t2 on t1.a = t2.a WHERE t2.b = 1; CREATE MATERIALIZED VIEW mv5 ENABLE QUERY REWRITE as (select * from mv1) union all (select * from t1 WHERE a = 1); CREATE MATERIALIZED VIEW mv6 ENABLE QUERY REWRITE as select * from mv4 left join (select * from mv1 union all select * from mv5)mv5 on mv4.t1a = mv5.a;
2. 上述SQL中先创建两个表t1和t2,然后以t1和t2为基表,创建了物化视图mv1和mv2;再以t1、t2、mv1、mv2为基表依次创建物化视图mv3、mv4、mv5和mv6。
和单层物化视图相比,嵌套物化视图顶层的定义更为简洁,使用上更为灵活。
3. 可通过下图了解基表和物化视图的创建关系及依赖关系。

4. 当底层数据发生变更时,需要刷新物化视图,保证物化视图中的数据都是有效的。即t1表数据发生变化,mv1、mv4、mv5和mv6中的数据都需要更新。使用级联刷新单个物化视图,自动将相关联的物化视图都进行更新。
REFRESH MATERIALIZED VIEW mv6 cascade backward;
5. 当嵌套物化视图中的数据都是有效的,执行查询语句时,会进行自动识别和匹配,将查询语句自动重写为查询物化视图,大大减少查询语句中的计算。