文档首页/ 数据仓库服务 GaussDB(DWS)/ SQL语法参考/ SQL语法参考(9.1.0.x)/ DDL语法/ CREATE MATERIALIZED VIEW
更新时间：2024-12-30 GMT+08:00
查看PDF
分享

CREATE MATERIALIZED VIEW

功能描述

创建一个新的物化视图。

注意事项

  • 物化视图的基表可以是行存表、列存表、hstore表、分区表或者指定某个分区、外表、其他物化视图，不支持包含临时表（包括全局临时表、volatile临时表和普通临时表），支持冷热表（910.200及以上版本支持）、不支持对自动分区表指定分区。
  • 物化视图禁止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为私有用户，则物化视图可以执行查询，但无法刷新。

约束条件

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

语法格式

1
2
3
4
5
6
7
 
CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] materialized_view_name [ ( column_name [, ...] ) ]
[ BUILD { DEFERRED | IMMEDIATE } ]
[ REFRESH  [ COMPLETE ] [ ON DEMAND ] [ [ START WITH (timestamptz) ] | [ EVERY (interval) ] ] ]
[ { ENABLE | DISABLE } QUERY REWRITE ]
[ WITH ( {storage_parameter = value} [, ... ] ) ]
[ DISTRIBUTE BY { REPLICATION | ROUNDROBIN | { HASH ( column_name [,...] ) } } ]
AS query;

1
2
3
4
5
6
7
 
CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] materialized_view_name
[ ( column_name [, ...] ) ]
[ BUILD IMMEDIATE ]
REFRESH AUTO
ENABLE QUERY REWRITE
[ WITH ( {storage_parameter = value} [, ... ] ) ]
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。

  • REFRESH AUTO

    同步物化视图的刷新方式。

  • ENABLE | DISABLE QUERY REWRITE

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

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

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

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

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

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

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

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

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

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

      • 默认值：

        ROW表示创建行存表。

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

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

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

      取值范围：

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

      默认值：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及以上集群版本支持。

      默认值：false

    • enable_turbo_store

      用于控制是否创建为turbo表（基于列存表实现）。该参数只对列存表有效。该参数仅9.1.0.200及以上集群版本支持。

      默认值：off

    • mv_analyze_mode

      控制物化视图自动analyze的方式。该参数仅9.1.0.200及以上集群版本支持。

      取值范围：

      none，表示物化视图在刷新后不自动执行analyze。

      light，表示物化视图在刷新后执行light analyze。

      默认值：light

    • mv_pck_column

      物化视图的局部聚簇存储，列存表导入数据时按照指定的列（单列或多列），进行局部排序。该参数仅9.1.0.200及以上集群版本支持。

      开启mv_pck_column='指定列'。

    • mv_support_function_type

      开启物化视图创建时查询语句中可以使用的函数属性。该参数仅9.1.0.200及以上集群版本支持。

      取值范围：

      stable，表示支持在查询语句中使用类型为STABLE和IMMUTABLE的函数。

      volatile，表示支持在查询语句中使用类型为VOLATILE，STABLE和IMMUTABLE的函数。

      默认值：空

    • 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及以上集群版本支持。

  • 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;

创建带有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;

相关链接

ALTER MATERIALIZED VIEW，DROP MATERIALIZED VIEW，REFRESH MATERIALIZED VIEW

父主题： DDL语法