Create a materialized view log

Overview

A materialized view log records the incremental data updates of a user table that serves as the base table of a materialized view, to support quick refreshes of the materialized view. A materialized view log is a record table that tracks changes to a base table and applies these changes to the corresponding materialized view to achieve quick refreshes of the materialized view.

Prerequisites

You 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 MySQL mode.

Syntax

The syntax for creating a materialized view log is as follows:

CREATE MATERIALIZED VIEW LOG ON [database.] table_name [parallel_clause] [with_clause] [mv_log_purge_clause];

For more information about the parameters in the syntax, see CREATE MATERIALIZED VIEW LOG.

Example

1. Create a table named test_tbl1.

   CREATE TABLE test_tbl1 (id INT, name VARCHAR(20), age INT, PRIMARY KEY(id, age)) PARTITION BY HASH(age) PARTITIONS 10;
   

2. Create a materialized view log in the test_tbl1 table. Set the degree of parallelism (DOP) of the materialized view log to 5. Specify to record data changes to the name and age columns in the base table to the materialized view, including the old and new values before and after the change. Specify to clear expired log data once a day, starting from the current day.

   CREATE MATERIALIZED VIEW LOG ON test_tbl1 
     PARALLEL 5 
     WITH SEQUENCE(name) INCLUDING NEW VALUES
     PURGE START WITH sysdate() NEXT sysdate() + interval 1 day;
   

3. Query the information of the materialized view log in the test_tbl1 table.

   DESC mlog$_test_tbl1;
   

   The return result is as follows:

   +------------+-------------+------+------+---------+-------+
   | Field      | Type         | Null | Key | Default | Extra |
   +------------+-------------+------+------+---------+-------+
   | id         | int(11)     | NO   | PRI  | NULL    |       |
   | name       | varchar(20) | YES  |      | NULL    |       |
   | age        | int(11)     | NO   | PRI  | NULL    |       |
   | SEQUENCE$$ | bigint      | NO   | PRI  | NULL    |       |
   | DMLTYPE$$  | varchar(1)  | YES  |      | NULL    |       |
   | OLD_NEW$$  | varchar(1)  | YES  |      | NULL    |       |
   +------------+-------------+------+------+---------+-------+
   6 rows in set
   

References

• Overview

• Drop a materialized view log

• DBA_MVIEW_LOGS