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

CREATE MATERIALIZED VIEW

Last Updated：2025-11-27 02:38:06 Updated

Purpose

You can use this statement to create a materialized view.

A materialized view is a special database object that stores the latest copy of query results and is periodically or manually refreshed. Materialized views can contain operations such as aggregation and join operations, and subqueries, and can be indexed and partitioned to further enhance the query performance.

Required privileges

You have the CREATE TABLE privilege. For more information about privileges in OceanBase Database, see Privilege types in Oracle 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 [COMPLETE | FAST | FORCE] [mv_refresh_on_clause]
    | NEVER REFRESH

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 by using the statement that creates a materialized view. To create an index for a materialized view, you need to use the CREATE INDEX statement.

Parameter	Description
view_name	The name of the materialized view to be created.
column_list	Optional. The list of columns in the materialized view. You can use the `column_list` clause to specify column names for the view and separate them with commas (,). Note The column names in a materialized view must be unique, as in a base table. By default, a materialized view uses the column names retrieved by the `SELECT` statement. The number of column names specified in the `column_list` clause must be equal to the number of columns retrieved by the `SELECT` statement.
column_name	The name of a column in the materialized view. By default, the column names retrieved by the `SELECT` statement are used as the column names in the view.
PRIMARY KEY	The primary key of the materialized view. Notice If the column names are not specified for the materialized view, the column names used in the primary key must be the same as those retrieved by the `SELECT` statement. When you specify the column names and primary key for a materialized view, use a comma (,) to separate the list of column names from the `PRIMARY KEY` definition.
table_option_list	Optional. The table options of the materialized view. You can separately set table options for a materialized view just like you do for a normal table. For more information about the parameters, see CREATE TABLE.
partition_option	Optional. The partitioning options for the materialized view. You can separately set partitioning options for a materialized view just like you do for a normal table. For more information about the parameters, see CREATE TABLE.
mv_column_group_option	Optional. The storage format of the materialized view. If you do not specify the `mv_column_group_option` option, a rowstore materialized view is created by default. For more information, see mv_column_group_option.
refresh_clause	Optional. The method for refreshing 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 rewrite based on the materialized view. For more information, see query_rewrite_clause.
on_query_computation_clause	Optional. Specifies whether the current materialized view is a real-time materialized view. For more information, see on_query_computation_clause.
view_select_stmt	The `SELECT` statement that defines data in the materialized view. You use this statement to retrieve data from a 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 about the syntax, see SIMPLE SELECT.

mv_column_group_option

WITH COLUMN GROUP(all columns): specifies to create a rowstore materialized view.
WITH COLUMN GROUP(each column): specifies to create a columnstore materialized view.
WITH COLUMN GROUP(all columns, each column): specifies to create a hybrid rowstore-columnstore materialized view.

Note

You can use the SHOW CREATE TABLE view_name; or SHOW CREATE VIEW view_name; statement to query the definition of a materialized view and determine the storage format of the materialized view.
In OceanBase Database in Oracle mode, you can also use the SELECT DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW','view_name') FROM DUAL; statement to query the definition of a materialized view.

refresh_clause

REFRESH [FAST | COMPLETE | FORCE] [mv_refresh_on_clause]: specifies the method for refreshing the materialized view.
- [COMPLETE | FAST | FORCE]: the refresh method of the materialized view. If you do not specify a refresh method, FORCE is used by default.
  - COMPLETE: specifies to perform a complete refresh. In this method, OceanBase Database recalculates the data of the entire materialized view to ensure data consistency with the base table.
  - FAST: specifies to perform an incremental refresh. In this method, OceanBase Database refreshes only the data related to changes in the base table.
    
    Notice
    
    This method determines the content for an incremental refresh based on the records in the materialized view log. Therefore, to support incremental refreshes of a materialized view, you need to create a materialized view log for the base table before you create the materialized view. For information about how to create a materialized view log, see Create a materialized view log.
  - FORCE: specifies to perform a hybrid refresh. In this method, OceanBase Database performs an incremental refresh first. If the incremental refresh fails, OceanBase Database performs a complete refresh. The hybrid refresh method is used by default.
NEVER REFRESH: specifies to disable refresh for the materialized view. If you specify the NEVER REFRESH clause, the materialized view is refreshed only when it is created, and will not be refreshed again.

mv_refresh_on_clause

Note

In addition to defining the refresh schedule by using the mv_refresh_on_clause clause, you can also call the dbms_mviews.refresh stored procedure to manually refresh a materialized view.

ON DEMAND: specifies to refresh the materialized view on demand. This option is optional.
[[START WITH expr] [NEXT expr]]: the refresh interval. This option is optional.
- [START WITH expr]: the time expression that defines when the refresh schedule begins. This option is optional.
- [NEXT expr]: the time expression that defines when the next refresh starts. This option is optional.
  Notice
  - If you use the NEXT clause, the time expressions of the refresh schedule must be set to future points in time. Otherwise, an error will occur.
  - If you want to periodically schedule a refresh task for a materialized view, you can use the NEXT clause to specify a scheduling interval.

We recommend that you use current_date to indicate the current time in the time zone. Here are some examples of the time expression:

The following clause specifies to refresh the materialized view once every 10 seconds, starting from the current time.
```
START WITH current_date NEXT current_date + INTERVAL '10' SECOND
```
The following clause specifies to refresh the materialized view once every 10 hours, starting from the current time.
```
START WITH current_date NEXT current_date + INTERVAL '10' HOUR
```
The following clause specifies to refresh the materialized view once every day, starting from the current time.
```
START WITH current_date NEXT current_date + 1
```

query_rewrite_clause

Notice

This feature is available only when the materialized view contains only the SELECT JOIN and WHERE clauses. In other words, materialized view-based automatic rewriting is supported only for select project join (SPJ) queries. If a matching materialized view does not meet the requirements, it will not be used for query rewriting, but the system does not return an error.
In the Oracle mode of OceanBase Database, you must specify the refresh method by using the refresh_clause clause for a materialized view to support automatic SQL query rewrite.

DISABLE QUERY REWRITE: specifies to disable automatic SQL query rewrite based on the materialized view. This is the default value.
ENABLE QUERY REWRITE: specifies to enable automatic SQL query rewrite based on the materialized view.

For more information on automatic SQL query rewrite based on materialized views, see Rewrite queries based on materialized views.

on_query_computation_clause

Notice

In the Oracle mode of OceanBase Database, you must specify the refresh method by using the refresh_clause clause for a real-time materialized view.

DISABLE ON QUERY COMPUTATION: specifies to create a normal materialized view. This is the default value.
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 the Create a materialized view topic.

Examples

Create a table named test_tbl1.

CREATE TABLE test_tbl1 (col1 NUMBER PRIMARY KEY, col2 VARCHAR2(20), col3 NUMBER, col4 NUMBER);

Create a materialized view named mv_test_tbl1. HASH-partition the materialized view by the col1 column into eight partitions. Specify to refresh the materialized view once every day by using the complete refresh method, starting from the current date. Query the test_tbl1 base table for data that meets the col3 >= 30 condition and record the query result in the materialized view.
```
CREATE MATERIALIZED VIEW mv_test_tbl1
  PARTITION BY HASH(col1) PARTITIONS 8
  REFRESH COMPLETE START WITH current_date NEXT current_date + 1
  AS SELECT col1, col2, col3
    FROM test_tbl1
    WHERE col3 >= 30;
```

Create a materialized view named mv2_test_tbl1 and set 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 columnstore 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;

Query the definition of the materialized view named mv3_test_tbl1.

SELECT DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW','MV3_TEST_TBL1') FROM DUAL;

CREATE MATERIALIZED VIEW

Purpose

Required privileges

Syntax

Parameters

Note

Note

Notice

mv_column_group_option

Note

refresh_clause

Notice

mv_refresh_on_clause

Note

Notice

query_rewrite_clause

Notice

on_query_computation_clause

Notice

Examples

References