Databases Reference
In-Depth Information
ing these large tables can consume significant resources. We will focus on the queries
that occur frequently, and also heavily consume disk input/output (I/O). If we have the
result of such a query, then why not save the result to disk, and just read the answer the
next time the query is posed? Materialized views are precalculated results stored on disk.
Quick query response is the goal.
There are two ways that materialized views can be accessed. The first is the brute
force method where the SQL is written to explictly access the view. This method limits
the choices for the query optimizer in selecting other potentially superior query execu-
tion plans perhaps using some of the base tables on which the materialized view is
based. Another issue is that it places a burden on the application designer to be aware of
the available materialized views and exploit them. In fact for many enterprise scale data-
bases the people designing the database application may not be the same people who
perform the database design—making it hard or impossible for the application to
explicitly access the materialized view. The second method for accessing materialized
views is for the decision to be made by the query compiler during query optimizaton
(when the query execution plan is selected). This method is known as “materialized
view routing” or “automatic materialized view routing.” In this case the query is written
exclusively against the base tables (without reference to any materialzed views), and the
query compiler makes a choice to access an available materialized view if it determines a
superior query execution plan will result. Materialized view routing is far more usable
than hand crafting access to each materialized view. However, automatic routing of this
sort depends heavily on the sophistication of the query compiler to a) recognize a mate-
rialized view can be used in the query execution plan, and b) to determine correctly that
such a substitution is beneficial. Both requirements turn out in practice to be highly
complex choices for all but the most simple cases (such as the trivial case where the
materialized view exactly matches an incoming query). As a result, materialized view
routing is still an inexact science in modern database systems.
5.1
Simple View Materialization
The most straightforward type of materialized view contains data that exactly matches
the result of a specific query. Reading from the materialized view gives the same result as
calculating the result from the base tables. We illustrate with an example, and demon-
strate the query I/O savings.
Figure 5.1 is a portion of a schema from a normalized database. We refer to these
base tables throughout the chapter, as our examples build upon each other. The corre-
sponding tables in the database instance contain data useful for the billing cycle, and job
costing at a manufacturing plant.
Each invoice has an invoice date and an associated customer. The terms of the
invoice track how many days the customer has to pay the invoice. The status indicates
the current stage of the billing cycle for the given invoice. The notations can hold any
