Purpose
This statement is used to modify the attributes of a materialized view log (MViewLog).
Privilege requirements
To execute the ALTER MATERIALIZED VIEW LOG statement, the current user must have the ALTER privilege on the base table. For more information about OceanBase AI Database privileges, see Permission classification.
Syntax
ALTER MATERIALIZED VIEW LOG ON [database.]table_name alter_mlog_action_list;
alter_mview_action_list:
alter_mlog_action [, alter_mlog_action ...]
alter_mlog_action:
parallel_clause
| PURGE [[START WITH expr] [NEXT expr]]
| LOB_INROW_THRESHOLD [=] integer
parallel_clause:
NOPARALLEL
| PARALLEL integer
Parameters
Parameters |
Description |
|---|---|
| database. | Optional. Specifies the database where the materialized view is located. If omitted, the current database is used. database., the default base table is in the database connected by the current session. |
| table_name | Specifies the name of the base table corresponding to the materialized view log. |
| alter_mlog_action_list | Specifies the list of operations that can be performed on the materialized view log. Multiple operations can be specified, separated by commas (,). For more information, see alter_mlog_action. |
alter_mlog_action
parallel_clause: specifies the degree of parallelism for modifying the materialized view log table. Valid values:NOPARALLEL: The degree of parallelism is 1, which is the default configuration.PARALLEL integer: specifies the degree of parallelism. The value ofintegermust be an integer greater than or equal to 1.
Example:
-- First, create the base table. obclient> CREATE TABLE mvlog ( id INT PRIMARY KEY, name VARCHAR(50) ); -- Create Materialized View Log obclient> CREATE MATERIALIZED VIEW LOG ON mvlog; -- Set the degree of parallelism to 5. obclient> ALTER MATERIALIZED VIEW LOG ON mvlog PARALLEL 5; -- Set to No Parallelism (default) obclient> ALTER MATERIALIZED VIEW LOG ON mvlog NOPARALLEL;PURGE [[START WITH expr] [NEXT expr]]: modifies the time cycle for the background cleanup task of materialized view logs.[START WITH expr]: Optional. Specifies the earliest time for which the materialized view log can be cleared.[NEXT expr]: Optional. Specifies the time for the next cleanup of materialized view logs.
Notice
Ensure that both the
START WITH exprandNEXT exprtime expressions refer to future points in time. Otherwise, an error will occur.It is recommended to use
sysdate()to represent the current time in the local timezone. An example time expression is as follows:START WITH sysdate() NEXT sysdate() + INTERVAL 1 DAYThe preceding clause specifies to clean up expired materialized view log records every other day starting from the current time (
sysdate()).Example:
obclient> ALTER MATERIALIZED VIEW LOG ON mvlog PURGE START WITH sysdate();obclient> ALTER MATERIALIZED VIEW LOG ON mvlog PURGE NEXT sysdate() + INTERVAL 1 DAY;obclient> ALTER MATERIALIZED VIEW LOG ON mvlog PURGE START WITH sysdate() NEXT sysdate() + INTERVAL 1 DAY;LOB_INROW_THRESHOLD [=] integer: Specifies the threshold for in-row storage of LOB data in materialized view logs. You can only increase this value.Note
The default value of
LOB_INROW_THRESHOLDis 8 KB. The default value ofLOB_INROW_THRESHOLDfor an mlog is not controlled by the system variable ob_default_lob_inrow_threshold.Example:
obclient> ALTER MATERIALIZED VIEW LOG ON mvlog LOB_INROW_THRESHOLD 10000;