Description
This statement is used to modify the attributes of a materialized view.
Privilege requirements
To execute the ALTER MATERIALIZED VIEW statement, the current user must have the ALTER privilege on the object to be operated on. For more information about OceanBase AI Database privileges, see Permission classification.
Syntax
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
Parameters |
Description |
|---|---|
| database. | Optional. Specifies the database where the materialized view is located. If omitted, the current database is used by default. database., the default materialized view is in the database connected by the current session. |
| view_name | Specifies the name of the materialized view to be renamed.
NoteOceanBase AI Database supports renaming a materialized view using the |
| alter_mview_action_list | Represents the list of operations that can be performed on a materialized view. Multiple operations can be specified, separated by commas (,). For more information, see alter_mview_action. |
alter_mview_action
parallel_clause: specifies the degree of parallelism for the materialized view. Valid values:NOPARALLEL: The default configuration, with a parallelism of 1.PARALLEL integer: Specifies the table-level parallelism, which is the degree of parallelism when querying materialized views. The value ofintegermust be an integer greater than or equal to 1.REFRESH PARALLEL integer: Specifies the refresh parallelism, which is the degree of parallelism used when a materialized view is refreshed. The value ofintegermust be an integer greater than or equal to 0. Whenintegeris 0, the system variablemview_refresh_dopis used to determine the refresh parallelism.
Example:
- Set the degree of parallelism for querying materialized views.
ALTER MATERIALIZED VIEW mv1 PARALLEL 5; - Set the refresh parallelism for the materialized view.
ALTER MATERIALIZED VIEW mv1 REFRESH PARALLEL 3;
REFRESH [nested_refresh_option] [[START WITH expr] [NEXT expr]]: modifies the refresh strategy for a nested materialized view or the time cycle for the background refresh task of a materialized view.nested_refresh_option: Optional. Specifies the refresh strategy for nested materialized views.INDIVIDUAL: The default value, which indicates an individual refresh.INCONSISTENT: Indicates a cascading non-consistent flush.CONSISTENT: indicates a cascading consistent refresh.
Example:
obclient> ALTER MATERIALIZED VIEW mv1 REFRESH INDIVIDUAL;obclient> ALTER MATERIALIZED VIEW mv1 REFRESH INCONSISTENT;obclient> ALTER MATERIALIZED VIEW mv1 REFRESH CONSISTENT;[[START WITH expr] [NEXT expr]]: Specifies the time interval for refreshing the materialized view in the background.[START WITH expr]: Optional. The time expression at which the refresh plan starts.[NEXT expr]: Optional. The expression that specifies the time for the next refresh.
Notice
- If the
NEXTclause is used, the time expression for refreshing the plan must be set to a future point in time; otherwise, an error will occur. - To periodically and automatically schedule refresh tasks for a materialized view, define
NEXTto set the scheduling cycle.
We recommend that you use
sysdate()to represent the current time in the current time zone. Example time expressions:START WITH sysdate() NEXT sysdate() + INTERVAL 1 DAYThe preceding clause specifies to refresh the materialized view every day starting from the current time (
sysdate()).Example:
obclient> ALTER MATERIALIZED VIEW mv1 REFRESH START WITH sysdate();Set only the refresh interval:
obclient> ALTER MATERIALIZED VIEW mv1 REFRESH NEXT sysdate() + INTERVAL 1 DAY;Set both the start time and refresh interval:
obclient> ALTER MATERIALIZED VIEW mv1 REFRESH START WITH sysdate() NEXT sysdate() + INTERVAL 1 DAY;