CREATE MATERIALIZED VIEW LOG

Purpose

You can use this statement to create a materialized view log.

A materialized view log (mlog) records the incremental data updates of a user table, that is the base table of the materialized view, to support quick refresh of the materialized view. An mlog is a record table that tracks changes to the base table and applies these changes to the corresponding materialized view to achieve quick refresh of the materialized view.

Required privileges

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

Syntax

CREATE 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

parallel_clause

• NOPARALLEL: sets the DOP to 1, which is the default value.

• PARALLEL integer: sets the DOP to an integer greater than or equal to 1.

with_clause

• PRIMARY KEY: specifies to record the primary key column of the base table in the materialized view log. This attribute takes effect only on a table with a primary key. If you do not specify this attribute, the system automatically adds this attribute to the materialized view log. In other words, the materialized view log contains the primary key column by default.

• ROWID: specifies to record the row ID of the base table in the materialized view log. If you do not specify this attribute, the system automatically adds this attribute to the materialized view log. In other words, the materialized view log contains the M_ROW$$ column by default.

• SEQUENCE: specifies to record the sequential numbers of multiple rows updated in a transaction in the materialized view log. If you do not specify this attribute, the system automatically adds this attribute to the materialized view log. In other words, the materialized view log contains the SEQUENCE$$ column by default.

new_values_clause

• INCLUDING: the default value, which specifies to record old and new values in the materialized view log. To perform the fast refresh of the materialized view, you must specify INCLUDING NEW VALUES.

• EXCLUDING: specifies not to record new values in the materialized view log. Do not use EXCLUDING NEW VALUES. Otherwise, an error will be reported.

mv_log_purge_clause

• IMMEDIATE: specifies to clear the corresponding materialized view log immediately after each refresh of the materialized view. The default value is SYNCHRONOUS.

  • SYNCHRONOUS: Optional. Specifies to perform cleanup synchronously.

• START WITH datetime_expr [NEXT datetime_expr]:

  • START WITH datetime_expr: the time to clear the materialized view log for the first time.
  • [NEXT datetime_expr]: Optional. The time to clear the materialized view log the next time.

• [START WITH datetime_expr] NEXT datetime_expr: If you specify only NEXT datetime_expr, the materialized view log is cleared for the first time at the time specified by NEXT datetime_expr.

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

• The following clause specifies to clear the expired materialized view logs once every 10 seconds, starting from the current time.

  START WITH current_date NEXT current_date + INTERVAL '10' SECOND
  

• The following clause specifies to clear the expired materialized view logs once every 10 hours, starting from the current time.

  START WITH current_date NEXT current_date + INTERVAL '10' HOUR
  

• The following clause specifies to clear the expired materialized view logs once every day, starting from the current time.

  START WITH current_date NEXT current_date + 1
  

Examples

1. Create a table named test_tbl1.

   CREATE TABLE test_tbl1 (id NUMBER PRIMARY KEY, name VARCHAR2(20), age NUMBER);
   

2. Create a materialized view log on the test_tbl1 table. Set the 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 ,age) INCLUDING NEW VALUES
     PURGE START WITH current_date NEXT current_date + 1;
   

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

   DESC mlog$_test_tbl1;
   

   The return result is as follows:

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

References

• Overview

• Create a materialized view log

• Drop a materialized view log