Thanks for your response. I can confirm that you can't use SYSDATE
when creating a MV (at least in 10g). I ended up using the following
create statement:
CREATE MATERIALIZED VIEW mv_sales
PARALLEL PARTITION BY RANGE(sales_date)
(PARTITION sales_mv_20050803 VALUES LESS
THAN(TO_DATE('20050803','YYYYMMDD')),
...
PARTITION sales_mv_20050818 VALUES LESS
THAN(TO_DATE('20050818','YYYYMMDD')))
BUILD IMMEDIATE
REFRESH ON COMMIT
ENABLE QUERY REWRITE
AS
SELECT sales_date, item_id, sum(amount)
FROM sales WHERE sales_date > DATE '2005-08-03'
GROUP BY sales_date, item_id;
As long as I create a new partition every day for the new days' data in
the fact table, and drop the oldest partition, I have exactly 15 days
of data in the MV.
Received on Wed Aug 17 2005 - 23:07:48 CDT
