CREATE MATERIALIZED VIEW
功能描述
创建新的物化视图。物化视图语法仅8.2.1.220及以上集群支持。
物化视图(Materialized View)是数据库中一种特殊类型的数据库对象。它将复杂计算的查询结果预计算并存储在数据库中,实现加速查询的目的。
适用于频繁对大量数据进行复杂的查询和分析场景,使用物化视图可以预先计算和存储该场景的查询结果,显著提高查询性能。
物化视图权限要求
- 创建权限:创建物化视图时需要schema的CREATE权限和基表或列的SELECT权限。
- 查询权限:查询物化视图需要物化视图的SELECT权限。
- 刷新权限:刷新需要物化视图的INSERT和基表或列的SELECT权限。
- 其他权限:
物化视图支持ANALYZE/VACUUM/ALTER/DROP等细粒度权限。
物化视图支持with grant option的权限传递操作。
- 安全管理:
物化视图不支持更高级别的安全控制,如果基表存在行级访问控制、脱敏策略或owner为私有用户等限制SELECT权限的场景,则禁止创建物化视图;
如果已存在物化视图,基表增加RLS、脱敏策略或修改owner为私有用户,则物化视图可以执行查询,但无法刷新。
注意事项
- 使用CREATE MATERIALIZED VIEW语句需确保enable_matview设置为on。
- 物化视图的基表可以是行存表、列存表、hstore表、冷热表(910.200及以上集群版本支持)、分区表或者指定某个分区、外表、其他物化视图。
- 不支持包含临时表(包括全局临时表、volatile临时表和普通临时表),不支持对自动分区表指定分区。
- 不支持物化视图定义中的CTE包含除SELECT语句以外的其他DML。
- 不支持基表上存在脱敏策略和行级访问控制或者基表属于私有用户。脱敏策略和行级访问控制可能会导致结果集错误,而私有用户可能导致刷新物化视图失败等问题。
- 物化视图不支持的存储类型:外表、时序表。
- 物化视图禁止INSERT/UPDATE/MERGE INTO/DELETE对数据进行修改。
- 物化视图执行一次将结果并保存,每次查询结果是一致的。BUILD IMMEDIATE或REFRESH后,物化视图可以查询出正确结果。
- 物化视图不能通过语法指定Node Group。支持物化视图的基表指定Node Group创建,物化视图会继承基表NodeGroup信息创建,需要多个基表的NodeGroup相同。
语法格式
1 2 3 4 5 6 7 8 |
CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] materialized_view_name [ ( column_name [, ...] ) ] [ BUILD { DEFERRED | IMMEDIATE } ] [ REFRESH [ [DEFAULT] | [ RESTRICT ] | [ CASCADE FORWARD ] | [ CASCADE BACKWARD ] | [ CASCADE ALL ] ] [ COMPLETE ] [ ON DEMAND ] [ [ START WITH (timestamptz) ] | [ EVERY (interval) ] ] ] [ { ENABLE | DISABLE } QUERY REWRITE ] [ WITH ( {storage_parameter = value} [, ... ] ) ] [ DISTRIBUTE BY { REPLICATION | ROUNDROBIN | { HASH ( column_name [,...] ) } } ] [ PARTITION BY {(column_name) | date_trunc('time_unit', column_name)} ] AS query; |
参数说明
参数 |
描述 |
取值范围/默认值 |
---|---|---|
BUILD DEFERRED | IMMEDIATE |
IMMEDIATE,表示创建物化视图时即包含最新数据。 DEFERRED,表示创建物化视图时需要等到第一次refresh时才会包含数据。 |
- |
REFRESH |
指定物化视图的刷新方式。 创建物化视图后,物化视图中的数据只反映创建时刻基表的状态。当基表中的数据发生变化时,需要通过刷新物化视图(REFRESH MATERIALIZED VIEW)更新物化视图中的数据。 |
|
ENABLE | DISABLE QUERY REWRITE |
是否开启查询重写。 查询重写是指在对基表进行查询时, 如果基表上创建有物化视图,数据库系统自动判断是否可以使用物化视图中的预计算结果处理查询。
如果可以使用某个物化视图,会直接从该物化视图读取预计算结果,起到加速查询的作用。
说明:
DWS当前仅支持文本匹配查询重写。 |
默认关闭。 指定ENABLE QUERY REWRITE时,需要设置GUC参数mv_rewrite_rule才能启用物化视图查询重写功能。 |
WITH |
创建物化视图指定的相关参数。 |
参见表2。 |
DISTRIBUTE BY |
指定表如何在节点之间分布或者复制。 |
REPLICATION、ROUNDROBIN或HASH:
默认值:由参数default_distribution_mode决定。
说明:
当物化视图指定为hash分布时,可能存在数据倾斜。可使用普通表检查数据是否倾斜的方法对物化视图进行检查,详细内容请参考《数据仓库服务开发指南》的“查看数据倾斜状态”章节。若物化视图的数据存储存在倾斜时,可参考《数据仓库服务开发指南》的“数据倾斜调优”章节进行存储层的倾斜调优。 |
PARTITION BY |
创建分区物化视图语法仅9.1.0.200及以上集群版本支持。
注意:
|
|
AS query |
基于query的结果创建物化视图。 |
- |
参数 |
描述 |
取值范围 |
---|---|---|
ORIENTATION |
指定表数据的存储方式,即行存方式、列存方式,该参数设置成功后就不再支持修改。 |
ROW或COLUMN:
默认值:ROW |
enable_foreign_table_query_rewrite |
指定是否允许包含外表的物化视图进行查询重写,需要与ENABLE QUERY REWRITE一起使用。 外表数据有变化,物化视图无法感知。如果需要对包含外表的物化视图使用查询重写功能,需要指定此选项。 |
默认值:off |
bitmap_columns |
bitmap index只适用于hstore_opt表,只有开启表级参数enable_hstore_opt且开启bitmap_columns='指定列'。该参数仅9.1.0.200及以上集群版本支持。 |
- |
secondary_part_num |
指定列存表二级分区的数量,仅适用于HStore列存表。该参数仅9.1.0.200及以上集群版本支持。 |
1~32。 默认值:8 |
enable_hstore_opt |
enable_hstore_opt表级参数打开时会默认同时打开enable_hstore表级参数。该参数仅9.1.0.200及以上集群版本支持。 |
true或者false。 默认值:false |
enable_turbo_store |
用于控制是否创建为turbo表(基于列存表实现)。该参数只对列存表有效。该参数仅9.1.0.200及以上集群版本支持。 |
on或者off。 默认值:off |
mv_analyze_mode |
控制物化视图自动analyze的方式。该参数仅9.1.0.200及以上集群版本支持。 |
默认值:light |
mv_pck_column |
物化视图的局部聚簇存储,列存表导入数据时按照指定的列(单列或多列),进行局部排序。该参数仅9.1.0.200及以上集群版本支持。 开启mv_pck_column='指定列'。 |
- |
mv_support_function_type |
开启物化视图创建时查询语句中可以使用的函数属性。该参数仅9.1.0.200及以上集群版本支持。 |
默认值:空 |
excluded_inactive_tables |
指定的基表发生数据变化不会失效该物化视图。该参数仅9.1.0.200及以上集群版本支持。 设置方式为:excluded_inactive_tables='schemaName1.tableName1,schemaName2.tableName2' |
默认值:空 |
force_rewrite_timeout |
在刷新后的指定时间间隔内,可以查询重写,无论此物化视图数据是否新鲜。该参数仅9.1.0.200及以上集群版本支持。 |
单位为秒,默认值:0。 |
TABLESPACE tablespace_name |
声明一个表空间。用于V3存储格式的创建,如果default_tablespace为空,将使用数据库的缺省表空间。该参数仅9.1.0.200及以上集群版本支持。 |
- |
示例
创建基表,并向基表插入数据。
1 2 |
CREATE TABLE t1 (a int, b int) DISTRIBUTE BY HASH(a); INSERT INTO t1 SELECT x,x FROM generate_series(1,10) x; |
创建默认BUILD IMMEDIATE方式的物化视图:
1
|
CREATE MATERIALIZED VIEW mv1 AS SELECT * FROM t1; |
创建指定列存方式的物化视图。
1
|
CREATE MATERIALIZED VIEW mv2 WITH(orientation = column) AS SELECT * FROM t1; |
创建手动按需刷新的物化视图。
1
|
CREATE MATERIALIZED VIEW mv3 BUILD DEFERRED REFRESH ON DEMAND AS SELECT * FROM t1; |
创建指定刷新时间的物化视图。
1
|
CREATE MATERIALIZED VIEW mv4 BUILD DEFERRED REFRESH START WITH(trunc(sysdate)) EVERY (interval '1 day') AS SELECT * FROM t1; |
创建带有bitmap index的物化视图。
1 2 |
CREATE MATERIALIZED VIEW mv1 with (ORIENTATION = COLUMN, enable_hstore=true, enable_hstore_opt=on, bitmap_columns='col1') AS SELECT * FROM base_table; |
创建物化视图并指定列存表二级分区的数量。
1 2 |
CREATE MATERIALIZED VIEW mv WITH (ORIENTATION=COLUMN, ENABLE_HSTORE=ON, enable_hstore_opt=on, mv_pck_column='c3', secondary_part_column = 'c2', secondary_part_num = 8) AS SELECT * FROM base_table; |
创建物化视图并指定pck列进行排序。
1 2 |
CREATE MATERIALIZED VIEW mv WITH (ORIENTATION=COLUMN, ENABLE_HSTORE=ON, enable_hstore_opt=on, mv_pck_column='col3') AS SELECT * FROM base_table; |
创建物化视图并指定其analyze的方式。
1
|
CREATE MATERIALIZED VIEW mv1 enable query rewrite with(excluded_inactive_tables='matview_basic."T1",matview_basic."a=b"',mv_analyze_mode='none') as SELECT * FROM base_table; |
创建V3物化视图:
1 2 3 |
CREATE MATERIALIZED VIEW mv1 with (orientation=column, enable_hstore=true, compression=low, enable_hstore_opt=on, COLVERSION = 3.0) TABLESPACE cu_obs_tbs distribute by hash(scope_name) AS SELECT * FROM dicttbl_low; |
创建包含外表的物化视图进行查询重写。
1
|
CREATE MATERIALIZED VIEW mv1 with (enable_foreign_table_query_rewrite = true) as SELECT * FROM base_table; |
创建物化视图指定查询语句中可以使用volatile函数。
1
|
CREATE MATERIALIZED VIEW mv_date with(mv_support_function_type = 'volatile') as select to_date(a) from t_date; |