Help Center/ Data Warehouse Service/ SQL Syntax Reference/ SQL Syntax Reference (9.1.1.x)/ DDL Syntax/ Views and Materialized Views/ CREATE MATERIALIZED VIEW

Updated on 2026-07-02 GMT+08:00

CREATE MATERIALIZED VIEW

Function

CREATE MATERIALIZED VIEW creates a materialized view. This syntax is supported only in clusters of version 8.2.1.220 or later.

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, UPDATE, DELETE permissions on the materialized view and the SELECT permission on the base tables or columns.
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

To use CREATE MATERIALIZED VIEW, ensure that enable_matview is set to on.
The base tables of a materialized view can be row-store tables, column-store tables, HStore tables, hot/cold tables (supported in clusters of version 9.1.0.200 or later), partitioned tables or specified partitions, foreign tables, or other materialized views.
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 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.
Materialized views do not support the following storage types: foreign tables and time series tables.
Materialized views must not be directly modified using INSERT, UPDATE, MERGE INTO, or DELETE. Data can only be refreshed using the REFRESH command.
A materialized view executes once and saves the result. Each query returns consistent results. 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 [ [DEFAULT] | [ RESTRICT ] | [ CASCADE FORWARD ] | [ CASCADE BACKWARD ] | [ CASCADE ALL ] ]
          [ COMPLETE | FAST ]
          [ [ ON DEMAND ] | [ ON STATEMENT ] | [ ON COMMIT ] ]
          [ [ START WITH (timestamptz) ] | [ EVERY (interval) ] ] ]
[ { ENABLE | DISABLE } QUERY REWRITE ]
[ WITH ( {storage_parameter = value} [, ... ] ) ]
[ DISTRIBUTE BY { REPLICATION | ROUNDROBIN | { HASH ( column_name [,...] ) } } ]
[ PARTITION BY {(column_name) | date_trunc('time_unit', column_name)} ]
AS query;
 
 
  

Parameter Description

**Table 1** CREATE MATERIALIZED VIEW parameters
Parameter	Description	Value Range
BUILD DEFERRED \| IMMEDIATE	IMMEDIATE indicates that the materialized view contains the latest data at the time of creation. DEFERRED indicates that the materialized view will contain data only after the first REFRESH.	-
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.	Force mode: DEFAULT (default refresh), RESTRICT (forced refresh). You can only specify the refresh method for materialized views that are asynchronously refreshed in the background. Refresh direction: The refresh direction when materialized views are used in a cascading/nested manner. FORWARD (upward refresh), BACKWARD (downward refresh), ALL (refresh all). You can only specify the refresh direction for materialized views that are asynchronously refreshed in the background. For details, see REFRESH MATERIALIZED VIEW. Refresh method: COMPLETE: full refresh. Executes the query statement defined by the materialized view and updates the materialized view. FAST: incremental refresh. Extracts the incremental data from each base table since the last refresh, performs incremental computation, and writes the results to the materialized view. Refresh triggering mode. ON DEMAND: manual on-demand refresh. ON COMMIT: When a transaction modifying a base table is committed, all materialized views on that base table are triggered to refresh. This ensures strong data consistency between the base table and the materialized view, but it affects the data write speed of the base table. Use with caution. ON STATEMENT: When a DML statement modifying a base table is executed, all materialized views on that base table are triggered to refresh. This ensures strong data consistency between the base table and the materialized view, but it affects the data write speed of the base table. Use with caution. ON COMMIT and ON STATEMENT are not supported under the repeatable read transaction isolation level. An error will be reported if triggered. START WITH (timestamptz) \| EVERY (interval): scheduled automatic refresh. START WITH specifies the first refresh time, and EVERY specifies the refresh interval. Automatic refresh occurs periodically based on the specified time. The value can be MONTH, DAY, HOUR, MINUTE, or SECOND.
ENABLE \| DISABLE QUERY REWRITE	Specifies whether to enable query rewrite. 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 accelerate the query.	Disabled by default. When ENABLE QUERY REWRITE is specified, you need to configure the GUC parameter mv_rewrite_rule to enable query rewrite of materialized views.
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: Each row of the table is stored on all DNs, meaning every DN has a complete copy of the 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.
PARTITION BY	Creates a partitioned materialized view. This parameter is supported only in clusters of version 9.1.0.200 or later. Constraints and limitations of partitioned materialized views: Materialized views only support range partitioning. Merging and splitting of base table partitions are not supported. The partition key of a materialized view must match that of the queried table's partitioned table to ensure partition-based mapping for invalidation and refresh. The partition key of a materialized view must be included in the outermost SQL statement of the query to enable partition key-based updates to the materialized view. Columns that may produce NULL values in an outer join cannot be used as partition keys. The queried table can be a composite partition. If multiple partition tables have the same partition conditions, the mapping between the queried table and the materialized view is automatically established. Partition mapping of foreign tables is not supported because data changes in foreign tables cannot be detected.	Proportional association: Specify the partition key of a partitioned table in the query as the partition key of the materialized view, creating a one-on-one mapping relationship. Roll-up association: Use the date_trunc function to roll up the partition key of a partitioned table in the query, establishing a roll-up mapping relationship. The data type should be compatible with the date_trunc function.
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.	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 storage applies to the data warehouse service, which has a large amount of aggregation computing, and involves a few column operations. Default value: ROW
bitmap_columns	A bitmap index is applicable only to hstore_opt tables. It is effective only when the table-level parameter enable_hstore_opt is enabled and bitmap_columns is set to a specified column. This parameter is supported only in clusters of version 9.1.0.200 or later.	-
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
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 parameter is supported only in clusters of version 9.1.0.200 or later.	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 parameter is supported only in clusters of version 9.1.0.200 or later.	on or off Default value: off
excluded_inactive_tables	Specifies that data changes in the designated base tables will not invalidate the materialized view. This parameter is supported only in clusters of version 9.1.0.200 or later. Set excluded_inactive_tables to schemaName1.tableName1,schemaName2.tableName2.	Default value: empty
force_rewrite_timeout	Within the specified time interval after a refresh, query rewriting is allowed regardless of whether the data in the materialized view is fresh. This parameter is supported only in clusters of version 9.1.0.200 or later.	The unit is second. The default value is 0.
mv_analyze_mode	Determines the automatic analysis method for materialized views. This parameter is supported only in 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_computing_resource	Specifies the elastic resource pool for materialized view refresh, which can reduce the impact of the refresh on the current cluster resources. This parameter is supported only in clusters of version 9.1.1.200 or later.	Default value: empty
mv_incremental_mode	Specifies the format of the incremental materialized view. This parameter is supported only in clusters of version 9.1.1.200 or later.	Empty: indicates that incremental computation is performed with the aid of an auxiliary table. Performance is optimal. v2: indicates that incremental computation is performed without the aid of an auxiliary table. Default value: empty
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 parameter is supported only in clusters of version 9.1.0.200 or later. Enable mv_pck_column and set it to a specified column.	Default value: empty
mv_support_function_type	Enables the use of function attributes in the query statements when creating materialized views. This parameter is supported only in 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
mv_refresh_guc	It is required for refreshing the current materialized view. This parameter is available only for clusters of version 9.1.1.100 or later.	Default value: empty
secondary_part_num	Specifies the number of secondary partitions for a column-store table. Applicable only to HStore column-store tables. This parameter is supported only in clusters of version 9.1.0.200 or later.	1~32 Default value: 8
TABLESPACE tablespace_name	Specifies a tablespace for V3 storage. If default_tablespace is empty, the default tablespace of the database is used. This parameter is supported only in clusters of version 9.1.0.200 or later.	-

Examples

Enable the materialized view function.

     set enable_matview = on;

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

     DROP TABLE IF EXISTS t1;
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.

     DROP TABLE IF EXISTS base_table;
CREATE TABLE base_table (col1 varchar(50), col2 int , col3 int) WITH (ORIENTATION=COLUMN);
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 secondary partitions for a column-store table.

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

Create a materialized view and specify PCK columns for sorting.

     CREATE MATERIALIZED VIEW mv2
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.

     DROP TABLE IF EXISTS dicttbl_low;
CREATE TABLE dicttbl_low (col1 int, col2 int ) WITH (ORIENTATION=COLUMN);
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(col1)
AS SELECT * FROM dicttbl_low;
 
 
  

Create a materialized view that contains a foreign table for query rewriting. base_table_ft in this example is a foreign table.

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

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

     DROP TABLE IF EXISTS t_date;
CREATE TABLE t_date (a int, b int ) WITH (ORIENTATION=COLUMN);
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: Views and Materialized Views

Previous topic: DROP VIEW

Next topic: ALTER MATERIALIZED VIEW

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.

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

Chatbot