Re: rolling Materialized Views

joel.hockey_at_gmail.com wrote:
> 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.

The answer should be easy to find in the docs; without checking I'm pretty sure the answer is "no".

[...]
>
> 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?
>

I don't think it's possible to do this and maintain query rewrite capability.

You can explicitly reference individual table partitions in the FROM clause of a query, so perhaps an MV based on a UNION query of the last three partitions of the fact table would work (it would have to be re-created every week, and the MV would have to be queried directly, no re-write). Or, build the MV on the entire partitioned fact table as described in the doc (relatively easy to maintain) and then just use the predicate in your ultimate query(s) to eliminate all but the most recent three weeks - query rewrite should work.

-Mark Bole Received on Wed Aug 17 2005 - 09:41:11 CDT