Purpose
This statement is used to create a materialized view log.
A materialized view log (mlog) records incremental update data of the base table to support the fast refresh feature of materialized views. The mlog is a tracking table that keeps track of changes in the base table and applies these changes to the corresponding materialized view, enabling fast refresh.
Note
The mlog feature of OceanBase AI Database does not currently support specifying a partition. The partition of the mlog table is bound to the partition of the base table.
Privilege requirements
To create a materialized view log, you must have the CREATE TABLE privilege and the SELECT privilege on the base table. For more information about OceanBase AI Database privileges, see Permission classification.
Syntax
CREATE [OR REPLACE] MATERIALIZED VIEW LOG ON [database.] table
[parallel_clause]
[with_clause]
[mv_log_purge_clause];
parallel_clause:
NOPARALLEL
| PARALLEL integer
with_clause:
WITH [ {PRIMARY KEY | ROWID | SEQUENCE}
[ { , PRIMARY KEY | , ROWID | , SEQUENCE }]... ]
(column_name [, column_name]...)
[new_values_clause]
new_values_clause:
{INCLUDING | EXCLUDING} NEW VALUES
mv_log_purge_clause:
PURGE {IMMEDIATE [ SYNCHRONOUS ]
| START WITH datetime_expr [NEXT datetime_expr]
| [START WITH datetime_expr] NEXT datetime_expr
}
Parameters
Parameters |
Description |
|---|---|
| database. | Optional. Specifies the database where the base table of the materialized view log is located. If omitted, database., the default base table is in your own database. |
| table | Specifies the name of the base table corresponding to the materialized view log. |
| parallel_clause | Optional. Specifies the DOP for creating the materialized view table, which determines the level of parallelism used to process the materialized view logs. For more information, see parallel_clause. |
| with_clause | Optional. Specifies the auxiliary columns to include in the materialized view log. It indicates whether to record the primary key when changes occur to the base table. PRIMARY KEY) and the row identifier (ROWID), and you can also use this clause to add sequences (SEQUENCE) to provide additional sorting information for materialized view logs. For more details, see with_clause. |
| column_name | Optional. Specifies the column name whose value is to be recorded in the materialized view log for all changed rows. |
| new_values_clause | Optional. Specifies whether to record both the old and new values in the materialized view log during an update operation. For more information, see new_values_clause. |
| mv_log_purge_clause | Optional. Specifies the time when data in the materialized view log is purged. For more information, see mv_log_purge_clause. |
parallel_clause
NOPARALLEL: The default configuration, with a parallelism of1.PARALLEL integer: Specifies the degree of parallelism. The value ofintegermust be greater than or equal to1.
with_clause
PRIMARY KEY: Indicates that the materialized view log records the primary key columns of the base table. This attribute takes effect only for tables with a primary key. If not specified, the system automatically adds this attribute to the materialized view log, meaning the log will contain the primary key columns by default.ROWID: Indicates that the materialized view log records the row identifier (ROWID) of the base table. If not specified, the system automatically adds this attribute to the materialized view log, meaning the log will contain theM_ROW$$column by default.SEQUENCE: Indicates that the materialized view log records the sequence numbers (seq_no) of multiple row updates within a transaction. The system automatically adds this attribute to materialized view logs, meaning they will contain theSEQUENCE$$column by default.
new_values_clause
INCLUDING: The default setting. It indicates that both the old and new values can be stored in the materialized view log. To enable fast refresh for a materialized view, you must specifyINCLUDING NEW VALUES.EXCLUDING: Indicates that recording new values in the materialized view log is disabled. Do not useEXCLUDING NEW VALUES, otherwise an error will be reported.
mv_log_purge_clause
Notice
The corresponding data in the materialized view log is cleared only after the materialized view is successfully refreshed.
IMMEDIATE: Indicates that the corresponding materialized view log is cleared immediately after each refresh of the materialized view. The default value isSYNCHRONOUS.SYNCHRONOUS: Optional. Specifies to perform the cleanup synchronously.
START WITH datetime_expr [NEXT datetime_expr]:START WITH datetime_expr: the earliest time for which the materialized view log can be cleared.[NEXT datetime_expr]: Optional. Specifies the time for the next purge of materialized view logs.
[START WITH datetime_expr] NEXT datetime_expr: If theSTART WITH datetime_exprparameter is not specified and only theNEXT datetime_exprparameter is specified, the first purge time for the materialized view log will be set to the value of theNEXT datetime_exprparameter.
Notice
Ensure that both time expressions START WITH datetime_expr and NEXT datetime_expr are set to future points in time. Otherwise, an error will occur.
We recommend that you use sysdate() to represent the current time in the current time zone. The following example shows a time expression:
START WITH sysdate() NEXT sysdate() + INTERVAL 1 DAY
The preceding clause specifies to clean up expired materialized view log records every day starting from the current time (sysdate()).
Examples
Create the table
test_tbl1.obclient> CREATE TABLE test_tbl1 (col1 INT PRIMARY KEY, col2 VARCHAR(20), col3 INT);Create a materialized view log on the
test_tbl1table. Specify the degree of parallelism for processing the materialized view log as5, and configure it to record changes in columncol2, including both the new values before and after the change. Set the materialized view log to clean up expired records every1day starting from the current date.obclient> CREATE MATERIALIZED VIEW LOG ON test_tbl1 PARALLEL 5 WITH SEQUENCE(col2) INCLUDING NEW VALUES PURGE START WITH sysdate() NEXT sysdate() + INTERVAL 1 DAY;View the materialized view log information on table
test_tbl1.obclient> DESC mlog$_test_tbl1;The return result is as follows:
+------------+-------------+------+------+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+------+---------+-------+ | col1 | int(11) | NO | PRI | NULL | | | col2 | varchar(20) | YES | | NULL | | | SEQUENCE$$ | bigint(20) | NO | PRI | NULL | | | DMLTYPE$$ | varchar(1) | YES | | NULL | | | OLD_NEW$$ | varchar(1) | YES | | NULL | | +------------+-------------+------+------+---------+-------+ 5 rows in set