CREATE MATERIALIZED VIEW

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

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.

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

• 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

• 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

• 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

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

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

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

5. Query the definition of the materialized view named mv3_test_tbl1.

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

References

• Overview

• Create a materialized view

• Query materialized views

• Drop a materialized view

• Refresh a materialized view