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 (manually or automatically) 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 Oracle-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. To create an index for a materialized view, you must use the CREATE INDEX statement separately.

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 explicit names for 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 by the `SELECT` statement. The number of column names specified in the `column_list` clause must be the same as the number of columns retrieved by the `SELECT` statement.
column_name	The name of the materialized view column. By default, the column names retrieved by 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 by the `SELECT` statement. If you specify column names for 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 mode 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. Whether to enable automatic query rewriting for the materialized view. For more information, see query_rewrite_clause.
on_query_computation_clause	Optional. 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 the `view_select_stmt` clause is the same as that of a regular `SELECT` statement. For more information, see SIMPLE SELECT. 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 of the materialized view, an error is returned. The `AS OF PROCTIME()` clause specifies to skip the refresh of this table during incremental refreshes. In addition, the `AS OF PROCTIME()` table does not need to have an mlog. OceanBase Database allows you to declare a regular view as a dimension table (`AS OF PROCTIME()`) and use it as the base table for an incremental-refresh materialized view.

mv_column_group_option

WITH COLUMN GROUP(all columns): specifies that the materialized view is stored in row format.
WITH COLUMN GROUP(each column): specifies that the materialized view is stored in column format.
WITH COLUMN GROUP(all columns, each column): specifies that the materialized view is stored in row-column 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.
In Oracle-compatible mode of OceanBase Database, you can also run the SELECT DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW','view_name') FROM DUAL; statement to view the definition of the materialized view.

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 parameter is optional. If you do not specify a 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 that has changed in the source table to avoid a complete recalculation of the entire view.
    Notice
    - The REFRESH FAST method uses the records in the materialized view log to determine the data to be incrementally refreshed. Therefore, when you perform an incremental refresh on a materialized view, you must create a materialized view log (mlog) for the base table before you create the materialized view.
    - The columns used for incremental refresh in the materialized view must be included in the mlog.
    For more information about how to create a materialized view log, see CREATE MATERIALIZED VIEWS LOG.
  - 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 for nested materialized views. This parameter is optional. 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 inconsistent refresh.
    - CONSISTENT: specifies a cascading consistent refresh.
    Note
    
    For non-nested materialized views, no cascading refresh is performed regardless of the refresh strategy specified. The specified refresh strategies only take effect in background tasks. When you manually use the PL package (DBMS_MVIEW.REFRESH) to schedule a refresh, the refresh is performed according to the specified PL parameters.
NEVER REFRESH: specifies that the materialized view does not need to be refreshed. In other words, the materialized view is only refreshed 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 a refresh plan, you can also manually refresh a materialized view by calling the dbms_mviews.refresh stored procedure.

ON DEMAND: optional. Specifies that the materialized view is refreshed on demand.
[[START WITH expr] [NEXT expr]]: optional. Specifies the refresh interval.
- [START WITH expr]: optional. Specifies the start time of the refresh plan.
- [NEXT expr]: optional. 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 returned.
  - If you want to periodically schedule automatic refresh tasks for the materialized view, define the NEXT clause to set the scheduling cycle.

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

Refresh the materialized view every 10 seconds starting from the current time (current_date).
```
START WITH current_date NEXT current_date + INTERVAL '10' SECOND
```
Refresh the materialized view every 10 hours starting from the current time (current_date).
```
START WITH current_date NEXT current_date + INTERVAL '10' HOUR
```
Refresh the materialized view every day starting from the current time (current_date).
```
START WITH current_date NEXT current_date + 1
```

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 reported as errors but will not be used for rewriting.
In Oracle-compatible mode of OceanBase Database, you must specify the refresh method (refresh_clause) when you create a materialized view that supports rewriting.

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

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

on_query_computation_clause

Notice

In OceanBase Database in Oracle-compatible mode, you must specify the refresh_clause when you create a materialized view.

DISABLE ON QUERY COMPUTATION: the default value. This option specifies to create a regular materialized view.
ENABLE ON QUERY COMPUTATION: specifies to create a materialized view that is computed on query.
Notice
- Materialized views that use the MIN or MAX function do not support the ENABLE ON QUERY COMPUTATION option.
- Aggregated materialized views that use outer joins do not support the ENABLE ON QUERY COMPUTATION option.
- Materialized views that use set queries do not support the ENABLE ON QUERY COMPUTATION option.
- Nested materialized views do not support the ENABLE ON QUERY COMPUTATION option.

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

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 8 partitions. Use a complete refresh method to refresh the materialized view, starting from the current date, and refresh it every 1 day. Query the records in the test_tbl1 table that meet 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 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 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-formatted 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.

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

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

Notice

Examples

References