Modify the attributes of a materialized view

This topic describes how to use SQL statements to modify the attributes of a materialized view.

Privilege requirements

To modify a materialized view, you must have the ALTER privilege on the object. For more information about the privileges of OceanBase Database, see Privilege types in Oracle-compatible mode.

Syntax

The syntax for modifying a materialized view is as follows:

ALTER MATERIALIZED VIEW [schema.]view_name alter_mview_action_list;

alter_mview_action_list:
    alter_mview_action [, alter_mview_action ...]

alter_mview_action:
    parallel_clause
    | REFRESH [nested_refresh_option] [[START WITH expr] [NEXT expr]]

parallel_clause:
    NOPARALLEL
    | PARALLEL integer

nested_refresh_option:
    INDIVIDUAL
    | INCONSISTENT
    | CONSISTENT

Parameters

• schema.: Optional. Specifies the schema where the materialized view is located. If you omit schema., the materialized view is located in the schema of the current session.

• view_name: the name of the materialized view to be modified.

  Note

  OceanBase Database allows you to rename a materialized view by using the RENAME TABLE statement.

• alter_mview_action_list: the list of operations that can be performed to modify the materialized view. You can specify multiple operations, separated by commas (,).

For more information about the parameters of the syntax for modifying a materialized view, see ALTER MATERIALIZED VIEW.

Examples

1. Create a table named test_tbl1.

   CREATE TABLE test_tbl1 (col1 NUMBER PRIMARY KEY, col2 VARCHAR2(20), col3 NUMBER, col4 BLOB);
   

2. Create a materialized view named mv_test_tbl1.

   CREATE MATERIALIZED VIEW mv_test_tbl1
       AS SELECT col1, col2, col3
          FROM test_tbl1;
   

3. Set the parallelism of the materialized view mv_test_tbl1 to 5.

   ALTER MATERIALIZED VIEW mv_test_tbl1 PARALLEL 5;
   

4. Set the initial refresh time of the materialized view mv_test_tbl1 to the current date and refresh it every 1 day.

   ALTER MATERIALIZED VIEW mv_test_tbl1
       REFRESH START WITH current_date NEXT current_date + 1;
   

5. Create a nested materialized view named mv_mv_test_tbl1 based on the materialized view mv_test_tbl1, and set the refresh strategy to INDIVIDUAL.

   CREATE MATERIALIZED VIEW mv_mv_test_tbl1
       REFRESH COMPLETE INDIVIDUAL
       AS SELECT col1, col2
          FROM mv_test_tbl1;
   

6. Set the refresh strategy of the nested materialized view mv_mv_test_tbl1 to INCONSISTENT.

   ALTER MATERIALIZED VIEW mv_mv_test_tbl1
       REFRESH INCONSISTENT;
   

References

• Overview of materialized views
• Materialized view logs
• Create materialized views
• Refresh materialized views
• Materialized view query rewrite
• Query materialized views
• Delete materialized views