Materialized views|V4.3.3| docs|Distributed Database

Materialized views

Last Updated：2024-11-15 04:52:21 Updated

Materialized views store query results of views. You can use materialized views to speed up view queries and reduce the CPU, network, and disk resources consumed by queries. Although materialized views occupy storage space, they can shorten the execution duration of queries.

Scenarios

Data summary: Sales data and user behavior statistics are summarized on a daily, weekly, or monthly basis.
Generation of statistics reports: The report system needs to generate fixed-format data reports on a regular basis.
Optimization of complex queries: You can materialize the results of resource-consuming queries to avoid repeated query computations.
Data distribution: Multiple copies of data are distributed to different regions so that users can access the data nearby.
Pre-aggregation of monitoring data: Monitoring data is displayed by different time intervals.

Examples

You can create materialized views to speed up queries on specified tables. First, you must define schemas and materialized views.

Define schemas

CREATE TABLE orders (
  order_id INT primary key,
  user_id INT,
  item_id INT,
  item_count INT,
  item_price DECIMAL(10, 2) NOT NULL,
  region VARCHAR(100)
);

CREATE TABLE order_items (
  order_id INT,
  product_id INT,
  quantity INT,
  price_per_item DECIMAL(10, 2) NOT NULL,
  primary key (order_id, product_id)
);

Create materialized views

The following examples show how to create materialized views based on the preceding information.

Summary materialized view: calculates the total sales of each region.

CREATE MATERIALIZED VIEW m1
REFRESH FAST ON DEMAND
AS
SELECT
    SUM(item_count * item_price) AS total_sales,
    region
FROM
    orders
GROUP BY
    region;

Wide-table materialized view: associates the orders with the item details to generate a wide table.

CREATE MATERIALIZED VIEW wide_orders
REFRESH FAST ON DEMAND
AS
SELECT
    o.order_id,
    o.user_id AS customer_id,
    o.item_id,
    o.item_count AS quantity,
    o.item_price AS price_per_item,
    o.region
FROM
    orders o
JOIN
    order_items oi ON o.order_id = oi.order_id;

Use the materialized views to speed up queries

Assume that a query needs to quickly obtain the order details and total sales of a region. You can use the preceding materialized views to speed up the query. Here is an example:

-- Query details about all orders and the total sales of a specified region.
SELECT
    wo.order_id,
    wo.customer_id,
    wo.product_id,
    wo.quantity,
    wo.price_per_item,
    m1.total_sales
FROM
    wide_orders wo
JOIN
    m1 ON wo.region = m1.region
WHERE
    wo.region = 'North America';

In this query, the wide_orders materialized view provides the order and item details in the wide table, and the m1 materialized view provides the total sales of each region. You can efficiently query the order details and total sales of a specified region by using the two materialized views.

Refresh strategies

To keep the data of materialized views up to date, you can set an appropriate refresh strategy as needed. In the preceding examples, the REFRESH FAST ON DEMAND strategy is used, which specifies that the refresh needs to be manually triggered. If you want the system to automatically and periodically refresh materialized views, you can modify the refresh strategy. Here are some examples.

-- Automatic refresh on an hourly basis
CREATE MATERIALIZED VIEW m1
REFRESH FAST
START WITH SYSDATE
NEXT SYSDATE + 1/24
AS
SELECT
    SUM(item_count * item_price) AS total_sales,
    region
FROM
    orders
GROUP BY
    region;

-- Automatic refresh on a daily basis
CREATE MATERIALIZED VIEW wide_orders
REFRESH FAST
START WITH SYSDATE
NEXT SYSDATE + 1
AS
SELECT
    o.order_id,
    o.user_id AS customer_id,
    o.item_id,
    o.item_count AS quantity,
    o.item_price AS price_per_item,
    o.region
FROM
    orders o
JOIN
    order_items oi ON o.order_id = oi.order_id;

This way, the system automatically refreshes the materialized views at specified time intervals to ensure the timeliness and accuracy of data, thus accelerating queries.

Create a materialized view

create_mv_stmt :=
CREATE MATERIALIZED VIEW view_name opt_column_list opt_table_option_list opt_partition_option [refresh_clause] AS view_select_stmt

refresh_clause :=
REFRESH [FAST/COMPLETE/FORCE] [mv_refresh_on_clause]
/
NEVER REFRESH


mv_refresh_on_clause :=
[ON DEMAND] [[START WITH expr] [NEXT expr]]

The preceding statement creates a materialized view. The syntax is similar to that for creating a standard view, except that the refresh_clause parameter is added in the statement to specify how the materialized view is to be refreshed. For more information, see the "Refreshes" section in this topic.

When you create a materialized view, you can specify the refresh_clause parameter to define a scheduled refresh strategy for the materialized view. Valid values of this parameter are FAST, COMPLETE, and FORCE. You can also set the refresh trigger condition to ON DEMAND, START WITH expr, or NEXT expr. The syntax and examples for creating a materialized view defined with scheduled refreshes are described as follows:

Syntax

CREATE MATERIALIZED VIEW view_name
REFRESH [FAST | COMPLETE | FORCE]
[ON DEMAND | START WITH expr NEXT expr | NEVER REFRESH]
AS
SELECT ...

FAST: specifies to perform incremental refreshes.
COMPLETE: specifies to perform complete refreshes.
FORCE: specifies to perform incremental or complete refreshes as needed.
ON DEMAND: specifies to perform refreshes when they are manually triggered.
START WITH expr: defines the start time of the first refresh.
NEXT expr: defines the start time of the next refresh.
NEVER REFRESH: specifies to never perform automatic refreshes. Refreshes can be triggered only manually.

Examples

Specify to perform incremental refreshes on demand.

CREATE MATERIALIZED VIEW sales_mv
REFRESH FAST ON DEMAND
AS
SELECT product_id, SUM(amount) AS total_sales
FROM sales
GROUP BY product_id;

Specify to perform complete refreshes on a daily basis.

CREATE MATERIALIZED VIEW daily_sales_mv
REFRESH COMPLETE
START WITH SYSDATE
NEXT SYSDATE + 1
AS
SELECT product_id, SUM(amount) AS total_sales
FROM sales
GROUP BY product_id;

Specify to perform a refresh on an hourly basis by using the automatically selected refresh method.

CREATE MATERIALIZED VIEW hourly_sales_mv
REFRESH FORCE
START WITH SYSDATE
NEXT SYSDATE + 1/24
AS
SELECT product_id, SUM(amount) AS total_sales
FROM sales
GROUP BY product_id;

Specify to never perform automatic refreshes.

CREATE MATERIALIZED VIEW static_sales_mv
REFRESH NEVER
AS
SELECT product_id, SUM(amount) AS total_sales
FROM sales
GROUP BY product_id;

The preceding refresh strategies are described as follows:

Perform incremental refreshes on demand: The sales_mv materialized view is incrementally refreshed when a refresh is manually triggered.
Perform complete refreshes on a daily basis: The daily_sales_mv materialized view is completely refreshed on a daily basis.
Perform a refresh on an hourly basis by using the automatically selected refresh method: The hourly_sales_mv materialized view is refreshed on an hourly basis. The database automatically chooses whether to perform an incremental refresh or a complete refresh.
Never perform automatic refreshes: The static_sales_mv materialized view is not automatically refreshed. Refreshes can be triggered only manually.

You can set refresh strategies based on your business needs and system performance requirements to keep the data of materialized views up to date in a timely and efficient manner.

Drop a materialized view

DROP MATERIALIZED VIEW opt_if_exists table_list opt_drop_behavior

You cannot move materialized views to the recycle bin alone. When you drop a database, its materialized views will be moved to the recycle bin.

Refreshes

Materialized view refreshes are crucial for keeping the data of materialized views up to date. Materialized views are database objects that store query results to significantly improve query performance. However, the data of materialized views must be updated as the source data changes, so as to indicate the latest status. Materialized views support complete refreshes and incremental refreshes.

A complete refresh recalculates and replaces all data in the materialized view. This method ensures the integrity and accuracy of the data in the materialized view. However, it consumes a large amount of system resources and time because it recalculates and writes a large amount of data.

An incremental refresh, also known as a fast refresh, updates only the data that has changed since the last refresh. This method significantly reduces the resources and time required but requires additional logs or triggers to be maintained in the database to track data changes.

Refreshes consume system resources. Therefore, you must properly set the refresh frequency for materialized views based on your business needs and system load. The common practice is to regularly refresh materialized views on a daily or hourly basis or even more frequently to ensure the timeliness and accuracy of the data.

Complete refreshes

A complete refresh ensures consistency between the data of the materialized view and the source data. During a complete refresh, the materialized view re-executes the query statement that defined it and completely replaces its internal data. Regardless of the complexity of the query and the amount of data involved, a complete refresh executes the entire query and re-stores the result set to the materialized view. Therefore, complete refreshes support any types of query statements, including complex join, aggregate, and nested queries.

Complete refreshes are simple and reliable. Each complete refresh rebuilds the entire dataset, thus ensuring the accuracy of the data in the materialized view and the consistency with the source data. Especially when the source data changes frequently or the data update pattern is complex, complete refreshes can directly obtain the latest data regardless of the complex changes.

However, complete refreshes consume more system resources and time because they recalculate and write all data, especially when they process large datasets. The resource consumption includes consumption of the computing power and overheads of I/O operations. Therefore, although complete refreshes can guarantee the accuracy and integrity of data, you must properly set the refresh frequency in practice to ensure both system performance and timeliness of data.

The refresh syntax is as follows:

DBMS_MVIEW.REFRESH (
    IN     mv_name                VARCHAR(65535),				-- The name of the materialized view.
    IN     method                 VARCHAR(65535) DEFAULT NULL,	-- The refresh options.
                                                                --   f: specifies to perform fast refreshes.
                                                            		--   ?: specifies to perform forcible refreshes.
                                                            		--   C|c: specifies to perform complete refreshes.
                                                            		--   A|a: specifies to always perform refreshes, which is equivalent to C.
    IN     refresh_parallel       INT            DEFAULT 1);		-- The degree of parallelism (DOP) of refreshes.

Incremental refreshes

An incremental refresh, also known as a fast refresh, updates only the data that has changed since the last refresh. Unlike complete refreshes, incremental refreshes process and update only the changed part of the source data, thus improving the refresh efficiency and reducing the consumption of system resources and time. However, incremental refreshes support only the SELECT statements that meet specific syntax and structural requirements, such as simple join and aggregate query statements.

Incremental refreshes depend on materialized view logs (mlogs). Mlogs are special database objects that record incremental changes in base tables. Mlogs record all INSERT, UPDATE, and DELETE operations on the data in base tables. During an incremental refresh, the system reads records from mlogs, identifies and processes the changes, and updates only related data in the materialized view. Although incremental refreshes significantly improve the refresh efficiency, mlogs occupy additional database space for storing change logs of base tables. Mlogs can grow in size as data changes accumulate over time. Therefore, you must regularly manage and maintain mlogs to avoid excessive usage of storage resources.

Rewrite queries based on materialized views

You can use materialized views to speed up queries. When you enter a query statement without specifying materialized views, the system automatically matches the query statement against definitions of materialized views and rewrites it into a query statement that contains the matching materialized view. This can significantly improve query performance and efficiency.

Limitations

A materialized view eligible for query rewriting must meet the following requirements:
- The ENABLE QUERY REWRITE clause was specified for the materialized view when it was created.
- The materialized view contains only the SELECT JOIN and WHERE clauses, namely, select-project-join (SPJ) queries. If a matching materialized view does not meet the requirements, it will not be used for query rewriting, but the system does not return an error.
The query statement must meet the following requirements:
- The query statement must be a SELECT statement that does not contain window functions, and cannot be a set-based or hierarchical statement.
- The FROM clause in the query statement exactly matches that in the materialized view.
- The WHERE condition in the materialized view is a subset of that in the query statement. If the query statement contains an aggregate function, the WHERE conditions must exactly match. For example, the WHERE condition in the materialized view is c1 > 10, and the WHERE condition in the query statement is c1 > 10 AND c2 > 20. In this case, the condition {c1 > 10} in the materialized view is a subset of the condition {c1 > 10 AND c2 > 20} in the query statement.
- The columns in the SELECT, WHERE, HAVING, and GROUP BY clauses in the query statement must all be included in the SELECT clause of the materialized view.

Rule and cost check

In OceanBase Database V4.3.3, the system does not check the cost during query rewriting. However, the rule check requires that the WHERE condition in the rewritten query statement use an index on at least one materialized view. If more than 10 materialized views are available, the system will attempt to use only the first 10 materialized views during query rewriting.

Rewrite a query based on a materialized view

The system variables that control materialized view-based query rewriting are as follows:

query_rewrite_enabled: specifies whether to enable materialized view-based query rewriting. For more information about this variable, see query_rewrite_enabled.

Here is an example:
```
SET query_rewrite_enabled = 'force';
```
query_rewrite_integrity: specifies the level of data consistency check for materialized view-based query rewriting. For more information about this variable, see query_rewrite_integrity.

Here is an example:
```
SET query_rewrite_integrity = 'stale_tolerated';
```

Here is an example of materialized view-based query rewriting:

Create a table named test_tbl1.

CREATE TABLE test_tbl1 (col1 INT, col2 INT, col3 INT);

Create a table named test_tbl2.

CREATE TABLE test_tbl2 (col1 INT, col2 INT, col3 INT);

Create a materialized view named mv_test_tbl1_tbl2 with the ENABLE QUERY REWRITE clause specified.

CREATE MATERIALIZED VIEW mv_test_tbl1_tbl2
    ENABLE QUERY REWRITE
    AS SELECT t1.col1 col1, t1.col2 t1col2, t1.col3 t1col3, t2.col2 t2col2, t2.col3 t2col3 
    FROM test_tbl1 t1, test_tbl2 t2
    WHERE t1.col1 = t2.col1;

Enter the SELECT count(*), test_tbl1.col1 col1 FROM test_tbl1, test_tbl2 WHERE test_tbl1.col1 = test_tbl2.col1 AND test_tbl2.col2 > 10 GROUP BY col1; statement. The system automatically rewrites it.
```
SELECT count(*), test_tbl1.col1 col1 FROM test_tbl1, test_tbl2 WHERE test_tbl1.col1 = test_tbl2.col1 AND test_tbl2.col2 > 10 GROUP BY col1;

MV REWRITE ==>

SELECT count(*), mv_test_tbl1_tbl2.col1 col1 FROM mv_test_tbl1_tbl2 WHERE mv_test_tbl1_tbl2.t2col2 > 10 GROUP BY mv_test_tbl1_tbl2.col1;
```
The FROM clause in the input query statement must exactly match that in the materialized view. The WHERE condition in the input query statement must contain all WHERE conditions in the materialized view.

Control materialized view-based query rewriting

You can use the MV_REWRITE and NO_MV_REWRITE hints to control materialized view-based query rewriting. The two hints have a higher priority than the query_rewrite_enabled variable.

MV_REWRITE

The syntax of the MV_REWRITE hint is as follows:

/*+ MV_REWRITE (@ queryblock [mv_name_list]) */

mv_name_list:
    mv_name [, mv_name ...]

When you use the MV_REWRITE hint alone, the system skips rule and cost check and uses all available materialized views for query rewriting. If you specify one or more materialized views after the hint, the system skips rule and cost check and uses only the specified materialized views for query rewriting.

When you use the MV_REWRITE hint to specify materialized views, you cannot forcibly use materialized views created without the ENABLE QUERY REWRITE clause or forcibly use non-real-time materialized views when the query_rewrite_integrity variable is set to enforced.

NO_MV_REWRITE

The syntax of the NO_MV_REWRITE hint is as follows:

/*+ NO_MV_REWRITE (@ queryblock) */

You can specify a query block in this hint.

Examples

Create a base table named tbl2.

CREATE TABLE tbl2 (col1 INT, col2 INT);

Insert two records into the tbl2 table.

INSERT INTO tbl2 VALUES (1,2),(3,4);

The return result is as follows:

Query OK, 2 rows affected
Records: 2  Duplicates: 0  Warnings: 0

Create a materialized view named mv1_tbl2 with the ENABLE QUERY REWRITE clause specified.

CREATE MATERIALIZED VIEW mv1_tbl2 NEVER REFRESH ENABLE QUERY REWRITE AS SELECT * FROM tbl2;

Create a materialized view named mv2_tbl2 with the ENABLE QUERY REWRITE clause specified.

CREATE MATERIALIZED VIEW mv2_tbl2 NEVER REFRESH ENABLE QUERY REWRITE AS SELECT * FROM tbl2 WHERE tbl2.col1 > 1;

Set the query_rewrite_integrity variable to stale_tolerated.

Note

The MV_REWRITE and NO_MV_REWRITE hints have a higher priority than the query_rewrite_enabled variable. Therefore, you do not need to specify the query_rewrite_enabled variable. However, you must set the query_rewrite_integrity variable to stale_tolerated to use non-real-time materialized views for query rewriting.
```
SET query_rewrite_integrity = 'stale_tolerated';
```

Use the MV_REWRITE hint to attempt to rewrite a query statement based on a materialized view while skipping cost and rule check. The following two query statements are rewritten based on the mv1_tbl2 materialized view.

The /*+mv_rewrite*/ hint attempts to find a materialized view that meets the query rewriting requirements and then rewrites the query statement based on the materialized view while skipping cost and rule check. After the hint finds a materialized view that meets the query rewriting requirements, subsequent materialized views will be ignored.

EXPLAIN SELECT /*+mv_rewrite*/ count(*), col1 FROM tbl2 WHERE tbl2.col1 > 1 GROUP BY col1;

The return result is as follows:

+------------------------------------------------------------------------------------+
| Query Plan                                                                         |
+------------------------------------------------------------------------------------+
| =====================================================                              |
| |ID|OPERATOR         |NAME    |EST.ROWS|EST.TIME(us)|                              |
| -----------------------------------------------------                              |
| |0 |HASH GROUP BY    |        |1       |3           |                              |
| |1 |└─TABLE FULL SCAN|mv1_tbl2|1       |3           |                              |
| =====================================================                              |
| Outputs & filters:                                                                 |
| -------------------------------------                                              |
|   0 - output([T_FUN_COUNT(*)], [mv1_tbl2.col1]), filter(nil), rowset=16            |
|       group([mv1_tbl2.col1]), agg_func([T_FUN_COUNT(*)])                           |
|   1 - output([mv1_tbl2.col1]), filter([mv1_tbl2.col1 > 1]), rowset=16              |
|       access([mv1_tbl2.col1]), partitions(p0)                                      |
|       is_index_back=false, is_global_index=false, filter_before_indexback[false],  |
|       range_key([mv1_tbl2.__pk_increment]), range(MIN ; MAX)always true            |
+------------------------------------------------------------------------------------+
14 rows in set

The /*+mv_rewrite(mv2_tbl2)*/ hint attempts to rewrite the query statement based on the mv2_tbl2 materialized view while skipping cost and rule check.

EXPLAIN SELECT /*+mv_rewrite(mv2_tbl2)*/ count(*), col1 FROM tbl2 WHERE tbl2.col1 > 1 GROUP BY col1;

The return result is as follows:

+-------------------------------------------------------------------------+
| Query Plan                                                              |
+-------------------------------------------------------------------------+
| =====================================================                   |
| |ID|OPERATOR         |NAME    |EST.ROWS|EST.TIME(us)|                   |
| -----------------------------------------------------                   |
| |0 |HASH GROUP BY    |        |1       |3           |                   |
| |1 |└─TABLE FULL SCAN|mv2_tbl2|1       |3           |                   |
| =====================================================                   |
| Outputs & filters:                                                      |
| -------------------------------------                                   |
|   0 - output([T_FUN_COUNT(*)], [mv2_tbl2.col1]), filter(nil), rowset=16 |
|       group([mv2_tbl2.col1]), agg_func([T_FUN_COUNT(*)])                |
|   1 - output([mv2_tbl2.col1]), filter(nil), rowset=16                   |
|       access([mv2_tbl2.col1]), partitions(p0)                           |
|       is_index_back=false, is_global_index=false,                       |
|       range_key([mv2_tbl2.__pk_increment]), range(MIN ; MAX)always true |
+-------------------------------------------------------------------------+
14 rows in set

The hint specifies to rewrite the query statement based on the mv2_tbl2 materialized view. However, the WHERE condition in the query statement does not meet the requirements for query rewriting. Therefore, the mv2_tbl2 materialized view cannot be used for query rewriting, and the query statement will not be rewritten.

EXPLAIN SELECT /*+mv_rewrite(mv2_tbl2)*/ count(*), col1 FROM tbl2 WHERE tbl2.col1 < 1 GROUP BY col1;

The return result is as follows:

+------------------------------------------------------------------------------------+
| Query Plan                                                                         |
+------------------------------------------------------------------------------------+
| =================================================                                  |
| |ID|OPERATOR         |NAME|EST.ROWS|EST.TIME(us)|                                  |
| -------------------------------------------------                                  |
| |0 |HASH GROUP BY    |    |1       |3           |                                  |
| |1 |└─TABLE FULL SCAN|tbl2|1       |3           |                                  |
| =================================================                                  |
| Outputs & filters:                                                                 |
| -------------------------------------                                              |
|   0 - output([T_FUN_COUNT(*)], [tbl2.col1]), filter(nil), rowset=16                |
|       group([tbl2.col1]), agg_func([T_FUN_COUNT(*)])                               |
|   1 - output([tbl2.col1]), filter([tbl2.col1 < 1]), rowset=16                      |
|       access([tbl2.col1]), partitions(p0)                                          |
|       is_index_back=false, is_global_index=false, filter_before_indexback[false],  |
|       range_key([tbl2.__pk_increment]), range(MIN ; MAX)always true                |
+------------------------------------------------------------------------------------+
14 rows in set

Use the /*+ no_mv_rewrite*/ hint to disable materialized view-based query rewriting.

EXPLAIN SELECT /*+no_mv_rewrite*/ count(*), col1 FROM tbl2 WHERE tbl2.col1 > 1 GROUP BY col1;

The return result is as follows:

+------------------------------------------------------------------------------------+
| Query Plan                                                                         |
+------------------------------------------------------------------------------------+
| =================================================                                  |
| |ID|OPERATOR         |NAME|EST.ROWS|EST.TIME(us)|                                  |
| -------------------------------------------------                                  |
| |0 |HASH GROUP BY    |    |1       |3           |                                  |
| |1 |└─TABLE FULL SCAN|tbl2|1       |3           |                                  |
| =================================================                                  |
| Outputs & filters:                                                                 |
| -------------------------------------                                              |
|   0 - output([T_FUN_COUNT(*)], [tbl2.col1]), filter(nil), rowset=16                |
|       group([tbl2.col1]), agg_func([T_FUN_COUNT(*)])                               |
|   1 - output([tbl2.col1]), filter([tbl2.col1 > 1]), rowset=16                      |
|       access([tbl2.col1]), partitions(p0)                                          |
|       is_index_back=false, is_global_index=false, filter_before_indexback[false],  |
|       range_key([tbl2.__pk_increment]), range(MIN ; MAX)always true                |
+------------------------------------------------------------------------------------+
14 rows in set

Mlogs

Overview

Mlogs are special database objects that record data changes in base tables. They are essential for incremental refreshes of materialized views. Mlogs are associated with base tables to capture and store the details of all INSERT, UPDATE, and DELETE operations on the base tables. Based on these logs, when refreshing a materialized view, the system needs to apply only the changes recorded in the logs since the last refresh without rescanning the entire base table.

Create an mlog

The syntax is as follows:

CREATE MATERIALIZED VIEW LOG ON [ schema. ] table
	[ parallel_clause ]
  [ WITH [ { PRIMARY KEY
         | ROWID
         | SEQUENCE
         }
           [ { , PRIMARY KEY
             | , ROWID
             | , SEQUENCE
             }
           ]... ]
    (column [, column ]...)
    [ new_values_clause ]
  ] [ mv_log_purge_clause ]
;

The parameters are described as follows:

schema: the schema where the base table corresponding to the mlog is located. If you do not specify this parameter, the current schema is used.
table: the name of the base table corresponding to the mlog.
parallel_clause: the DOP for creating mlogs.
WITH clause: specifies the columns attached to the mlog. PRIMARY KEY indicates all primary key columns of the base table. ROWID indicates hidden primary key columns of the base table when it has no primary key. SEQUENCE indicates the sequence numbers of multiple rows updated in a transaction. Observe the following rules:
- You can use the WITH PRIMARY KEY clause only for tables with primary keys. If you use it for a table without a primary key, it does not take effect. If you do not specify the WITH clause, the system will automatically add the PRIMARY KEY option.
- You can use the WITH ROWID clause only for tables without primary keys. If you use it for a table with a primary key, it does not take effect. If you do not specify the WITH clause, the system will automatically add the ROWID option.
- The system will automatically add the SEQUENCE option to the WITH clause.
new_values_clause: specifies whether to record both the old and new values in UPDATE operations to the mlog.

If you want the materialized view to support fast refreshes, you must specify the INCLUDING NEW VALUES clause.

{ INCLUDING | EXCLUDING } NEW VALUES

Observe the following rules:

The system will automatically add the INCLUDING NEW VALUES clause.
If you specify the EXCLUDING NEW VALUES clause, the system will return an error.
mv_log_purge_clause: the time to purge the data in the mlog.

PURGE { IMMEDIATE [ SYNCHRONOUS | ASYNCHRONOUS ]
      | START WITH datetime_expr [ NEXT datetime_expr ]
      | [ START WITH datetime_expr ] NEXT datetime_expr
      }

The options are described as follows:

IMMEDIATE specifies to purge the corresponding mlog immediately after each refresh of the materialized view. SYNCHRONOUS specifies to purge the mlog synchronously. ASYNCHRONOUS specifies to purge the mlog asynchronously. At present, the ASYNCHRONOUS option is not supported. If you specify this option, the system will return an error.
START WITH datetime_expr specifies the time when the mlog is to be purged for the first time. NEXT datetime_expr specifies the time when the materialized view log is to be purged the next time. The values of both parameters must be later than the current time. Otherwise, the system will return an error.
If you specify only the NEXT datetime_expr parameter, the START WITH datetime_expr parameter will be automatically set to the same value.

The following sample statement creates an mlog based on the t1 table and specifies to purge the data in the mlog on a daily basis after it is created.

-- MySQL mode
create materialized view log on t1 purge start with sysdate() next sysdate() + interval 1 day;

-- Oracle mode (We recommend that you use `current_date` to specify the datetime in the current time zone, because `sysdate` is not affected by the time zone.)
create materialized view log on t1 purge start with current_date next current_date + 1;

Note

Same as with local indexes, you cannot specify partitions for mlogs, and the partitions of an mlog are the same as those of the corresponding base table.

Drop an mlog

You can drop an mlog corresponding to a base table.

The syntax is as follows:

DROP MATERIALIZED VIEW LOG ON [ schema. ] table;

When you drop an mlog, if a transaction is running on the base table, the drop operation is blocked until the transaction is completed.

Limitations

Although mlogs play an important role in database systems, especially in incremental refreshes, they have some limitations and disadvantages:

Additional disk usage: Mlogs store change records of base tables in the database, which occupies additional disk space. Especially, when data is frequently updated, log tables can grow rapidly and occupy a large amount of disk space.
Impact on performance: Mlogs record all INSERT, UPDATE, and DELETE operations on base tables. Each write operation causes additional overheads, which can affect the write performance of the base tables.
High disk usage when no refresh is performed for a long time: If materialized views are not refreshed for a long time, unprocessed records in mlogs will accumulate and occupy a large amount of disk space. This affects not only storage resources but also overall performance of the database.
Complexity of management: Database administrators (DBAs) must regularly maintain and manage mlogs, for example, properly specify the refresh interval, monitor the log size, and purge expired logs. This increases the workload of DBAs and the complexity of management.
Challenges in backup and restore: Mlogs do not support backup and restore.

References

For more information about materialized views, see Overview (MySQL mode) and Overview (Oracle mode).