rolling Materialized Views

Hello,

I am currently working on a data warehouse project with a fact table that will hold many millions of records over a rolling 6 month period. The fact table is partitioned by week.

I would like to create a materialized view that will give summary information, but only for the most recent 15 days. Does anyone have any thoughts on the best way to do this.

Is it possible to create a MV using SYSDATE within the WHERE clause. E.g.
CREATE MATERIALIZED VIEW total_daily_sales_mv BUILD DEFERRED
REFRESH FAST ON DEMAND
ENABLE QUERY REWRITE AS
SELECT sales_date, item_id, sum(amount)
FROM sales
WHERE sales_date >= SYSDATE - 15
GROUP BY sales_date, item_id;

If it's not possible to use SYSDATE within the query, I have read in the Oracle Data Warehousing Guide that it is possible to create rolling MVs by using partitions and dropping old partitions. If I used this approach, I could let the MV use the same partitioning strategy as the fact table, and trim old partitions from the MV.

The create statement would be:
CREATE MATERIALIZED VIEW total_daily_sales_mv ON PREBUILT TABLE
ENABLE QUERY REWRITE AS
SELECT sales_date, item_id, sum(amount)
FROM sales
GROUP BY sales_date, item_id;

Is it possible to specify that the MV should only be on the last 3 partitions (3 weeks of data), not the entire table when initially creating the MV?

Thanks for your help,

Joel Received on Wed Aug 17 2005 - 02:44:49 CDT