CREATE MATERIALIZED VIEW

功能描述

创建一个新的物化视图。

物化视图功能仅8.3.0及以上集群支持，需联系技术支持开启。

注意事项

• 物化视图的基表可以是行存表、列存表、hstore表、分区表或者指定某个分区、外表，不支持包含临时表（包括全局临时表、volatile临时表和普通临时表），不支持冷热表、不支持对自动分区表指定分区。
• 物化视图禁止INSERT/UPDATE/MERGE INTO/DELETE对数据进行修改。
• 物化视图执行一次将结果并保存，每次查询结果是一致的。BUILD IMMEDIATE或REFRESH后，物化视图可以查询出正确结果。
• 物化视图不能通过语法指定Node Group。支持物化视图的基表指定Node Group创建，物化视图会继承基表NodeGroup信息创建，需要多个基表的NodeGroup相同。
• 创建物化视图时需要schema的CREATE权限和基表或列的SELECT权限。
• 查询物化视图需要物化视图的SELECT权限。
• 刷新需要物化视图的INSERT和基表或列的SELECT权限。
• 物化视图支持ANALYZE/VACUUM/ALTER/DROP等细粒度权限。
• 物化视图支持with grant option的权限传递操作。
• 物化视图不支持更高级别的安全控制，如果基表存在行级访问控制、脱敏策略或owner为私有用户等限制SELECT权限的场景，则禁止创建物化视图；如果已存在物化视图，基表增加RLS、脱敏策略或修改owner为私有用户，则物化视图可以执行查询，但无法刷新。

约束条件

• 不支持包含非immutable函数。
• 不支持包含临时表，包括全局临时表、volatile临时表和普通临时表。
• 不支持物化视图嵌套。
• 不支持物化视图定义中的CTE包含除SELECT语句以外的其他DML。
• 不支持基表上存在脱敏策略和行级访问控制或者基表属于私有用户。脱敏策略和行级访问控制可能会导致结果集错误，而私有用户可能导致刷新物化视图失败等问题。

语法格式

1
2
3
4
5
6
7

CREATE MATERIALIZED VIEW [view_name] [ ( column_name [, ...] ) ]
{{ BUILD { DEFERRED | IMMEDIATE }|
{ REFRESH { COMPLETE }{ ON DEMAND }|{ START WITH (timestmaptz) | EVERY (interval) }…}…| 
{ ENABLE | DISABLE } QUERY REWRITE}| 
{ WITH ( { storage_parameter = value } [, ... ] ) }| 
{ DISTRIBUTE BY { HASH (column [ , ... ]) | ROUNDROBIN | REPLICATION }]
AS query

参数说明

• BUILD DEFERRED | IMMEDIATE

  IMMEDIATE表示创建物化视图时即包含最新数据。

  DEFERRED表示创建物化视图时需要等到第一次REFRESH时才会包含数据。

• REFRESH

  指定物化视图的刷新方式。

  创建物化视图后，物化视图中的数据只反映创建时刻基表的状态。当基表中的数据发生变化时，需要通过刷新物化视图（REFRESH MATERIALIZED VIEW）更新物化视图中的数据。

  • 目前只支持COMPLETE全量刷新这一种刷新方式。执行物化视图定义的查询语句并更新物化视图。
  • 刷新触发方式。

    ON DEMAND：手动按需刷新。

    START WITH (timestamptz) | EVERY (interval)：定时刷新。START WITH指定首次刷新时间。EVERY指定刷新间隔，根据指定的时间定时刷新，支持MONTH、DAY、HOUR、MINUTE及SECOND。

• ENABLE | DISABLE QUERY REWRITE

  是否支持查询重写。默认不支持。

  在指定ENABLE QUERY REWRITE时，需要设置GUC参数 mv_rewrite_rule才能启用物化视图查询重写功能。

  

  查询重写是指在对基表进行查询时， 如果基表上创建有物化视图，数据库系统自动判断是否可以使用物化视图中的预计算结果处理查询。

  如果可以使用某个物化视图，会直接从该物化视图读取预计算结果，起到加速查询的作用。

• WITH ( { storage_parameter = value } [, ... ] )
  • ORIENTATION

    指定表数据的存储方式，即行存方式、列存方式，该参数设置成功后就不再支持修改。

    • 取值范围：
      • ROW，表示表的数据将以行式存储。

        行存储适合于OLTP业务，此类型的表上交互事务比较多，一次交互会涉及表中的多个列，用行存查询效率较高。

      • COLUMN，表示表的数据将以列式存储。

        列存储适合于数据仓库业务，此类型的表上会做大量的汇聚计算，且涉及的列操作较少。

    • 默认值：

      ROW表示创建行存表。

  • 物化视图不支持的存储类型：分区表、h-store表、外表、时序表。
  • enable_foreign_table_query_rewrite

    指定是否允许包含外表的物化视图进行查询重写，需要与ENABLE QUERY REWRITE一起使用。

    外表数据有变化，物化视图无法感知。如果需要对包含外表的物化视图使用查询重写功能，需要指定此选项。

    取值范围：

    • on，允许包含外表的物化视图进行查询重写。
    • off，不允许包含外表的物化视图进行查询重写。

    默认值：off

• DISTRIBUTE BY

  指定表如何在节点之间分布或者复制。

  取值范围：

  • REPLICATION：表的每一行存在所有数据节点（DN）中，即每个数据节点都有完整的表数据。
  • ROUNDROBIN：表的每一行被轮番地发送给各个DN，因此数据会被均匀地分布在各个DN中。（ROUNDROBIN仅8.1.2及以上版本支持）
  • HASH：对指定的列进行Hash，通过映射，把数据分布到指定DN。

  默认值：由参数default_distribution_mode决定。

• AS query

  基于query的结果创建物化视图。

示例

创建基表，并向基表插入数据：

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;

相关链接

ALTER MATERIALIZED VIEW，DROP MATERIALIZED VIEW，REFRESH MATERIALIZED VIEW