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 (manually or automatically) to keep the data up to date. Materialized views can include operations such as aggregations, joins, and subqueries, and they can be indexed and partitioned to further improve performance.

Privilege requirements

To create a materialized view, you must have the CREATE TABLE privilege. For more information about the privileges of OceanBase Database, see Privilege classification 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 [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 the materialized view.

  • refresh_option: specifies the refresh method of the materialized view. Optional. If not specified, the default value is FORCE.

    • 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

      • The REFRESH FAST method uses the records in the materialized view log to determine the content 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.
      • All columns used in the incremental refresh of the materialized view must be present in the mlog.

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

    • FORCE: default value. Specifies to perform a hybrid refresh. First, it attempts an incremental refresh. If the incremental refresh fails, it performs a complete refresh.

    • nested_refresh_option: specifies the refresh strategy of the nested materialized view. Optional. If not specified, the default value is INDIVIDUAL. Valid values:

      • INDIVIDUAL: default value. Specifies to perform an independent refresh.
      • INCONSISTENT: specifies to perform an inconsistent cascade refresh.
      • CONSISTENT: specifies to perform a consistent cascade refresh.

      Note

      For non-nested materialized views, cascade refresh is not supported. Therefore, regardless of the specified refresh strategy, the default independent refresh is performed. 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 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 at creation and cannot be refreshed again after creation.

mv_refresh_on_clause

• ON DEMAND: optional. Specifies to refresh the materialized view on demand.

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

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

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

    Notice

    • If the NEXT clause is used, 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 automatically schedule refresh tasks, 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

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

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

  Notice

  • Materialized views created with the MIN or MAX function 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 NUMBER PRIMARY KEY, col2 VARCHAR2(20), col3 NUMBER, col4 NUMBER);
   

2. Create a materialized view named mv_test_tbl1. Partition the materialized view by the col1 column into 8 partitions. Use the 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 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 current_date NEXT current_date + 1
     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 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.

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

References

• Overview of materialized views

• Create materialized views

• Refresh materialized views

• Query materialized views

• Delete materialized views