Modify the attributes of a materialized view

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

Privilege requirements

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

Syntax

The syntax of 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: the name of the materialized view to be modified.

  Note

  You can also rename a materialized view by using the RENAME TABLE statement.

• alter_mview_action_list: the list of operations to be performed on 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 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. The following example shows how to do this:

   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 set the refresh interval to 1 day. The following example shows how to do this:

   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. The refresh strategy is INDIVIDUAL. The following example shows how to do this:

   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. The following example shows how to do this:

   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 for materialized views
• Query a materialized view
• Delete a materialized view