CREATE MATERIALIZED VIEW|V4.4.2| docs|Distributed Database

CREATE MATERIALIZED VIEW

Last Updated：2026-03-06 07:02:42 Updated

Purpose

This statement is used to create a materialized view.

A materialized view is a special type of database object that stores a copy of the query results and is periodically refreshed (or manually refreshed) to keep the data up to date. Materialized views can contain operations such as aggregations, joins, and subqueries, and can be indexed and partitioned to further improve performance.

Privilege requirements

You must have the CREATE TABLE privilege to create a materialized view. For more information about the privileges of OceanBase Database, see Privilege types in MySQL-compatible mode.

Syntax

CREATE MATERIALIZED VIEW view_name [([column_list] [PRIMARY KEY(column_list)])]
    [table_option_list]
    [partition_option]
    [mv_column_group_option]
    [refresh_clause]
    [query_rewrite_clause]
    [on_query_computation_clause]
    AS view_select_stmt;

column_list:
    column_name [, column_name ...]

refresh_clause:
    REFRESH [refresh_option [nested_refresh_option]] [mv_refresh_on_clause]
    | NEVER REFRESH

refresh_option:
    COMPLETE
    | FAST
    | FORCE

nested_refresh_option:
    INDIVIDUAL
    | INCONSISTENT
    | CONSISTENT

mv_refresh_on_clause:
    [ON DEMAND] [[START WITH expr] [NEXT expr]]

query_rewrite_clause:
    DISABLE QUERY REWRITE
    | ENABLE QUERY REWRITE

on_query_computation_clause:
    DISABLE ON QUERY COMPUTATION
    | ENABLE ON QUERY COMPUTATION

mv_column_group_option:
    WITH COLUMN GROUP(all columns)
    | WITH COLUMN GROUP(each column)
    | WITH COLUMN GROUP(all columns, each column)

Parameters

Note

You cannot directly create an index in the statement for creating a materialized view. You can use the CREATE INDEX or ALTER TABLE statement to create an index for a materialized view.

Parameter	Description
view_name	The name of the materialized view to be created.
column_list	Optional. The list of columns of the materialized view. If you want to specify the names of the view columns explicitly, you can use the column_list clause and separate the column names with commas. Note The view must have unique column names, just like the base table. By default, the column names of the view are the same as those retrieved in the `SELECT` statement. The number of column names specified in the `column_list` clause must be the same as the number of columns retrieved in the `SELECT` statement.
column_name	The name of the column of the materialized view. By default, the column names retrieved in the `SELECT` statement are used as the view column names.
PRIMARY KEY	The primary key of the materialized view. Notice If you do not specify the column names of the materialized view, the column names used in the primary key of the materialized view must be the same as those retrieved in the `SELECT` statement. If you specify the column names of the materialized view, you must separate the column names from the `PRIMARY KEY` definition with a comma.
table_option_list	Optional. The table options of the materialized view. Like a regular table, a materialized view can have table options. For more information, see CREATE TABLE.
partition_option	Optional. The partitioning options of the materialized view. Like a regular table, a materialized view can be partitioned. For more information, see CREATE TABLE.
mv_column_group_option	Optional. The storage format of the materialized view. If you do not specify this option, a rowstore materialized view is created by default. For more information, see mv_column_group_option.
refresh_clause	Optional. The refresh method of the materialized view. For more information, see refresh_clause.
mv_refresh_on_clause	Optional. The refresh timing of the materialized view. For more information, see mv_refresh_on_clause.
query_rewrite_clause	Optional. Specifies whether to enable automatic query rewriting for the materialized view. For more information, see query_rewrite_clause.
on_query_computation_clause	Optional. Specifies whether the materialized view is a real-time materialized view. For more information, see on_query_computation_clause.
view_select_stmt	The query (SELECT) statement for defining the data of the materialized view. This statement is used to retrieve data from the base table and store the result in the materialized view. The syntax of `view_select_stmt` is the same as that of a regular `SELECT` statement. For more information, see SELECT statement. Note OceanBase Database allows you to create a full-refresh materialized view with an external table as the base table. OceanBase Database allows you to add the `AS OF PROCTIME()` clause to the base table when you create a materialized view. If you use the `AS OF PROCTIME()` clause outside the base table definition, an error is returned. The `AS OF PROCTIME()` clause is used to skip the refresh of this table during incremental refreshes. In addition, an mlog is not required for the `AS OF PROCTIME()` table. OceanBase Database allows you to declare a regular view as a dimension table (`AS OF PROCTIME()`) and use it as the base table for incremental-refresh materialized views.

mv_column_group_option

WITH COLUMN GROUP(all columns): Specifies to create a materialized view in rowstore format.
WITH COLUMN GROUP(each column): Specifies to create a materialized view in columnstore format.
WITH COLUMN GROUP(all columns, each column): Specifies to create a materialized view in rowstore-columnstore redundant format.

Note

You can run the SHOW CREATE TABLE view_name; or SHOW CREATE VIEW view_name; statement to view the definition of the materialized view and determine its storage format.

refresh_clause

REFRESH [refresh_option [nested_refresh_option]] [mv_refresh_on_clause]: Specifies the refresh method of the materialized view.
- refresh_option: specifies the refresh method of the materialized view. This is an optional parameter. If you do not specify any refresh method, the default value is FORCE.
  - COMPLETE: specifies a complete refresh. A complete refresh recalculates all data in the materialized view to ensure that the data in the materialized view is consistent with that in the source table.
  - FAST: specifies an incremental refresh. An incremental refresh refreshes only the data in the materialized view that is related to the changes in the source table, without recalculating the entire materialized view.
    Notice
    - When you use the REFRESH FAST method to refresh a materialized view, you must create a materialized view log (mlog) for the base table before you create the materialized view.
    - Columns used in the incremental refresh of the materialized view must be in the mlog.
    For more information about how to create a materialized view log, see Materialized view logs.
  - FORCE: the default value. Specifies a hybrid refresh. First, an incremental refresh is attempted. If the incremental refresh fails, a complete refresh is performed.
  - nested_refresh_option: specifies the refresh strategy of the nested materialized view. This is an optional parameter. If you do not specify a refresh strategy, the default value is INDIVIDUAL. Valid values:
    - INDIVIDUAL: the default value. Specifies an independent refresh.
    - INCONSISTENT: specifies a cascading refresh that does not maintain consistency.
    - CONSISTENT: specifies a cascading refresh that maintains consistency.
    Note
    
    For a non-nested materialized view, no cascading refresh is performed regardless of the specified refresh strategy. In this case, the refresh strategy is ignored and an independent refresh is performed. The three specified refresh strategies take effect only for background tasks. When you manually use the PL package (DBMS_MVIEW.REFRESH) to schedule a refresh, the refresh is performed based on the specified PL parameters.
NEVER REFRESH: specifies that the materialized view does not need to be refreshed. That is, the materialized view is refreshed only when it is created and cannot be refreshed again after it is created.

mv_refresh_on_clause

Note

Except for using the mv_refresh_on_clause clause to define the refresh plan, you can also manually refresh a materialized view by calling the dbms_mviews.refresh stored procedure.

ON DEMAND: an optional parameter. Specifies to refresh the materialized view on demand.
[[START WITH expr] [NEXT expr]]: an optional parameter. Specifies the refresh interval.
- [START WITH expr]: an optional parameter. Specifies the time expression for the start of the refresh plan.
- [NEXT expr]: an optional parameter. Specifies the time expression for the next refresh.
  Notice
  - If you use the NEXT clause, the time expression of the refresh plan must be set to a future time. Otherwise, an error will be returned.
  - If you want to schedule the automatic refresh of the materialized view periodically, set the scheduling cycle by using the NEXT clause.

We recommend that you use sysdate() to represent the current time in the current time zone. Here is an example of a time expression:

START WITH sysdate() NEXT sysdate() + INTERVAL 1 DAY

The preceding clause indicates that the materialized view is refreshed every 1 day starting from the current time (sysdate()).

query_rewrite_clause

Notice

This feature applies only to materialized views that contain only the SELECT JOIN and WHERE clauses, that is, SPJ queries. Materialized views that do not meet the conditions will not be used for rewriting, but no error will be returned.

DISABLE QUERY REWRITE: the default value. Specifies that automatic rewriting is disabled for the current materialized view.
ENABLE QUERY REWRITE: specifies that automatic rewriting is enabled for the current materialized view.

For more information about materialized view rewriting, see Materialized view query rewriting.

on_query_computation_clause

DISABLE ON QUERY COMPUTATION: the default value, which specifies to create a regular materialized view.
ENABLE ON QUERY COMPUTATION: specifies to create a real-time materialized view.
Notice
- Materialized views that use the MIN or MAX function do not support real-time materialized views.
- Aggregated materialized views with outer joins do not support real-time materialized views.
- Materialized views with set queries do not support real-time materialized views.
- Nested materialized views cannot be created as real-time materialized views.

For more information about real-time materialized views, see the Create a real-time materialized view section in Create a materialized view.

Examples

Create a table named test_tbl1.

CREATE TABLE test_tbl1 (col1 INT PRIMARY KEY, col2 VARCHAR(20), col3 INT, col4 INT);

Create a materialized view named mv_test_tbl1. Partition the materialized view by the col1 column into 8 partitions. Refresh the materialized view using the complete refresh method, starting from the current date and refreshing every 1 day. Query the records in the test_tbl1 table that satisfy the condition col3 >= 30 as the base table and use the query results as the data for the materialized view.
```
CREATE MATERIALIZED VIEW mv_test_tbl1
  PARTITION BY HASH(col1) PARTITIONS 8
  REFRESH COMPLETE START WITH sysdate() NEXT sysdate() + INTERVAL 1 DAY
  AS SELECT col1, col2, col3
    FROM test_tbl1
    WHERE col3 >= 30;
```

Create a materialized view named mv2_test_tbl1 and specify the primary key.

CREATE MATERIALIZED VIEW mv2_test_tbl1(c1, c2, c3, PRIMARY KEY(c1))
      AS SELECT col1, col2, col3
        FROM test_tbl1;

Create a columnar format materialized view named mv3_test_tbl1.

CREATE MATERIALIZED VIEW mv3_test_tbl1
  WITH COLUMN GROUP(each column)
  AS SELECT col1, col2, col3
    FROM test_tbl1;

View the definition of the materialized view mv3_test_tbl1.
```
SHOW CREATE VIEW mv3_test_tbl1;
```

CREATE MATERIALIZED VIEW

Purpose

Privilege requirements

Syntax

Parameters

Note

Note

Notice

Note

mv_column_group_option

Note

refresh_clause

Notice

Note

mv_refresh_on_clause

Note

Notice

query_rewrite_clause

Notice

on_query_computation_clause

Notice

Examples

References