Oracle materialized view仅新增不更新全量及数据留存方案咨询

标准物化视图的设计目标是和源表保持数据一致性，不管是全量刷新还是增量快速刷新，都会同步源表的删除操作，因此原生刷新机制确实无法满足你“留存源端已删除数据”的需求，以下是具体实现方案：

最优方案：通过DBMS_SCHEDULER自定义定时任务实现 #

这是成本最低、稳定性最高的适配方案，实现步骤如下：

• 新建独立的业务留存表，结构和你之前物化视图的输出结构一致，额外新增两个字段：数据入库日期（dt，DATE类型，默认当前日期）、数据唯一业务主键（用于避免重复插入，需要建唯一索引/主键约束）
• 编写存储过程实现核心逻辑，参考伪代码如下：

-- 1. 留存表建表示例
CREATE TABLE business_retention (
  id NUMBER PRIMARY KEY,
  -- 此处补充你关联查询需要的所有业务字段
  biz_col1 VARCHAR2(100),
  biz_col2 NUMBER,
  dt DATE DEFAULT SYSDATE
);

-- 2. 核心处理存储过程
CREATE OR REPLACE PROCEDURE proc_refresh_retention IS
BEGIN
  -- 追加昨日新增数据，通过NOT EXISTS避免重复插入
  INSERT INTO business_retention(id, biz_col1, biz_col2)
  SELECT t.id, t.biz_col1, t.biz_col2
  FROM (你的关联查询SQL) t
  WHERE t.业务产生日期 = TRUNC(SYSDATE) - 1
  AND NOT EXISTS (
    SELECT 1 FROM business_retention r WHERE r.id = t.id
  );
  COMMIT;

  -- 清理3个月以上的过期历史数据
  DELETE FROM business_retention WHERE dt < ADD_MONTHS(TRUNC(SYSDATE), -3);
  COMMIT;
END;
/

• 通过DBMS_SCHEDULER创建每日定时任务，设置在业务低峰期（比如凌晨1点）执行上述存储过程即可。

可选改造方案：基于物化视图二次封装（不推荐） #

如果一定要沿用物化视图的能力，可以做两层架构改造：

• 第一层创建增量刷新物化视图，仅同步源表的新增和更新操作，部分数据库支持在物化视图日志中过滤DELETE操作，避免同步源表的删除行为
• 第二层新建留存表，每日定时将第一层物化视图的新增数据追加到留存表，同时执行3个月过期清理逻辑
  该方案额外增加了架构复杂度，且高度依赖数据库的物化视图特性兼容性，不同数据库实现差异较大，容易出现数据一致性问题，因此不推荐使用。

疑问解答 #

不是只能使用DBMS_SCHEDULER实现，但该方案是最适配当前场景的选择：原生物化视图的设计逻辑就是和源表对齐数据，天然不支持留存源端已删除的归档类需求，硬改造反而会带来更高的维护成本。

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