CREATE MATERIALIZED VIEW LOG

Purpose

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

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

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 [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 setting, which sets the DOP to 1.

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

with_clause

• PRIMARY KEY: specifies that the materialized view log records the primary key columns of the base table. This takes effect only on tables that have a primary key. If you do not specify this attribute, the system automatically adds it, that is, the materialized view log contains the primary key columns by default.

• ROWID: specifies that the materialized view log records the row identifiers (ROWID) of the base table. If you do not specify this attribute, the system automatically adds it, that is, the materialized view log contains the M_ROW$$ column by default.

• SEQUENCE: specifies that the materialized view log records the sequence numbers (seq_no) of multiple rows updated within a transaction. If you do not specify this attribute, the system automatically adds it, that is, the materialized view log contains the SEQUENCE$$ column by default.

new_values_clause

• INCLUDING: the default setting, which specifies that both the old and new values are recorded in the materialized view log. To enable fast refresh of a materialized view, you must specify INCLUDING NEW VALUES.

• EXCLUDING: specifies that new values are not recorded in the materialized view log. Do not use EXCLUDING NEW VALUES, otherwise an error will be returned.

mv_log_purge_clause

• IMMEDIATE: specifies that the corresponding entries in the materialized view log are cleared immediately after each refresh of the materialized view. The default value is SYNCHRONOUS.

  • SYNCHRONOUS: Optional. Specifies that cleanup is performed synchronously.

• START WITH datetime_expr [NEXT datetime_expr]:

  • START WITH datetime_expr: specifies the time when the materialized view log is cleared for the first time.
  • [NEXT datetime_expr]: Optional. Specifies the time when the materialized view log is cleared next.

• [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 indicated 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 expressions:

• The following clause specifies to clear expired materialized view log records 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 expired materialized view log records 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 expired materialized view log records 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 (col1 NUMBER PRIMARY KEY, col2 VARCHAR2(20), col3 NUMBER);
   

2. Create a materialized view log on the test_tbl1 table. Set the DOP for processing the materialized view log to 5, and configure the log to record changes to column col2, including the old and new values before and after each change. Configure the log to clear expired records once a day, starting from the current date.

   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;
   

   +------------+--------------+------+------+---------+-------+
   | 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. Modify the materialized view log on the test_tbl1 table to record changes to both columns col2 and col3.

   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 logs