Modify the attributes of a materialized view

This topic describes how to execute an SQL statement to modify the attributes of a materialized view.

Privileges

To modify a materialized view, the current user must have the ALTER privilege on the target object. For more information about privileges in OceanBase Database, see Privilege types in Oracle 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. The schema where the materialized view resides. If you specify schema., you must specify the schema name. If you omit schema., the default schema in the current session is used.

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

  Note

  For OceanBase Database V4.3.5, the RENAME statement can be used to rename materialized views starting from V4.3.5 BP3.

• alter_mview_action_list: the list of actions that can be performed on the materialized view. Multiple actions can be specified at once, separated by commas (,).

For more information about the parameters, 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 degree of parallelism of the mv_test_tbl1 materialized view to 5.

   ALTER MATERIALIZED VIEW mv_test_tbl1 PARALLEL 5;
   

4. Set the refresh schedule of the mv_test_tbl1 materialized view so that the initial refresh time is the current date and the materialized view is refreshed every day thereafter.

   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, with the refresh strategy set to INDIVIDUAL.

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

6. Change 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
• Query rewriting
• Query materialized views
• Drop materialized views