View Materialization

In Section 6.4.3 we discussed one approach to handling queries based on a view, where
the query is modified into a query on the underlying base tables. One disadvantage with
this approach is the time taken to perform the view resolution, particularly if the view
is accessed frequently. An alternative approach, called view materialization, is to store
the view as a temporary table in the database when the view is first queried. Thereafter,
queries based on the materialized view can be much faster than recomputing the
view each time. The speed difference may be critical in applications where the query
rate is high and the views are complex so that it is not practical to recompute the view for
every query.
Materialized views are useful in new applications such as data warehousing, replication
servers, data visualization, and mobile systems. Integrity constraint checking and query
optimization can also benefit from materialized views. The difficulty with this approach
is maintaining the currency of the view while the base table(s) are being updated. The
process of updating a materialized view in response to changes to the underlying data is
called view maintenance. The basic aim of view maintenance is to apply only those
changes necessary to the view to keep it current. As an indication of the issues involved,
consider the following view:
CREATE VIEW StaffPropRent (staffNo)
AS SELECT DISTINCT staffNo
FROM PropertyForRent
WHERE branchNo = ‘B003’ AND rent > 400;
with the data shown in Table 6.8. If we were to insert a row into the PropertyForRent table
with a rent . 400, then the view would be unchanged. If we were to insert the row (ePG24f,
. . . , 550, eCO40f, eSG19f, eB003f) into the PropertyForRent table then the row should also
appear within the materialized view. However, if we were to insert the row (ePG54f, . . . ,
450, eCO89f, eSG37f, eB003f) into the PropertyForRent table, then no new row need be
added to the materialized view because there is a row for SG37 already. Note that in these
three cases the decision whether to insert the row into the materialized view can be made
without access to the underlying PropertyForRent table.
If we now wished to delete the new row (ePG24f, . . . , 550, eCO40f, eSG19f, eB003f)
from the PropertyForRent table then the row should also be deleted from the materialized
view. However, if we wished to delete the new row (ePG54f, . . . , 450, eCO89f, eSG37f,
eB003f) from the PropertyForRent table then the row corresponding to SG37 should not
be deleted from the materialized view, owing to the existence of the underlying base
row corresponding to property PG21. In these two cases, the decision on whether to delete
or retain the row in the materialized view requires access to the underlying base table
PropertyForRent. For a more complete discussion of materialized views, the interested
reader is referred to Gupta and Mumick (1999).