Modify the attributes of a materialized view

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

Permissions

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

Syntax

The SQL statement 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. The schema where the materialized view is located. If you omit this parameter, the materialized view is located in the schema of the current session.

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

  Note

  You can use the RENAME TABLE statement to rename a materialized view in OceanBase Database.

• alter_mview_action_list: the list of operations that can be performed to modify the materialized view. You can specify multiple operations in this parameter, 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 the refresh interval to 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. The refresh strategy is 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
• Materialized view logs
• Create a materialized view
• Refresh a materialized view
• Materialized view query rewriting
• Query a materialized view
• Delete a materialized view