Modify the attributes of a materialized view

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

Permissions

You must have the ALTER privilege on the object to be modified to modify a materialized view. For more information about the privileges of OceanBase Database, see Privilege types in MySQL mode.

Syntax

The SQL statement 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. Specifies the database where the materialized view is located. If you omit database., the materialized view is located in the database to which the current session is connected.

• view_name: specifies 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: specifies the list of operations that can be performed on the materialized view. You can specify multiple operations at a time, 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 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 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 the materialized view every 1 day.

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

   CREATE MATERIALIZED VIEW mv_mv_test_tbl1
       REFRESH COMPLETE INDIVIDUAL
       AS SELECT c1, c2
          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 a materialized view
• Refresh a materialized view
• Query rewrite based on materialized views
• Query a materialized view
• Delete a materialized view