Databases Reference
In-Depth Information
Of course, a complete refresh would drain resources if the Profit_Fact table has
many rows. When the source table is huge, as is often the case in a data warehouse,
another more viable option is to use the fast refresh option. Fast refresh is an incremen-
tal update strategy. Basically the existing data in the materialized view is left intact,
changes to the source table are tracked, and the update process summarizes the new data
and then adjusts/creates the appropriate rows in the materialized view. If we want to use
the fast refresh option, then we need to tell Oracle to track the changes to the source
data table.
CREATE MATERIALIZED VIEW LOG ON Profit_Fact
WITH ROWID, SEQUENCE(job_id, invoice_date_id,
customer_id, cost, sell, profit)
INCLUDING NEW VALUES;
The definition for the profit by customer summary, taking advantage of the fast
refresh strategy, is as follows.
CREATE MATERIALIZED VIEW Profit_by_Customer
REFRESH FAST
START WITH TO_DATE('31-01-2007 03:00:00',
'DD-MM-YYYY HH24:MI:SS')
NEXT SYSDATE + 1
AS SELECT customer_id, sum(cost) AS cost, sum(sell) AS
sell, sum(profit) AS profit
FROM Profit_Fact
GROUP BY customer_id;
Other strategies are also possible. For example, the following definition keeps the
materialized view updated in real time, whenever there is a commit on the Profit_Fact
table.
CREATE MATERIALIZED VIEW Profit_by_Customer
REFRESH ON COMMIT
AS SELECT customer_id, sum(cost) AS cost, sum(sell) AS
sell, sum(profit) AS profit
FROM Profit_Fact
GROUP BY customer_id;
Typically, data warehouses contain huge fact tables, and the refresh processes run
during designated update windows. The “fast” option supports this strategy. If you have
a materialized view where a sizeable portion of the rows change each day, then a “com-
plete” refresh may perform faster, since bulk operations are more efficient than a large
number of incremental changes. The “on commit” option supports real-time updates. If
