物化视图

物化视图存储了视图定义查询的结果。可以加速该视图查询，减少查询的系统资源消耗（cpu, 网络，磁盘等）。利用存储物化视图结果的空间换取的查询时候的时间。

常见用例

• 数据汇总：汇总每天、每周或每月的销售数据、统计用户行为数据等
• 统计信息报表数据生成：报表系统需要定期生成固定格式的数据报告
• 复杂查询优化：对于特别消耗资源的查询，可以将结果物化避免查询重复计算
• 分发数据：冗余多份数据放到不同的区域中，便于各个区域的人就近访问数据
• 监控数据的预聚合：监控数据会按照不同的时间间隔进行展示

使用示例

基于给定的表和物化视图创建情况，以下是如何生成一个物化视图来加速查询的具体示例。首先，让我们确保我们有一个适当的表结构和物化视图定义。

表结构定义

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 VIEW m1
REFRESH COMPLETE ON DEMAND
AS
SELECT
    SUM(item_count * item_price) AS total_sales,
    count(item_count * item_price) cnt_total_sales,
    count(*) cnt,
    region
FROM
    orders
GROUP BY
    region;

宽表物化视图：将订单与商品详情关联，形成一个宽表

CREATE MATERIALIZED VIEW wide_orders
REFRESH COMPLETE 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;

加速查询示例

假设我们有一个查询，需要快速获取某个地区的订单详情和销售总额，可以使用上述物化视图来加速查询。以下是一个具体的示例查询。

-- 查询某个地区的所有订单详情和总销售额
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';

在这个查询中，wide_orders 物化视图提供了订单与商品详情的宽表数据，m1 物化视图提供了每个地区的总销售额。通过将这两个物化视图结合起来，我们可以快速查询特定地区的订单详情和总销售额。

刷新策略

为了保持物化视图的数据最新，我们可以根据需求设置合适的刷新策略。上述示例中，我们使用了 REFRESH COMPLETE ON DEMAND，表示需要手动触发刷新。如果需要定期自动刷新，可以修改刷新策略，例如：

-- 每小时自动刷新一次
CREATE MATERIALIZED VIEW m1
REFRESH COMPLETE
START WITH SYSDATE
NEXT SYSDATE + 1/24
AS
SELECT
    SUM(item_count * item_price) AS total_sales,
    region
FROM
    orders
GROUP BY
    region;

-- 每天自动刷新一次
CREATE MATERIALIZED VIEW wide_orders
REFRESH COMPLETE
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;

通过这种方式，物化视图可以在指定的时间间隔内自动刷新，以确保数据的及时性和准确性，从而加速查询性能。

创建物化视图

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

语法如上图所示，和创建普通视图类似。最主要的是增加了 refresh 子句来描述刷新的信息。在刷新章节会详细介绍。

在创建物化视图时，可以通过指定刷新子句（refresh_clause）来定义物化视图的定时刷新策略。这包括选择增量刷新（FAST）、全量刷新（COMPLETE）或根据情况自动选择的强制刷新（FORCE）。此外，还可以设置刷新触发条件，如按需（ON DEMAND）、指定开始时间（START WITH expr）和下一次刷新时间（NEXT expr）。以下是定义物化视图定时刷新的语法和示例。

语法

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

• FAST：进行增量刷新。
• COMPLETE：进行全量刷新。
• FORCE：根据当前情况自动选择增量或全量刷新。
• ON DEMAND：按需刷新，即手动触发刷新。
• START WITH expr：定义首次刷新的时间。
• NEXT expr：定义下一次刷新的时间。
• NEVER REFRESH：不自动刷新，只能手动刷新。

示例

按需增量刷新

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

每日全量刷新

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;

每小时自动选择刷新类型

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;

从不自动刷新

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

解释:

• 按需增量刷新：物化视图 sales_mv 将使用增量刷新，但仅在手动触发时刷新。
• 每日全量刷新：物化视图 daily_sales_mv 每天刷新一次，每次执行全量刷新。
• 每小时自动选择刷新类型：物化视图 hourly_sales_mv 每小时刷新一次，数据库会自动选择使用增量刷新还是全量刷新。
• 从不自动刷新：物化视图 static_sales_mv 不会自动刷新，只能通过手动操作进行刷新。

通过合理设置这些刷新策略，可以根据具体需求和系统性能要求，确保物化视图的数据既及时又高效地保持最新。

删除物化视图

DROP MATERIALIZED VIEW opt_if_exists table_list opt_drop_behavior

物化视图不能单独进回收站。删除 database 的时候，物化视图能进回收站。

刷新

物化视图刷新（Materialized View Refresh）是确保物化视图中的数据保持最新状态的重要操作。物化视图是一种存储查询结果的数据库对象，可以显著提高查询性能。然而，随着源数据的变化，物化视图的数据也需要更新以反映最新的状态。物化视图的刷新可以分为全量刷新和增量刷新两种方式。

全量刷新（Complete Refresh）指的是重新计算并替换物化视图中的所有数据。这种方式确保了物化视图数据的完整性和准确性，但由于涉及大量数据的重算和写入操作，全量刷新通常会消耗较多的系统资源和时间。

增量刷新（Incremental Refresh），也称为快速刷新（Fast Refresh），则是只更新自上次刷新以来发生变化的数据。这种方式可以显著减少刷新所需的资源和时间，但需要在数据库中维护额外的日志或触发器，以跟踪数据的变化。

由于刷新操作会消耗系统资源，因此需要根据实际需求和系统负载，合理安排物化视图的刷新频率。常见的做法是定期刷新，可以是每天、每小时甚至更频繁地执行，以确保物化视图数据的及时性和准确性。

全量刷新

物化视图全量刷新（Complete Refresh）是一种确保物化视图数据与源数据完全一致的刷新方式。在全量刷新过程中，物化视图会重新执行定义它的查询语句，并完全替换其内部数据。这意味着，不论查询的复杂度和涉及的数据量多大，全量刷新都会执行整个查询并将结果集全部重新存储到物化视图中。因此，全量刷新能够支持任何类型的查询语句，包括复杂的联接、聚合和嵌套查询。

全量刷新的主要优势在于其简单和可靠性。因为它每次都会重建整个数据集，所以能够确保物化视图中的数据绝对准确，与源数据保持完全一致。尤其在源数据变化频繁或数据更新模式复杂的场景下，全量刷新提供了一种无视这些复杂变化、直接获取最新数据的方法。

然而，由于全量刷新涉及对全部数据的重新计算和写入，因此相对来说，它会消耗更多的系统资源和时间，特别是在处理大规模数据集时。这种资源消耗不仅包括计算能力，还包括 I/O 操作的开销。因此，虽然全量刷新能够提供数据准确性和完整性，但在实际应用中需要慎重考虑其执行频率，以平衡系统性能和数据及时性之间的关系。

刷新语法

DBMS_MVIEW.REFRESH (
    IN     mv_name                VARCHAR(65535),				-- 物化视图名称
    IN     method                 VARCHAR(65535) DEFAULT NULL,	-- 刷新选项
                                                                --   f 快速刷新
                                                            		--   ? 强制刷新
                                                            		--   C|c 完全刷新
                                                            		--   A|a 始终刷新，等价于C
    IN     refresh_parallel       INT            DEFAULT 1);		-- 刷新并行度

增量刷新

物化视图增量刷新（Incremental Refresh），也称为快速刷新（Fast Refresh），是一种仅更新自上次刷新以来发生变化的数据的刷新方式。与全量刷新不同，增量刷新只对源数据中发生变更的部分进行处理和更新，从而提高刷新效率，减少系统资源消耗和刷新时间。然而，增量刷新仅支持部分 SELECT 语句，通常需要满足特定的语法和结构要求，如简单的联接和聚合操作。

增量刷新依赖于物化日志（Materialized View Log, 简称 MLOG）的支持。MLOG 是一个特殊的数据库对象，用于记录基表中发生的增量变化。每当基表中的数据被插入、更新或删除时，这些变化都会记录在 MLOG 中。增量刷新时，系统读取 MLOG 中的记录，识别并处理这些变化，从而只更新物化视图中的相关部分数据。 虽然增量刷新显著提高了刷新效率，但 MLOG 的使用会额外占用数据库空间，因为它需要存储基表的变更日志。随着时间推移和数据变更的积累，MLOG 的大小可能不断增长，需定期管理和维护，以避免占用过多存储资源。

物化视图查询改写

通过使用物化视图来加速查询，当输入一个不使用物化视图的查询时，系统会自动将查询重写为使用现有物化视图的查询。该方法的原理是将查询语句与物化视图的定义进行匹配，如果发现匹配的物化视图，则自动将查询重写为使用物化视图的查询，这样可以大大提高查询性能和效率。

物化视图查询改写使用限制

• 物化视图满足以下要求：

  • 创建物化视图时，指定 ENABLE QUERY REWRITE 开启当前物化视图的自动改写。
  • 需要物化视图仅包含 SELECT JOIN 与 WHERE 子句，即 SPJ 查询。对于不满足条件的物化视图，不会报错，但不会被用于改写。

• 当前查询满足以下要求：

  • 查询为 SELECT 查询，不是集合查询或层次查询，并且不包含窗口函数。
  • FROM 子句与物化视图完全匹配。
  • WHERE 条件物化视图是当前查询的子集，当前查询有聚合的情况下需要完全匹配。例如，物化视图的 WHERE 是 c1 > 10，当前查询的 WHERE 是 c1 > 10 AND c2 >20。这样物化视图的条件 {c1 > 10} 是当前查询 {c1>10, c2>20} 的子集。
  • 当前查询所涉及的 SELECT 项、WHERE、HAVING 和 GROUP BY 等条件中的列，都需要包含在物化视图的 SELECT 语句中的列中。

规则/代价检查

查询改写时，OceanBase 数据库当前版本不进行代价检查，但规则检查要求改写后的 WHERE 条件能够使用至少一个物化视图上的索引。此外，当有超过 10 个的物化视图存在时，物化视图查询改写将仅尝试使用前 10 个物化视图。

物化视图改写

控制物化视图查询改写的系统变量如下：

• query_rewrite_enabled：用于是否开启物化视图改写功能。该变量的详细介绍信息，参见 query_rewrite_enabled。

  示例如下：

  SET query_rewrite_enabled = 'force';
  

• query_rewrite_integrity：用于指定物化视图改写的数据一致性检查级别。该变量的详细介绍信息，参见 query_rewrite_integrity。

  示例如下：

  SET query_rewrite_integrity = 'stale_tolerated';
  

通过以下示例展示物化视图改写：

1. 创建表 test_tbl1。

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

2. 创建表 test_tbl2。

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

3. 创建名为 mv_test_tbl1_tbl2 的物化视图，并开启当前物化视图的自动改写。

   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;
   

4. 当输入查询 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; 时，发生物化视图查询改写。

   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;
   

   物化视图查询改写要求输入的查询与物化视图的 FROM 完全匹配，WHERE 条件包含物化视图中的所有 WHERE 条件。

物化视图查询改写控制

物化视图查询改写控制包括 MV_REWRITE、NO_MV_REWRITE 两个 Hint，并且这两个 Hint 的优先级高于系统变量 query_rewrite_enabled。

MV_REWRITE

MV_REWRITE 语法如下：

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

mv_name_list:
    mv_name [, mv_name ...]

单独使用 MV_REWRITE Hint 时可以跳过物化视图查询改写的规则/代价检查，直接使用可以使用的改写。Hint 后指定一个或多个物化视图的情况，除了跳过规则/代价检查外，物化视图查询改写将只使用指定的物化视图尝试改写，忽略所有没被指定的物化视图。

使用 MV_REWRITE Hint 指定物化视图时，无法强制使用没有 ENABLE QUERY REWRITE（开启当前物化视图的自动改写） 子句的物化视图，无法在系统变量 query_rewrite_integrity 设置为 enforced 的情况下强制使用非实时物化视图。

NO_MV_REWRITE

NO_MV_REWRITE 语法如下：

/*+ NO_MV_REWRITE (@ queryblock) */

禁止物化视图查询改写，可以指定 query block。

物化视图查询改写控制 Hint 使用示例

1. 创建基表 tbl2。

   CREATE TABLE tbl2 (col1 INT, col2 INT);
   

2. 向基表 tbl2 中插入两条数据。

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

   返回结果如下：

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

3. 创建物化视图 mv1_tbl2，并开启当前物化视图的自动改写。

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

4. 创建物化视图 mv2_tbl2，并开启当前物化视图的自动改写。

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

5. 设置系统变量 query_rewrite_integrity 为 stale_tolerated。

   说明

   MV_REWRITE 和 NO_MV_REWRITE Hint 的优先级高于系统变量 query_rewrite_enabled，因此不需要设置 query_rewrite_enabled。但是需要设置 query_rewrite_integrity 为 stale_tolerated 才可以使用非实时物化视图进行改写。

   SET query_rewrite_integrity = 'stale_tolerated';
   

6. 使用 MV_REWRITE Hint 用物化视图尝试进行改写，并跳过改写代价/规则检查。下面两条查询都将使用物化视图 mv1_tbl2 进行改写。

   • /*+mv_rewrite*/ 将尝试使用符合改写条件的物化视图进行改写，一旦找到了符合改写要求的物化视图，后续的物化视图将不再被考虑，并跳过改写代价/规则检查。

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

     返回结果如下：

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

   • /*+mv_rewrite(mv2_tbl2)*/ 将尝试使用 mv2_tbl2 进行改写，并跳过改写代价/规则检查。

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

     返回结果如下：

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

7. 虽然查询指定使用 mv2_tbl2 来查询改写，由于查询语句的 WHERE 条件不满足要求，mv2_tbl2 无法用于查询改写，所以此查询不会进行物化视图查询改写。

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

   返回结果如下：

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

8. 使用 /*+ no_mv_rewrite*/ Hint，不会进行物化视图查询改写。

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

   返回结果如下：

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

物化视图日志

定义

物化视图日志（Materialized View Log）是一种特殊的数据库对象，用于记录在基表（base table）上发生的数据更改。它是实现物化视图（Materialized View）增量刷新机制的关键组件。物化视图日志是关联到基表，用于捕获和存储基表上每次数据修改（插入、更新、删除）的详细信息。这些日志使得物化视图在刷新时无需重新扫描整个基表，而是只需应用自上次刷新以来记录在日志中的更改，从而提高刷新效率。

创建物化视图日志

以下为创建物化视图日志的语法。

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

以下为具体的语义解释。

• schema：物化视图日志基表所在的 schema（数据库），不指定就在当前的 schema 中。
• table：物化视图日志对应的基表名称。
• parallel_clause：创建物化视图表的 dop
• with_clause：指定物化视图日志中包含的辅助列，primary key 表示基表的所有主键列，rowid 表示基表为无主键表时的隐藏主键列，sequence 表示一个事务内的多行更新序号号（seq_no）。规则如下：
  • with primary key 只能用在有主键表中，在无主键表中使用不会有任何效果，如果不指定，则系统会自动带上。
  • with rowid 只能用在无主键表中，在有主键表中使用不会有任何效果，如果不指定，则系统会自动带上。
  • with sequence 会自动加上。
• new_values_clause：是否在物化视图日志中同时记录 update 操作中的旧值和新值。

如果希望 MV 支持快速刷新，则必须指定 INCLUDING NEW VALUES。

{ INCLUDING | EXCLUDING } NEW VALUES

规则：

• including new values 会自动加上
• excluding new values 会报错
• mv_log_purge_clause：指定物化视图日志中数据的清除时间。

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

子选项说明：

• IMMEDIATE 表示在每次刷新完物化视图后就立即清除相应的物化视图日志，其中 SYNCHRONOUS 表示同步地执行清除，而 ASYNCHRONOUS 则表示异步地执行清除，但是目前不支持异步清除，用户设置该异步选项会报错。
• START WITH datetime_expr 表示第一次清除物化视图日志的时间，NEXT datetime_expr 则是下一次清除的时间，这两个时间必须是在未来，否则会报错。
• 如果 START WITH datetime_expr 参数没有指定，而只指定了 NEXT datetime_expr，那么第一次清除物化视图日志的时间将被设置为 NEXT datetime_expr 参数值。

示例：“在表 t1 上面创建一个物化视图日志，从创建的时刻开始，每天清除一次”的语句如下。

--mysql模式
create materialized view log on t1 purge start with sysdate() next sysdate() + interval 1 day;

--oracle模式（oracle模式中建议使用current_date表示当前时区时间，因为sysdate不受时区影响）
create materialized view log on t1 purge start with current_date next current_date + 1;

删除物化视图日志

以下为删除物化视图日志的语法。

表示删除某个基表对应的物化视图日志。

DROP MATERIALIZED VIEW LOG ON [ schema. ] table;

删除物化视图日志时，如果基表正处于某个运行的事务中，则直到该事务结束前，删除操作都会阻塞。

物化视图日志的局限性

虽然物化视图日志在数据库系统中具有重要的作用，特别是在实现增量刷新方面，但它们也存在一些局限性和缺点：

• 占用额外空间： 物化视图日志需要在数据库中存储基表的更改记录，这会占用额外的磁盘空间。特别是在数据更新频繁的场景下，日志表可能会迅速增长，导致大量的磁盘空间被占用。
• 性能影响： 每次对基表的插入、更新或删除操作，都会在物化视图日志中记录相应的更改。这意味着每次写操作都需要额外的开销，可能会影响基表的写入性能。
• 长期不刷新带来的空间占用： 如果物化视图长时间不刷新，物化视图日志中的未处理记录会不断积累，占用大量磁盘空间。这样的累积不仅会影响存储资源，还可能影响数据库的整体性能。
• 管理复杂性： 物化视图日志需要定期维护和管理，例如确定适当的刷新间隔、监控日志大小以及清理过期日志记录等。这增加了数据库管理员的工作负担和管理复杂性。
• 恢复和备份挑战：目前物化视图日志不支持备份恢复。

相关文档

• 有关物化视图的详细介绍和使用指导，参见 物化视图概述（MySQL 模式） 和 物化视图概述（Oracle 模式）。