CREATE MATERIALIZED VIEW

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

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.

refresh_clause

• REFRESH [refresh_option [nested_refresh_option]] [mv_refresh_on_clause]: Specifies the refresh method of a materialized view.

  • refresh_option: Specifies the refresh method of a materialized view. This is an optional parameter. If you do not specify a refresh method, the default value is FORCE.

    • COMPLETE: Specifies to perform a full refresh. A full refresh recalculate all data of the materialized view to ensure that the data in the materialized view is consistent with that in the source table.

    • FAST: Specifies to perform an incremental refresh. An incremental refresh only recalculate data that has changed in the source table to avoid recalculating all data of the materialized 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.
      • Columns used in an incremental refresh of a materialized view must be included in the mlog.

      For more information about how to create a materialized view log, see Materialized view log.

    • FORCE: The default value. Specifies to perform a hybrid refresh. The system first attempts an incremental refresh. If the incremental refresh fails, it performs a full refresh.

    • nested_refresh_option: Specifies the refresh strategy of a 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 to perform an independent refresh.
      • INCONSISTENT: Specifies to perform a cascading refresh without consistency.
      • CONSISTENT: Specifies to perform a cascading refresh with consistency.

      Note

      For a non-nested materialized view, cascading refresh does not exist. Therefore, no matter which refresh strategy you specify, the system performs an independent refresh. The specified three refresh strategies only take effect in background tasks. When you manually use the PL package (DBMS_MVIEW.REFRESH) to schedule a refresh, the system performs the refresh based on 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

• ON DEMAND: An optional parameter. Specifies to perform an on-demand refresh.

• [[START WITH expr] [NEXT expr]]: An optional parameter. Specifies the refresh interval.

  • [START WITH expr]: An optional parameter. Specifies the start time expression of the refresh plan.

  • [NEXT expr]: An optional parameter. Specifies the next refresh time expression.

    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 and automatically schedule refresh tasks for a materialized view, 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 specifies that the materialized view is refreshed every 1 day starting from the current time (sysdate()).

query_rewrite_clause

• 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 standard materialized view.

• ENABLE ON QUERY COMPUTATION: Specifies to create a real-time materialized view.

  Notice

  • Materialized views that use the MIN or MAX functions 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

1. Create a table named test_tbl1.

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

2. 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 refresh it 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;
   

3. 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;
   

4. 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;
   

5. View the definition of the materialized view mv3_test_tbl1.

   SHOW CREATE VIEW mv3_test_tbl1;
   

References

• Overview of materialized views

• Create materialized views

• Query materialized views

• Delete materialized views

• Refresh materialized views