How does the SYSDATE in Materialized View behave with respect to Materialized View's Last Refresh Date?

Question

I have this Materialized View in Oracle with a SYSDATE condition in the WHERE clause.

CREATE MATERIALIZED VIEW DISCOUNT_ELIG_CUST ( AS
SELECT customer_id,
       customer_name,
       join_date
FROM MARKET_CUSTOMERS where trunc(join_date) >= trunc(sysdate) - 7 
UNION ALL
SELECT customer_id,
       customer_name,
       join_date
FROM RETAIL_CUSTOMERS where trunc(join_date) >= trunc(sysdate) - 7;

This MV should fetch details of the customers who joined us in the last 7 days.

The Mv was last refresh on 1st June 2023 in TEST environment. Running this MV on 16th July 2023, produces records from 25th May 2023 to 1st June 2023. But the expected output as on 16th July 2023 is the records from 9th July to 16th July.

Does the sysdate in MV have any impact on the query output based on the LRD(Last Refresh Date) of the MV?

Answer 1

If I understood you correctly, then: an "ordinary" view is just a stored SELECT statement, doesn't contain any data and is executed every time you call it. For example:

create view v_employees as
select *
from employees
where hiredate > sysdate - 7

will return employees hired in last 7 days. Presuming that people frequently get hired (every day), select from v_employees would return different result set every day.

Unlike it, a materialized view contains data. Once you create it, its contents doesn't change until the next refresh. Therefore,

create materialized view mv_employees as
select *
from employees
where hiredate > sysdate - 7

ran "today" will display the same employees forever - today, tomorrow, next month, ..., until it is refreshed - then it'll contain data about employees hired during last 7 days according to refresh date (actually, a "new" sysdate).