CREATE INCREMENTAL MATERIALIZED VIEW

Function

CREATE INCREMENTAL MATERIALIZED VIEW creates an incremental materialized view, and you can refresh the data of the materialized view by using REFRESH MATERIALIZED VIEW (full refresh) and REFRESH INCREMENTAL MATERIALIZED VIEW (incremental refresh).

CREATE INCREMENTAL MATERIALIZED VIEW is similar to CREATE TABLE AS, but it remembers the query used to initialize the view, so it can refresh data later. A materialized view has many attributes that are the same as those of a table, but does not support temporary materialized views.

Precautions

• Incremental materialized views cannot be created on temporary tables or global temporary tables.
• Incremental materialized views support only simple filter queries and UNION ALL queries of base tables.
• Distribution columns cannot be specified when an incremental MV is created.
• After an incremental materialized view is created, most DDL operations in the base table are no longer supported.
• IUD operations cannot be performed on incremental materialized views.
• After an incremental materialized view is created, you need to run the REFRESH command to synchronize the materialized view with the base table when the base table data changes.

Syntax

CreateIncrementalMaterializedView ::= CREATE INCREMENTAL MATERIALIZED VIEW mv_name
    [ (column_name [, ...] ) ]
    [ TABLESPACE tablespace_name ]
    AS query;

Parameter Description

• mv_name

  Name (optionally schema-qualified) of the materialized view to be created.

  Value range: a string. It must comply with the naming convention.

• column_name

  Column name in the new materialized view. The materialized view supports specified columns. The number of specified columns must be the same as the number of columns in the result of the subsequent query statement. If no column name is provided, the column name is obtained from the output column name of the query.

  Value range: a string. It must comply with the naming convention.

• TABLESPACE tablespace_name

  Tablespace to which the new materialized view belongs. If not specified, the default tablespace is used.

• AS query

  SELECT, TABLE, or VALUES command This query will be run in a security-constrained operation.

Examples

-- Create an ordinary table.
mogdb=# CREATE TABLE my_table (c1 int, c2 int);
-- Create an incremental materialized view.
mogdb=# CREATE INCREMENTAL MATERIALIZED VIEW my_imv AS SELECT * FROM my_table;
-- Write data to the base table.
mogdb=# INSERT INTO my_table VALUES(1,1),(2,2);
-- Incrementally refresh the incremental materialized view my_imv.
mogdb=# REFRESH INCREMENTAL MATERIALIZED VIEW my_imv;

Helpful Links

ALTER MATERIALIZED VIEW， CREATE MATERIALIZED VIEW，CREATE TABLE， DROP MATERIALIZED VIEW，REFRESH INCREMENTAL MATERIALIZED VIEW ，REFRESH MATERIALIZED VIEW