Re: rolling Materialized Views

Hi,

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

This depends on database version. In 8i no limitations inside where are seen. For sysdate especially you must check the docs. If SYSDATE is not supported, you could always drop the MAV daily an recreate with date explicit set. this takes approx 2 sek if using prebuild tables. that way you must drop the mav anyway to update the data.
>
> 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.
In contrast to weeks used in fact table the summary should use days. 15 partitions are not that much (we have many thousands in one table).
>
> 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?

The data is not populated by statement above. Neither the table is created. All the given statement does is setting up the logical connection of mav and table.

There is also no check, wheter data in prebuild table is result of the given select. so you can populate prebuild table with an other select as given in the query. but take care, if populating only part of the data in the mav the main drawback is oracle not beeing aware of the incompleteness. You are responsible for correct data if using prebuild tables.
You must be very careful on any query, wherer the "incomplete" mav is beeing used.
this should be better in 9i, but i was yet not able to try this out.
>
> Thanks for your help,
>
> Joel
>

hth

Matthias Received on Wed Aug 17 2005 - 16:00:02 CDT