CREATE MATERIALIZED VIEW LOG

Purpose

This statement is used to create a materialized view log.

A materialized view log (mlog) records incremental updates to the base table (a regular table or a materialized view) to support fast refreshes of materialized views. An mlog is a record table that tracks changes to the base table and applies these changes to the corresponding materialized view, enabling fast refreshes.

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

parallel_clause

• NOPARALLEL: The default value. The degree of parallelism is 1.

• PARALLEL integer: Specifies the degree of parallelism. The value of 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 is effective only 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 that the materialized view log will default to including the primary key columns.

• 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 that the materialized view log will default to including the M_ROW$$ column.

• SEQUENCE: Indicates that the materialized view log records the sequence numbers (seq_no) of multiple rows updated in a transaction. The system automatically adds this attribute to the materialized view log, meaning that the materialized view log will default to including the SEQUENCE$$ column.

new_values_clause

• INCLUDING: The default value. Indicates that both the old and new values can be recorded in the materialized view log. If you want to support fast refreshes for the materialized view, you must specify INCLUDING NEW VALUES.

• EXCLUDING: Indicates that new values are not recorded in the materialized view log. Do not use EXCLUDING NEW VALUES, or an error will occur.

mv_log_purge_clause

• IMMEDIATE: Indicates that the materialized view log is purged immediately after each refresh. The default value is SYNCHRONOUS.

  • SYNCHRONOUS: Optional. Indicates that the purge is performed synchronously.

• START WITH datetime_expr [NEXT datetime_expr]:

  • START WITH datetime_expr: Indicates the initial purge time for the materialized view log.
  • [NEXT datetime_expr]: Optional. Indicates the next purge time for the materialized view log. It is used to set the next purge time.

• [START WITH datetime_expr] NEXT datetime_expr: If you specify only the NEXT datetime_expr parameter and omit the START WITH datetime_expr parameter, the initial purge time of the materialized view log is set to the value of the NEXT datetime_expr parameter.

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 1 day starting from the current time (current_date).

  START WITH current_date NEXT current_date + 1
  

Examples

1. Create a table named test_tbl1.

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

2. Create a materialized view log on the test_tbl1 table. Set the parallelism for processing the materialized view log to 5, and specify that the log records 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.

   CREATE MATERIALIZED VIEW LOG ON test_tbl1
       PARALLEL 5
       WITH SEQUENCE(col2) INCLUDING NEW VALUES
       PURGE START WITH current_date NEXT current_date + 1;
   

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

   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
   

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

   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;
   

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

   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 log