CREATE MATERIALIZED VIEW

Function

Creates a materialized view.

The materialized view feature is only supported by clusters 8.3.0 or later versions. If you want to use this feature, reach out to technical support for assistance.

Precautions

• The base table of a materialized view can be a row-store table, column-store table, hstore table, partitioned table, or a specified partition or foreign table. It cannot be a temporary table (global temporary table, volatile temporary tables, and common temporary table) or a hot and cold table, and it cannot be a specified partition of an auto-partitioned table.
• Materialized views do not allow INSERT, UPDATE, MERGE INTO, or DELETE operations.
• The result of each query to the materialized view saved to ensure that the query results are the same each time. After the BUILD IMMEDIATE or REFRESH operation, the correct result can be queried in the materialized view.
• A node group cannot be specified for a materialized view using syntax. A node group can be specified for the base table of a materialized view. The materialized view can inherit the node group information from the base table. The node groups of multiple base tables must be the same.
• To create a materialized view, you must have the CREATE permission for the schema and the SELECT permission for the base table or column.
• To query a materialized view, you must have the SELECT permission on the materialized view.
• To refresh a materialized view, you must have the INSERT permission on the materialized view and SELECT permission on the base table or column.
• Materialized views support fine-grained permissions such as ANALYZE, VACUUM, ALTER, and DROP.
• Materialized views support permission transfer through the transfer with grant option.
• Materialized views do not support higher-level security control. In scenarios where the SELECT permission is restricted, for example, a base table has row-level access control or masking policies or its owner is a private user, materialized views cannot be created. If a materialized view already exists and an RLS or masking policy is added to the base table or the owner of the base table is changed to a private user, the materialized view can be queried but cannot be refreshed.

Constraints

• Non-immutable functions are not supported.
• Temporary tables cannot be included, including global temporary tables, volatile temporary tables, and common temporary tables.
• Materialized views cannot be nested.
• The CTE in the definition of a materialized view cannot contain other DML statements except the SELECT statement.
• The base table of a materialized view cannot have masking policies and row-level access control, or belong to a private user. Masking policies and row-level access control may cause result set errors, while private users may cause problems such as materialized view refresh failures.

Syntax

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

Parameter Description

• BUILD DEFERRED | IMMEDIATE

  IMMEDIATE indicates that the latest data is included when the materialized view is created.

  DEFERRED indicates that data is included only when the materialized view is refreshed for the first time.

• REFRESH

  Specifies the refresh mode of a materialized view.

  After a materialized view is created, the data in the materialized view reflects only the status of the base table at the creation time. When the data in the base table changes, you need to refresh the materialized view (REFRESH MATERIALIZED VIEW) to update the data in the materialized view.

  • Currently, only the COMPLETE refresh mode is supported, which refresh full data in the materialized view. Execute the query statement defined in the materialized view to update the materialized view.
  • Refresh triggering mode.

    ON DEMAND: manual refresh on demand.

    START WITH (timestamptz) | EVERY (interval): scheduled refresh. START WITH specifies the initial refresh time, and EVERY specifies the refresh interval. Data is refreshed periodically based on the specified time.

• ENABLE | DISABLE QUERY REWRITE

  Indicates whether query rewriting is supported. By default, query rewriting is not supported.

  When ENABLE QUERY REWRITE is specified, the GUC parameter mv_rewrite_rule must be set to enable query rewriting for materialized views.

  

  Query rewriting means that when a base table is queried, if a materialized view is created for the base table, the database system automatically determines whether the pre-calculated result in the materialized view can be used to process the query.

  If a materialized view can be used, the pre-calculated result is directly read from the materialized view to accelerate the query.

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

    Specifies the storage mode (row-store, column-store) for table data. This parameter cannot be modified once it is set.

    • Value range:
      • ROW indicates that table data is stored in rows.

        ROW applies to OLTP service, which has many interactive transactions. An interaction involves many columns in the table. Using ROW can improve the efficiency.

      • COLUMN indicates that the data is stored in columns.

        COLUMN applies to the data warehouse service, which has a large amount of aggregation computing, and involves a few column operations.

    • Default value: determined by the GUC parameter default_orientation.
      • row: creates a row-store table.
      • column: creates a column-store table.
      • column enabledelta: creates a column-store table with delta tables enabled.
  • Materialized views do not support the following storage types: partitioned tables, H-store tables, foreign tables, and time series tables.
  • enable_foreign_table_query_rewrite

    Specifies whether to allow query rewriting on materialized views that contain foreign tables. This parameter must be used together with ENABLE QUERY REWRITE.

    The materialized view cannot detect the data changes in the foreign table. Specify this option if you want to enable query rewriting for materialized views that contain foreign tables.

    Value range:

    • on: allows query rewriting on materialized views that contain foreign tables.
    • off: does not allow query rewriting on materialized views that contain foreign tables.

    Default value: off

• DISTRIBUTE BY

  Specifies how the table is distributed or replicated between DNs.

  Value range:

  • REPLICATION: Each row in the table exists on all DNs, that is, each DN has complete table data.
  • ROUNDROBIN: Each row in the table is sent to each DN in turn. Therefore, data is evenly distributed on each DN. This value is supported only in 8.1.2 or later.
  • HASH: Each row of the table will be placed into all the DNs based on the hash value of the specified column.

  Default value: determined by the default_distribution_mode parameter.

• AS query

  Creates a materialized view based on the query result.

Examples

Create a base table and insert data into the base table.

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;

Create a materialized view with the default option BUILD IMMEDIATE.

1

CREATE MATERIALIZED VIEW mv1 AS SELECT * FROM t1;

Create a materialized view in column-store.

1

CREATE MATERIALIZED VIEW mv2 WITH(orientation = column) AS SELECT * FROM t1;

Create a materialized view that is manually refreshed as required.

1

CREATE MATERIALIZED VIEW mv3 BUILD DEFERRED REFRESH ON DEMAND AS SELECT * FROM t1;

Create a materialized view with a scheduled refresh time.

1

CREATE MATERIALIZED VIEW mv4 BUILD DEFERRED REFRESH START WITH(trunc(sysdate)) EVERY (interval'1 day') AS SELECT * FROM t1;

Helpful Links

ALTER MATERIALIZED VIEW, DROP MATERIALIZED VIEW, REFRESH MATERIALIZED VIEW