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

Syntax

The SQL syntax for modifying a materialized view is as follows:

ALTER MATERIALIZED VIEW [database.]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

• database.: optional. The database where the materialized view to be modified resides. If you omit database., the default database of 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 TABLE 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 INT PRIMARY KEY, col2 VARCHAR(20), col3 INT, col4 TEXT);
   

2. Create a materialized view named mv_test_tbl1.

   CREATE MATERIALIZED VIEW mv_test_tbl1(c1, c2, c3, PRIMARY KEY(c1))
       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. Modify the refresh schedule of the materialized view mv_test_tbl1 to set the initial refresh time to the current date, and then refresh the materialized view every day thereafter.

   ALTER MATERIALIZED VIEW mv_test_tbl1
       REFRESH START WITH sysdate() NEXT sysdate() + interval 1 DAY;
   

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

   CREATE MATERIALIZED VIEW mv_mv_test_tbl1
       REFRESH COMPLETE INDIVIDUAL
       AS SELECT c1, c2
          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 with materialized views
• Query materialized views
• Drop materialized views