This topic describes how to modify the attributes of a materialized view using SQL statements.
Privilege requirements
To modify a materialized view, the current user must have the ALTER privilege on the object. For more information about OceanBase AI Database privileges, see Permission classification.
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
| REFRESH PARALLEL integer
nested_refresh_option:
INDIVIDUAL
| INCONSISTENT
| CONSISTENT
Parameters
database.: Optional. Specifies the database where the materialized view is located. Ifdatabase.is omitted, the materialized view is in the database connected to the current session by default.view_name: Specifies the name of the materialized view to be modified.Note
OceanBase AI Database supports renaming a materialized view using the
RENAME TABLEstatement.alter_mview_action_list: Indicates the list of modification operations that can be performed on the materialized view. Multiple operations can be specified, separated by commas (,).
For detailed parameter descriptions on the syntax for modifying a materialized view, see ALTER MATERIALIZED VIEW.
Examples
Create a table named
test_tbl1.CREATE TABLE test_tbl1 (col1 INT PRIMARY KEY, col2 VARCHAR(20), col3 INT, col4 TEXT);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;Modify the query parallelism of the materialized view
mv_test_tbl1to 5.ALTER MATERIALIZED VIEW mv_test_tbl1 PARALLEL 5;Modify the refresh plan of the materialized view
mv_test_tbl1to set the initial refresh time to the current date and then refresh it every other day.ALTER MATERIALIZED VIEW mv_test_tbl1 REFRESH START WITH sysdate() NEXT sysdate() + interval 1 DAY;Create a nested materialized view named
mv_mv_test_tbl1based on the materialized viewmv_test_tbl1, with a refresh strategy ofINDIVIDUAL.CREATE MATERIALIZED VIEW mv_mv_test_tbl1 REFRESH COMPLETE INDIVIDUAL AS SELECT c1, c2 FROM mv_test_tbl1;Change the refresh strategy of the nested materialized view
mv_mv_test_tbl1toINCONSISTENT.ALTER MATERIALIZED VIEW mv_mv_test_tbl1 REFRESH INCONSISTENT;Change the refresh parallelism of the nested materialized view
mv_mv_test_tbl1to 3.ALTER MATERIALIZED VIEW mv_mv_test_tbl1 REFRESH PARALLEL 3;