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

CREATE MATERIALIZED VIEW

Last Updated：2025-11-18 14:33:32 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 can be manually refreshed) to keep the data up to date. Materialized views can include 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 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 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, 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 the column name of the materialized view is not specified, the column names used in the primary key of the materialized view must be the same as those retrieved in the `SELECT` statement. If the column names of the materialized view are specified, a comma must be placed between the last column name in the column name list and the `PRIMARY KEY` clause.
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 partition options of the materialized view. Like a regular table, a materialized view can have partition options. For more information, see CREATE TABLE.
mv_column_group_option	Optional. The storage format of the materialized view. If this option is not specified, a row-based 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) used to define the data of the materialized view. This statement is used to retrieve data from the base table and store the results 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 For OceanBase Database V4.3.5: Starting from V4.3.5 BP2, you can create a full-refresh materialized view with a standard view or an external table as the base table. Starting from V4.3.5 BP4, you can add the `AS OF PROCTIME()` clause to the base table when you create a materialized view. If the `AS OF PROCTIME()` clause is used outside the base table in a materialized view creation statement, an error is returned. The `AS OF PROCTIME()` clause is used to specify that the refresh of this table is skipped during an incremental refresh. The `AS OF PROCTIME()` table does not require an mlog. Starting from V4.3.5 BP5, a standard view declared as a dimension table (`AS OF PROCTIME()`) can be used as the base table for an incremental-refresh materialized view.

mv_column_group_option

WITH COLUMN GROUP(all columns): specifies to create a materialized view in row store format.
WITH COLUMN GROUP(each column): specifies to create a materialized view in column store format.
WITH COLUMN GROUP(all columns, each column): specifies to create a materialized view in redundant row and column store format.

Note

You can use 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 for the materialized view.
- refresh_option: specifies the refresh method for the materialized view. This is an optional parameter. If you do not specify a refresh method, the default value is FORCE. Valid values:
  - COMPLETE: specifies to perform a complete refresh. This means that the entire data of the materialized view is recalculated to ensure that the data in the view is completely consistent with the source table.
  - FAST: specifies to perform an incremental refresh. This means that only the data related to changes in the source table is refreshed, avoiding the need to recalculate the entire view.
    Notice
    - When you use the REFRESH FAST method, you must create a materialized view log (mlog) for the base table before you create the materialized view. This is because the REFRESH FAST method uses the records in the materialized view log to determine which data to incrementally refresh.
    - All columns used in the incremental refresh of the materialized view must be included in the mlog.
    For more information about how to create a materialized view log, see Materialized view logs.
  - FORCE: the default value. Specifies to perform 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 for nested materialized views. 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 to perform an independent refresh.
    - INCONSISTENT: specifies to perform a cascading inconsistent refresh.
    - CONSISTENT: specifies to perform a cascading consistent refresh.
    Note
    - Starting from OceanBase Database V4.3.5 BP3, the parameter nested_refresh_option (specifies the refresh strategy for nested materialized views) is supported in OceanBase Database V4.3.5.
    - For non-nested materialized views, there is no cascading refresh behavior. Therefore, regardless of the refresh strategy you specify, the default value of `INDIVIDUAL` is used. The three specified refresh strategies only take effect in background tasks. When you manually schedule a refresh using the PL package (DBMS_MVIEW.REFRESH), the refresh is performed based on the specified PL parameters.
NEVER REFRESH: specifies that the materialized view does not need to be refreshed. This means that the materialized view is only refreshed when it is created and cannot be refreshed again after creation.

mv_refresh_on_clause

Note

Except for using the mv_refresh_on_clause clause to define a 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 start time of the refresh plan.
- [NEXT expr]: an optional parameter. Specifies the next refresh time.
  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 triggered.
  - If you want the materialized view to periodically and automatically schedule refresh tasks, define the NEXT clause to set the scheduling cycle.

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 is applicable 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 reported as errors, but will not be used for rewriting.

DISABLE QUERY REWRITE: the default value. Specifies to disable automatic rewriting for the materialized view.
ENABLE QUERY REWRITE: specifies to enable automatic rewriting for the 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.

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. Hash 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 test_tbl1 table for records where col3 >= 30 and use these records as the base table 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

Examples

References