Selecting Materialized Views - Physical Database Design - page 80

There is another approach to maintaining materialized views that can further
improve the gain in many cases. Instead of computing the materialized views from
scratch each night, they can be maintained incrementally. For example, in the case of
the Profit_by_Invoice_Date table, we could keep the existing data, and just add a new
row whenever a new invoice cycle completes. Let's finish up the current train of
thought, and we'll return the idea of incremental updates in Section 5.4.
The profitability by customer query is very similar to the profitability by invoice
date. This query is run primarily by the sales department. We determine that the query
is run on average three times per day. Here's the query on the base tables:

SELECT i.customer_id, sum(c.cost) AS cost, sum(li.sell)
AS sell,
sum(li.sell) - sum(c.cost) AS profit
FROM Invoice AS i, Job_Costing AS c, Line_Item AS li
WHERE i.invoice_id = li.invoice_id
AND c.job_id = li.job_id
GROUP BY i. customer_id;

Notice the tables and the joins are the same as those used in the profitability by
invoice date query. Since the same tables are read, and the same joins are performed, the
query I/O time is also unchanged.

Query I/O time (shared disk)
= scan Line_Item + scan Invoice + scan Job_Costing
= 84,008

× 5.8 ms = 487 seconds.

Figure 5.4 Profit_by_Customer materialized view.

Figure 5.4 shows the corresponding materialized view. The I/O cost to create the
Profit_by_Customer table is equal to the cost to query the base tables plus the cost to
write the result to disk.

× ≈

Query I/O time (dedicated disk) = 84,008 2.2 ms 185 seconds.