CREATE MATERIALIZED VIEW LOG|V4.4.2| docs|Distributed Database

CREATE MATERIALIZED VIEW LOG

Last Updated：2026-03-06 07:02:42 Updated

Purpose

This statement is used to create a materialized view log.

A materialized view log (mlog) records incremental updates to the base table to support fast refreshes of materialized views. The mlog is a record table that tracks changes in the base table and applies these changes to the corresponding materialized view to enable fast refreshes.

Note

Currently, OceanBase Database does not support specifying partitions for mlog. The partitions of mlog are bound to the partitions 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 privileges in OceanBase Database, see Privilege types in Oracle-compatible mode.

Syntax

CREATE [OR REPLACE] MATERIALIZED VIEW LOG ON [schema.] 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

Parameter	Description
OR REPLACE	Optional. If you specify `OR REPLACE`, a new `mlog` will be created based on the definition and replace the existing `mlog` if it already exists. This process does not affect the normal read and write operations of the materialized view and base table.
schema.	Optional. Specifies the schema where the base table of the materialized view log is located. If you omit `schema.`, the base table will be created in your own schema by default.
table	Specifies the name of the base table corresponding to the materialized view log.
parallel_clause	Optional. Specifies the degree of parallelism (dop) for creating the materialized view log. For more information, see parallel_clause below.
with_clause	Optional. Specifies the auxiliary columns included in the materialized view log. It indicates whether to record the primary key (`PRIMARY KEY`) and row identifier (`ROWID`) when the base table changes. You can also use this clause to add a sequence (`SEQUENCE`) to provide additional sorting information for the materialized view log. For more information, see with_clause below.
column_name	Optional. Specifies the name of the column whose value will be recorded in the materialized view log for all modified rows.
new_values_clause	Optional. Specifies whether to record both the old and new values in the materialized view log. For more information, see new_values_clause below.
mv_log_purge_clause	Optional. Specifies the time when data in the materialized view log will be purged. For more information, see mv_log_purge_clause below.

parallel_clause

NOPARALLEL: The default configuration. The degree of parallelism is 1.
PARALLEL integer: Specifies the degree of parallelism. integer must be greater than or equal to 1.

with_clause

PRIMARY KEY: Indicates that the materialized view log records the primary key columns of the base table. This option only takes effect for tables with a primary key. If you do not specify this option, the system automatically adds this attribute to the materialized view log, meaning it will include the primary key columns by default.
ROWID: Indicates that the materialized view log records the row identifiers (ROWID) of the base table. If you do not specify this option, the system automatically adds this attribute to the materialized view log, meaning it will include the M_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 the materialized view log, meaning it will include the SEQUENCE$$ column by default.

new_values_clause

INCLUDING: The default setting. Indicates that both the old and new values can be recorded in the materialized view log. If you want the materialized view to support fast refreshes, you must specify INCLUDING NEW VALUES.
EXCLUDING: Indicates that new values will not be recorded in the materialized view log. Do not use EXCLUDING NEW VALUES, or an error will occur.

mv_log_purge_clause

Notice

Materialized view logs will only be purged after the corresponding materialized view has been successfully refreshed.

IMMEDIATE: Indicates that the materialized view log will be purged immediately after each refresh. The default value is SYNCHRONOUS.
- SYNCHRONOUS: Optional. Indicates that the purge will be executed synchronously.
START WITH datetime_expr [NEXT datetime_expr]:
- START WITH datetime_expr: Specifies the initial purge time for the materialized view log.
- [NEXT datetime_expr]: Optional. Specifies the next purge time for the materialized view log. Used to set the next purge time.
[START WITH datetime_expr] NEXT datetime_expr: If you specify only the NEXT datetime_expr parameter without the START WITH datetime_expr parameter, the initial purge time for the materialized view log will be set to the value of the NEXT datetime_expr parameter.

Notice

Make sure that the time expressions START WITH datetime_expr and NEXT datetime_expr are set to future dates and times. Otherwise, an error will occur.

We recommend that you use current_date to represent the current time in the current time zone. Here are some examples of time expressions:

Purge expired materialized view log records every 10 seconds starting from the current time (current_date).
```
START WITH current_date NEXT current_date + INTERVAL '10' SECOND
```
Purge expired materialized view log records every 10 hours starting from the current time (current_date).
```
START WITH current_date NEXT current_date + INTERVAL '10' HOUR
```
Purge expired materialized view log records every day starting from the current time (current_date).
```
START WITH current_date NEXT current_date + 1
```

Examples

Create a table named test_tbl1.

obclient> CREATE TABLE test_tbl1 (col1 NUMBER PRIMARY KEY, col2 VARCHAR2(20), col3 NUMBER);

Create a materialized view log on the test_tbl1 table. Specify the parallelism for processing the materialized view log as 5, and record changes to the col2 column, including both the old and new values. Configure the materialized view log to start from the current date and to clean up expired records every 1 day.
```
obclient> CREATE MATERIALIZED VIEW LOG ON test_tbl1
    PARALLEL 5
    WITH SEQUENCE(col2) INCLUDING NEW VALUES
    PURGE START WITH current_date NEXT current_date + 1;
```

View the information about the materialized view log on the test_tbl1 table.

obclient> DESC mlog$_test_tbl1;

The returned result is as follows:

+------------+--------------+------+------+---------+-------+
| FIELD      | TYPE         | NULL | KEY  | DEFAULT | EXTRA |
+------------+--------------+------+------+---------+-------+
| COL1       | NUMBER       | NO   | PRI  | NULL    | NULL  |
| COL2       | VARCHAR2(20) | YES  | NULL | NULL    | NULL  |
| SEQUENCE$$ | BIGINT(20)   | NO   | PRI  | NULL    | NULL  |
| DMLTYPE$$  | VARCHAR2(1 ) | YES  | NULL | NULL    | NULL  |
| OLD_NEW$$  | VARCHAR2(1 ) | YES  | NULL | NULL    | NULL  |
+------------+--------------+------+------+---------+-------+
5 rows in set

Change the materialized view log on the test_tbl1 table to record changes to the col2 and col3 columns.

obclient> CREATE OR REPLACE MATERIALIZED VIEW LOG ON test_tbl1
    PARALLEL 5
    WITH SEQUENCE(col2, col3) INCLUDING NEW VALUES
    PURGE START WITH current_date NEXT current_date + 1;

View the information about the materialized view log on the test_tbl1 table.

obclient> DESC mlog$_test_tbl1;

The returned result is as follows:

+------------+--------------+------+------+---------+-------+
| FIELD      | TYPE         | NULL | KEY  | DEFAULT | EXTRA |
+------------+--------------+------+------+---------+-------+
| COL1       | NUMBER       | NO   | PRI  | NULL    | NULL  |
| COL2       | VARCHAR2(20) | YES  | NULL | NULL    | NULL  |
| COL3       | NUMBER       | YES  | NULL | NULL    | NULL  |
| SEQUENCE$$ | BIGINT(20)   | NO   | PRI  | NULL    | NULL  |
| DMLTYPE$$  | VARCHAR2(1 ) | YES  | NULL | NULL    | NULL  |
| OLD_NEW$$  | VARCHAR2(1 ) | YES  | NULL | NULL    | NULL  |
+------------+--------------+------+------+---------+-------+
6 rows in set

References

Materialized view logs