CREATE MATERIALIZED VIEW

Description

This statement is used to create a materialized view.

A materialized view is a special type of database object that stores a copy of query results and is refreshed periodically (or manually) to keep the data up to date. Materialized views can contain aggregate functions, joins, and subqueries and can be indexed and partitioned to improve performance.

Privilege requirements

To create a materialized view, you must 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] [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

Parameter description

refresh_clause

• REFRESH [FAST | COMPLETE | FORCE] [mv_refresh_on_clause]: specifies the refresh method for the materialized view.

  • [COMPLETE | FAST | FORCE]: specifies the refresh method for the materialized view. The options are optional. If you do not specify any refresh method, the default value FORCE takes effect.

    • COMPLETE: specifies to completely refresh the materialized view, namely, to recalculate all the data of the materialized view to ensure consistency with the source table.

    • FAST: specifies to incrementally refresh the materialized view, namely, to refresh only the data related to changes in the source table, without recalculating the entire materialized view.

      Notice

      • The REFRESH FAST method uses records in mlogs to determine the data to be incrementally refreshed. Therefore, to use the incremental refresh method to refresh a materialized view, you must create mlogs for the base table before you create the materialized view.
      • All columns used for refreshing the materialized view must exist in the mlogs.

      For more information about how to create mlogs, see Create an mlog.

    • FORCE: the default value, specifies to hybrid refresh the materialized view. It first attempts to incrementally refresh the materialized view. If the incremental refresh fails, it completely refreshes the materialized view.

• NEVER REFRESH: specifies not to refresh the materialized view. In other words, the materialized view is only refreshed when it is created and cannot be refreshed after it is created.

mv_refresh_on_clause

• ON DEMAND: the option to refresh the materialized view when needed. This option is optional.

• [[START WITH expr] [NEXT expr]]: the option to define the refresh interval. This option is optional.

  • [START WITH expr]: the option to specify the start time of the refresh plan in the form of an expression. This option is optional.

  • [NEXT expr]: the option to specify the time of the next refresh in the form of an expression. This option is optional.

    Notice

    • If the NEXT clause is used, the start time expression must specify a time point later than the current time. Otherwise, an error will occur.
    • If you want the system to automatically schedule refresh tasks for the materialized view on a periodic basis, use the NEXT clause to specify the scheduling period.

We recommend that you use current_date to indicate the current time zone and provide time expressions as follows:

• Start refreshing the materialized view every 10 seconds from the current time (current_date).

  START WITH current_date NEXT current_date + INTERVAL '10' SECOND
  

• Start refreshing the materialized view every 10 hours from the current time (current_date).

  START WITH current_date NEXT current_date + INTERVAL '10' HOUR
  

• Start refreshing the materialized view every day from the current time (current_date).

  START WITH current_date NEXT current_date + 1
  

query_rewrite_clause

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

For more information about query rewriting, see Materialized view-based query rewriting.

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 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 view by using the col1 column as the hash key with 8 partitions. Use the complete refresh method to refresh the view, starting from the current date and refreshing it every 1 day. Query records whose col3 value is greater than or equal to 30 from the test_tbl1 table as the base table and use the query result as the data of 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;
   

References

• Overview

• Create a materialized view

• Query materialized views

• Drop a materialized view

• Refresh a materialized view