CREATE INCREMENTAL MATERIALIZED VIEW

Description

CREATE INCREMENTAL MATERIALIZED VIEW creates a fast-refresh materialized view, and you can refresh the data of the materialized view by using REFRESH MATERIALIZED VIEW (complete-refresh) and REFRESH INCREMENTAL MATERIALIZED VIEW (fast-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 database link tables, temporary tables, or global temporary tables.
Fast-refresh materialized views support only simple filter queries and UNION ALL queries of base tables.
The distribution key cannot be specified when a fast-refresh materialized view is created.
After a fast-refresh materialized view is created, most DDL operations in the base table are no longer supported.
The IUD operation cannot be performed on fast-refresh materialized views.
After a fast-refresh 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.
The Ustore does not support the creation and use of materialized views.

Syntax

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

Parameters

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
(Optional) Tablespace to which the new materialized view belongs. If the tablespace is not specified, the default tablespace is used.
AS query
SELECT or TABLE command. This query will be run in a security-constrained operation.

Examples

-- Create a tablespace.
gaussdb=# CREATE TABLESPACE tbs_data1 RELATIVE LOCATION 'tablespace1/tbs_data1';

-- Create an ordinary table.
gaussdb=# CREATE TABLE my_table (c1 int, c2 int);

-- Create a fast-refresh materialized view.
gaussdb=# CREATE INCREMENTAL MATERIALIZED VIEW my_imv (col1,col2) TABLESPACE tbs_data1 AS SELECT * FROM my_table;

-- Write data to the base table.
gaussdb=# INSERT INTO my_table VALUES(1,1);

-- Query fast-refresh materialized view data.
gaussdb=# SELECT * FROM my_imv;
 col1 | col2 
------+------
(0 rows)

-- Fast refresh the fast-refresh materialized view my_imv.
gaussdb=# REFRESH INCREMENTAL MATERIALIZED VIEW my_imv;

-- Query fast-refresh materialized view data.
gaussdb=# SELECT * FROM my_imv;
 col1 | col2 
------+------
    1 |    1
(1 row)

-- Delete a fast-refresh materialized view.
gaussdb=# DROP MATERIALIZED VIEW my_imv;

-- Delete the ordinary table my_table.
gaussdb=# DROP TABLE my_table;

-- Delete the tablespace.
gaussdb=# DROP TABLESPACE tbs_data1;