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

CREATE MATERIALIZED VIEW

Last Updated：2024-06-28 05:30:30 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 need the CREATE TABLE privilege to create a materialized view. For more information about privileges in 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] [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

Parameters

Note

You cannot directly create an index by using the statement that creates 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 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.
refresh_clause	Optional. The method for refreshing the materialized view. For more information, see refresh_clause.
mv_refresh_on_clause	Optional. The refresh mode of the materialized view. For more information, see mv_refresh_on_clause.
query_rewrite_clause	Optional. Specifies whether to enable automatic query rewrite for 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 SELECT.

refresh_clause

REFRESH [FAST | COMPLETE | FORCE] [mv_refresh_on_clause]: specifies how the materialized view is refreshed.
- [COMPLETE | FAST | FORCE]: the refresh method, which is optional. If you do not specify the refresh method, FORCE is used by default.
  - COMPLETE: specifies to perform the full 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 the incremental refresh. In this method, OceanBase Database refreshes only the data related to changes in the base table.
    
    Notice
    
    To perform an incremental refresh of a materialized view, you need to create a materialized view log on 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 the hybrid refresh. In this method, OceanBase Database performs an incremental refresh first. If the incremental refresh fails, OceanBase Database performs a full refresh. The hybrid refresh method is used by default.
NEVER REFRESH: specifies to disable the materialized view refresh. 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: Optional. Specifies to refresh the materialized view on demand.
[[START WITH expr] [NEXT expr]]: Optional. The refresh interval.
- [START WITH expr]: Optional. The time expression that defines when the refresh schedule begins.
- [NEXT expr]: Optional. The time expression that defines when the next refresh starts.
  
  Notice
  
  If you use the NEXT clause, the time expressions of the refreshing schedule must be set to future points in time. Otherwise, an error will occur.

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

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

The preceding clause specifies to refresh the materialized view once every day, starting from the current time.

query_rewrite_clause

Notice

This feature is available when the materialized view contains only the SELECT JOIN and WHERE clauses, that is, only select project join (SPJ) queries are involved. No error will be reported if you specify the query_rewrite_clause option for an unqualified materialized view, but the rewrite will not be performed.

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

For more information about query rewrite for materialized views, see Query rewrite for materialized views.

on_query_computation_clause

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 Create a real-time materialized view 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 eight partitions. Specify to refresh the materialized view once every day by using the full 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 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 set the primary key.

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

CREATE MATERIALIZED VIEW

Purpose

Required privileges

Syntax

Parameters

Note

Note

Notice

refresh_clause

Notice

mv_refresh_on_clause

Note

Notice

query_rewrite_clause

Notice

on_query_computation_clause

Examples

References