如何在快速刷新的Materialized View中设置日期条件并替换SYSDATE？

好问题！确实，Oracle的快速刷新物化视图对查询中的表达式有严格限制，像SYSDATE这种每次执行结果都变化的非确定性函数，会破坏快速刷新依赖的“可重复计算”规则，导致无法使用REFRESH FAST选项。不过有几种实用的方案能绕开这个限制，同时满足你获取当前日期作为过滤条件的需求：

方案1：使用日期辅助表（最通用） #

核心思路是把动态的SYSDATE-1转换成静态的表字段值，让物化视图的查询变成确定性的，然后在每次刷新前更新这个字段。

步骤1：创建日期辅助表 #

CREATE TABLE date_filter (filter_date DATE);
-- 初始化基准日期
INSERT INTO date_filter VALUES (SYSDATE - 1);
COMMIT;

步骤2：创建支持快速刷新的物化视图 #

现在查询里用辅助表的字段替代SYSDATE-1，确保查询是可重复计算的：

CREATE MATERIALIZED VIEW mview_load
BUILD IMMEDIATE
REFRESH FAST
ON DEMAND
DISABLE QUERY REWRITE
AS
    SELECT l.ID, l.NUMBER_SHIPMENT, l.NUMBER_STOP, l.CREATE_DATE, l.UPDATE_DATE
    FROM LOAD l
    JOIN date_filter df ON l.CREATE_DATE >= df.filter_date;

注意：别忘了给LOAD表创建对应的物化视图日志，否则快速刷新还是会失败：

CREATE MATERIALIZED VIEW LOG ON LOAD WITH PRIMARY KEY, ROWID (ID, NUMBER_SHIPMENT, NUMBER_STOP, CREATE_DATE, UPDATE_DATE) INCLUDING NEW VALUES;

步骤3：刷新时更新基准日期 #

每次刷新物化视图前，先更新辅助表的日期，再执行刷新：

-- 更新过滤日期为当前日期减1
UPDATE date_filter SET filter_date = SYSDATE - 1;
COMMIT;
-- 执行快速刷新
DBMS_MVIEW.REFRESH('MVIEW_LOAD', 'F');

方案2：分区表+分区交换（适合分区场景） #

如果你的LOAD表是按CREATE_DATE做范围分区的，比如每天一个分区，那么可以直接把最近一天的分区交换到物化视图中。这种方式性能极高，而且不需要依赖物化视图日志：

1. 先创建一个和LOAD表结构一致的临时表，用来接收目标分区的数据
2. 把LOAD表中CREATE_DATE >= SYSDATE-1对应的分区交换到临时表
3. 把临时表的数据插入或交换到物化视图中

这种方案适合数据量较大的场景，避免了全量刷新的开销。

方案3：基于刷新时间过滤（业务适配场景） #

如果业务允许，可以把过滤条件改成基于物化视图的上次刷新时间，而不是固定的“最近一天”。比如：

CREATE MATERIALIZED VIEW mview_load
BUILD IMMEDIATE
REFRESH FAST
ON DEMAND
DISABLE QUERY REWRITE
AS
    SELECT l.ID, l.NUMBER_SHIPMENT, l.NUMBER_STOP, l.CREATE_DATE, l.UPDATE_DATE
    FROM LOAD l
    WHERE l.CREATE_DATE >= (SELECT LAST_REFRESH_DATE FROM USER_MVIEWS WHERE MVIEW_NAME = 'MVIEW_LOAD');

不过这种方式需要第一次初始化时手动设置初始的过滤日期，而且刷新逻辑需要确保LAST_REFRESH_DATE是正确的。

为什么原写法不行？ #

快速刷新的核心是Oracle通过物化视图日志追踪源表的变化，然后只刷新变化的数据。而SYSDATE这种非确定性函数，每次计算结果都不同，Oracle无法确定哪些数据应该被包含在物化视图中，因此会直接禁止在快速刷新的物化视图查询中使用这类函数。

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