Help Center/ GaussDB(DWS)/ SQL Syntax Reference/ SQL Syntax Reference (9.1.0.x)/ DDL Syntax/ CREATE MATERIALIZED VIEW

Updated on 2025-07-22 GMT+08:00

View PDF

CREATE MATERIALIZED VIEW

Function

CREATE MATERIALIZED VIEW creates a materialized view.

A materialized view is a special database object. It pre-computes complex query results and stores them in the database to accelerate queries.

When a large amount of data is frequently queried and analyzed, materialized views can be used to pre-calculate and store query results, significantly improving query performance.

Materialized View Permissions

To create a materialized view, you must have the CREATE permission on the schema and the SELECT permission on 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 view and the SELECT permission on the base table or column.
Other permissions:
Materialized views support fine-grained permissions such as ANALYZE, VACUUM, ALTER, and DROP.

Materialized views support WITH GRANT OPTION,which means that the recipient of the permission can in turn grant it to others.
Security:
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.

Precautions

Before running CREATE MATERIALIZED VIEW, ensure that enable_matview is set to on.
The base table of a materialized view can be a row-store table, column-store table, HStore table, hot and cold table (supported by clusters of version 910.200 or later), partitioned table, specified partition, foreign table, or another materialized view.
Temporary tables (including global temporary tables, volatile temporary tables, and common temporary tables) are not supported. Partitions cannot be specified for partitioned tables that are automatically created.
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 data masking policies or row-level access control. It also cannot be owned by a private user. The usage of masking policies and row-level access control may lead to errors in the result set, while materialized views owned by private users may cause refresh failures.
Materialized views do not support the following storage types: foreign tables and time series tables.
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.

Syntax

     
          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;

Parameter Description

**Table 1** CREATE MATERIALIZED VIEW parameters
Parameter	Description	Value Range/Default Value
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 stored in the materialized view reflects only the base table status 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): automatic refresh at a scheduled time. START WITH specifies the first refresh time. EVERY specifies the automatic refresh interval and its value can be MONTH, DAY, HOUR, MINUTE, or SECOND.
ENABLE \| DISABLE QUERY REWRITE	Specifies whether to enable query rewrite. When ENABLE QUERY REWRITE is specified to enable query rewrite for materialized views, the GUC parameter mv_rewrite_rule must be specified. Query rewrite means that when a base table is queried and 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 improve the query.	Disabled by default.
WITH	Specifies the parameters for creating a materialized view.	For details, see Table 2.
DISTRIBUTE BY	Specifies how the table is distributed or replicated between DNs.	Value range: REPLICATION, ROUNDROBIN, or HASH 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. NOTE: When the materialized view is distributed in hash mode, data skew may occur. To check for data skews in materialized views, follow the same procedures used for detecting data skews in regular tables. For details, see "Checking for Data Skew" in the Data Warehouse Service (DWS) Developer Guide. If data skew is identified in a materialized view, perform data skew optimization at the storage layer by referring to "Optimizing Data Skew" in the Data Warehouse Service (DWS) Developer Guide.
AS query	Creates a materialized view based on the query result.	-

**Table 2** WITH parameters
Parameter	Description	Value Range
ORIENTATION	Specifies the storage mode (row-store, column-store) for table data. This parameter cannot be modified once it is set.	Value range: ROW or COLUMN 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: ROW
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.	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
bitmap_columns	The bitmap index is only applicable to the hstore_opt table. To use it, enable the table-level parameter enable_hstore_opt and set bitmap_columns to the specified column. This is supported only by clusters of version 9.1.0.200 or later.	-
secondary_part_num	Specifies the number of level-2 partitions in a column-store table. This parameter applies only to H-Store column-store tables. This is supported only by clusters of version 9.1.0.200 or later.	Value range: 1 to 32 Default value: 8
enable_hstore_opt	When the enable_hstore_opt table-level parameter is enabled, the enable_hstore table-level parameter is also automatically enabled by default. This is supported only by clusters of version 9.1.0.200 or later.	Value range: true or false Default value: false
enable_turbo_store	Determines whether to create a turbo table (column-store tables). The parameter is only valid for column-store tables. This is supported only by clusters of version 9.1.0.200 or later.	Value range: on or off Default value: off
mv_analyze_mode	Determines the automatic analysis method for materialized views. This is supported only by clusters of version 9.1.0.200 or later.	none: indicates that the materialized view does not automatically execute ANALYZE after being refreshed. light: indicates that the materialized view performs light analysis after being refreshed. Default value: light
mv_pck_column	Specifies a partial clustering storage for a materialized view. During the data import process to a column-store table, the data is partially sorted according to the specified column(s). This is supported only by clusters of version 9.1.0.200 or later. Enable mv_pck_column and set it to a specified column.	-
mv_support_function_type	Enables the use of function attributes in the query statements when creating materialized views. This is supported only by clusters of version 9.1.0.200 or later.	stable: indicates that functions of the STABLE and IMMUTABLE types can be used in query statements. volatile: indicates that functions of the VOLATILE, STABLE, and IMMUTABLE types can be used in query statements. Default value: empty
excluded_inactive_tables	Specifies that the materialized view will not be invalidated when there are data changes in the underlying base table. This is supported only by clusters of version 9.1.0.200 or later. Set excluded_inactive_tables to schemaName1.tableName1,schemaName2.tableName2.	Default value: empty
force_rewrite_timeout	Allows query rewrite within a specified time interval after refresh, regardless of the freshness of the data in the materialized view. This is supported only by clusters of version 9.1.0.200 or later.	The unit is second. The default value is 0.
TABLESPACE tablespace_name	Specifies a tablespace for V3 storage. If default_tablespace is empty, the default tablespace of the database is used. This is supported only by clusters of version 9.1.0.200 or later.	-

Examples

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

     
          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.

     
          CREATE MATERIALIZED VIEW mv1 AS SELECT * FROM t1;

Create a materialized view in column-store mode.

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

Create a materialized view that is manually refreshed as required.

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

Create a materialized view with a scheduled refresh time.

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

Create a materialized view with a bitmap index.

     
          CREATE MATERIALIZED VIEW mv1
with (ORIENTATION = COLUMN, enable_hstore=true, enable_hstore_opt=on, bitmap_columns='col1')  AS SELECT * FROM base_table;

Create a materialized view and specify the number of level-2 partitions in a column-store table.

     
          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;

Create a materialized view and specify the PCK column for sorting.

     
          CREATE MATERIALIZED VIEW mv
WITH (ORIENTATION=COLUMN, ENABLE_HSTORE=ON, enable_hstore_opt=on, mv_pck_column='col3')  AS SELECT * FROM base_table;

Create a materialized view and specify the analysis method.

     
          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;

Create a V3 materialized view.

     
          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;

Create a materialized view that contains a foreign table for query rewriting.

     
          CREATE MATERIALIZED VIEW mv1 with (enable_foreign_table_query_rewrite = true) as SELECT * FROM base_table;

Create a materialized view and specify that volatile functions can be used in the query statement.

     
          CREATE MATERIALIZED VIEW mv_date with(mv_support_function_type = 'volatile') as select to_date(a) from t_date;

Helpful Links

ALTER MATERIALIZED VIEW, DROP MATERIALIZED VIEW, REFRESH MATERIALIZED VIEW

Parent Topic: DDL Syntax

Previous topic: CREATE INDEX

Next topic: CREATE PROCEDURE

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.

The system is busy. Please try again later.

Which of the following issues have you encountered?

Content is inconsistent with the product UI

Unclear descriptions

Lack of examples or code

Incorrect steps

Can't find what I need

Lack of best practices

Feedback (optional)

0/500

Select at least one type of issue, and enter your comments or suggestions.

Enter a maximum of 500 characters.

Submit Cancel

For any further questions, feel free to contact us through the chatbot.

Chatbot