无MV日志时如何增量刷新Oracle物化视图（非原生快速刷新）

当源表无法创建MV日志但需要增量刷新物化视图时，以下几种方案可以满足需求：

1. Oracle 19c 哈希增量刷新（Hash-Based Incremental Refresh） #

这是Oracle 19c引入的原生特性，无需依赖MV日志，通过行哈希值对比实现增量同步：

• 核心原理：创建MV时自动计算源表每行的哈希值并存储，刷新时对比源表与MV的行哈希值，仅同步哈希值不一致或新增/删除的行。
• 创建语法：

CREATE MATERIALIZED VIEW your_mv_name
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
USING HASH
AS
SELECT col1, col2, col3 -- 替换为你的源表字段
FROM your_source_table;

• 注意事项：
  • 仅支持Oracle 19c及更高版本
  • 源表最好有主键/唯一键，降低哈希冲突概率（无主键也可使用，但冲突风险略高）
  • 刷新性能略逊于MV日志驱动的增量，但远优于全量刷新，适合更新频率中等的场景

2. 自定义增量刷新脚本 #

如果无法使用19c的哈希特性，可以自行编写SQL脚本实现增量同步，常见两种思路：

基于时间戳字段同步 #

若源表有记录修改时间的字段（如last_updated），可通过时间过滤仅同步更新的行：

-- 假设存在控制表存储上次刷新时间，先初始化或更新刷新时间
MERGE INTO mv_refresh_control c
USING (SELECT 'your_mv_name' AS mv_name, SYSTIMESTAMP AS current_time FROM DUAL) s
ON (c.mv_name = s.mv_name)
WHEN MATCHED THEN UPDATE SET c.last_refresh_time = s.current_time
WHEN NOT MATCHED THEN INSERT (mv_name, last_refresh_time) VALUES (s.mv_name, s.current_time);

-- 同步新增/更新行
MERGE INTO your_mv_name mv
USING (
    SELECT col1, col2, col3, last_updated
    FROM your_source_table
    WHERE last_updated > (SELECT last_refresh_time FROM mv_refresh_control WHERE mv_name = 'your_mv_name')
) src
ON (mv.id = src.id) -- 替换为你的主键/关联字段
WHEN MATCHED THEN UPDATE SET mv.col1 = src.col1, mv.col2 = src.col2, mv.col3 = src.col3
WHEN NOT MATCHED THEN INSERT (col1, col2, col3) VALUES (src.col1, src.col2, src.col3);

-- 可选：删除MV中源表已移除的行
DELETE FROM your_mv_name mv
WHERE NOT EXISTS (SELECT 1 FROM your_source_table src WHERE mv.id = src.id);

自定义行哈希对比 #

自行计算源表和MV的行哈希值，通过哈希差异识别变更行：

-- 创建包含哈希值的MV
CREATE MATERIALIZED VIEW your_mv_name
AS
SELECT 
    col1, col2, col3,
    ORA_HASH(CONCAT(col1, col2, col3)) AS row_hash -- 拼接所有字段计算哈希，或根据实际调整
FROM your_source_table;

-- 刷新脚本
MERGE INTO your_mv_name mv
USING (
    SELECT 
        col1, col2, col3,
        ORA_HASH(CONCAT(col1, col2, col3)) AS row_hash
    FROM your_source_table
) src
ON (mv.id = src.id)
WHEN MATCHED AND mv.row_hash != src.row_hash THEN 
    UPDATE SET mv.col1 = src.col1, mv.col2 = src.col2, mv.col3 = src.col3, mv.row_hash = src.row_hash
WHEN NOT MATCHED THEN 
    INSERT (col1, col2, col3, row_hash) VALUES (src.col1, src.col2, src.col3, src.row_hash);

-- 同步删除操作
DELETE FROM your_mv_name mv
WHERE NOT EXISTS (SELECT 1 FROM your_source_table src WHERE mv.id = src.id);

3. 分区交换刷新（Partition Exchange Load） #

如果源表是分区表（如按日期分区），可以利用分区交换实现高效增量刷新：

• 先给MV创建与源表完全一致的分区结构
• 刷新时，将源表中最近更新的分区交换到临时表
• 将临时表与MV的对应分区进行交换，完成增量同步
• 最后更新分区的统计信息，保证查询性能

4. CDC工具异步同步 #

使用Oracle GoldenGate等Change Data Capture工具，捕获源表的INSERT/UPDATE/DELETE操作，异步同步到MV。这类工具无需依赖MV日志，通过解析数据库重做日志获取变更，适合高并发、高更新频率的场景。

内容的提问来源于stack exchange，提问作者Pravin