8.5 Materialized Views

Materialized Views

Section outline

• Introduces the concept of a materialized view, which is a view whose result is physically stored, unlike logical views.
• Contrasts the benefit of faster querying with the cost of maintaining the view when the underlying base tables change.

#1

read: Section 8.5: Materialized Views (section)

model: pro
rules: bullet definition math presentation short


Present the following.  Use definition formal definition when it applies.

- Introduces the concept of a materialized view, which is a view whose result is physically stored, unlike logical views.
- Contrasts the benefit of faster querying with the cost of maintaining the view when the underlying base tables change.

A view describes how a new relation can be constructed from base tables. While we have considered views as logical descriptions, it can be efficient to materialize them; that is, to store their value as a physical table. This approach introduces a fundamental trade-off between query performance and maintenance cost.

Definition: Materialized View

A materialized view is a database object that contains the results of a query. Unlike a logical (or virtual) view, the result set is pre-computed and stored as a physical table. The materialized view must be refreshed or maintained when the data in the underlying base tables changes.

• Benefit of Materialization: Query Performance

  Queries against a materialized view are often faster than those against a logical view. The system can access the pre-computed data directly, avoiding the cost of executing the view’s defining query, which may involve complex operations like joins and aggregations, each time the view is accessed.

• Cost of Materialization: Maintenance Overhead

  The primary drawback is the cost of maintaining the view’s consistency. When data in the underlying base tables is modified, the materialized view must also be updated. This maintenance introduces overhead to INSERT, UPDATE, and DELETE operations on the base tables. The system must recompute parts or all of the materialized view to reflect these changes.

gemini-2.5-pro

• Version: 1
• Model: gemini-2.5-pro
• Created: 2025-10-15 23:49:26
• Duration: 11.570029020309448
• Cummulative cost: $1.42e-02

Maintaining a Materialized View

Section outline

• Explains that a DBMS must update a materialized view when its base tables are modified.
• Describes how for simple views, it is possible to perform incremental updates rather than a full re-computation of the view.
• Uses an example to illustrate how to handle insertions and deletions in the base tables and reflect these changes incrementally in the materialized view.

#2

read: Section 8.5.1: Maintaining a Materialized View (section)

model: pro
rules: bullet definition math presentation short


Present the following.  Use definition formal definition when it applies.

- Explains that a DBMS must update a materialized view when its base tables are modified.
- Describes how for simple views, it is possible to perform incremental updates rather than a full re-computation of the view.
- Uses an example to illustrate how to handle insertions and deletions in the base tables and reflect these changes incrementally in the materialized view.

• In principle, a Database Management System (DBMS) must recompute a materialized view each time one of its underlying base tables is modified. This ensures the view remains consistent with the source data.

  For simple views, it is often possible to limit the scope of work required to maintain the view. Rather than a full re-computation, the view can be updated incrementally.

• Consider the following materialized view, which joins the Movies and MovieExec tables.

  CREATE MATERIALIZED VIEW MovieProd AS
  SELECT title, year, name
  FROM Movies, MovieExec
  WHERE producerC# = cert#

  Modifications to the base tables can be propagated to this view through a series of smaller, more efficient operations.

• Insertion into Movies: If a new tuple is inserted into Movies, such as ('Kill Bill', 2003, producerC#=23456), the system performs a lookup in MovieExec for the corresponding cert#. The resulting joined tuple is then inserted into MovieProd.

  -- Find the producer's name
  SELECT name FROM MovieExec WHERE cert# = 23456;
  -- Assume 'Quentin Tarantino' is returned
  INSERT INTO MovieProd VALUES('Kill Bill', 2003, 'Quentin Tarantino');

• Deletion from Movies: If a tuple is deleted from Movies, the corresponding tuple in MovieProd can be deleted directly using its key attributes.

  DELETE FROM MovieProd
  WHERE title = 'Dumb & Dumber' AND year = 1994;

• Insertion into MovieExec: If a new tuple is inserted into MovieExec, such as (name='Max Bialystock', cert#=34567), the system must find all movies produced by this executive and insert the corresponding join results into MovieProd.

  INSERT INTO MovieProd
  SELECT title, year, 'Max Bialystock'
  FROM Movies
  WHERE producerC# = 34567;

• Deletion from MovieExec: If a tuple is deleted from MovieExec, such as the one for cert# = 45678, all movies produced by that executive must be removed from MovieProd.

  DELETE FROM MovieProd
  WHERE (title, year) IN
    (SELECT title, year FROM Movies WHERE producerC# = 45678);

  This ensures that all movies associated with the now-deleted producer are removed from the view.

gemini-2.5-pro

• Version: 1
• Model: gemini-2.5-pro
• Created: 2025-10-15 23:49:42
• Duration: 15.839313983917236
• Cummulative cost: $1.90e-02

Periodic Maintenance of Materialized Views

Section outline

• Presents an alternative to immediate maintenance: reconstructing views periodically (e.g., nightly).
• Discusses the use case in OLAP systems where queries are frequent, but data modifications dominate, and slight data staleness is acceptable for analysts.
• Highlights that this approach avoids the overhead of maintaining views in real-time.

#3

read: Section 8.5.2: Periodic Maintenance of Materialized Views (section)

model: pro
rules: bullet definition math presentation short


Present the following.  Use definition formal definition when it applies.

- Presents an alternative to immediate maintenance: reconstructing views periodically (e.g., nightly).
- Discusses the use case in OLAP systems where queries are frequent, but data modifications dominate, and slight data staleness is acceptable for analysts.
- Highlights that this approach avoids the overhead of maintaining views in real-time.

Definition: Periodic Maintenance of a Materialized View

Let \(V\) be a materialized view defined by a query \(Q\) over a set of base tables \(\{R_1, R_2, \ldots, R_n\}\). Let the state of the database at time \(t\) be \(D_t\). The value of the view at time \(t\), denoted \(V_t\), is \(Q(D_t)\).

Periodic maintenance is a strategy where the stored instance of \(V\) is not updated immediately with each change to the base tables. Instead, the view is recomputed, or refreshed, at discrete points in time \(t_1, t_2, \ldots, t_k\). Between any two refresh times \(t_i\) and \(t_{i+1}\), a query against the materialized view returns the state \(V_{t_i} = Q(D_{t_i})\), even if the underlying base tables in \(D\) have changed.

• An Alternative Maintenance Strategy

  In certain contexts, maintaining a materialized view in perfect synchrony with its underlying base tables is prohibitively expensive. This is especially true when the rate of modifications to the base tables is high. An alternative is to reconstruct the view periodically, for instance, on a nightly basis when database activity is low.

• Use Case in Analytical Processing

  This strategy is common in systems that support Online Analytical Processing (OLAP). Analysts’ queries, which often involve complex aggregations over large datasets, benefit significantly from pre-computed results in a materialized view. The requirement for absolutely current data is often relaxed, and a delay of several hours is acceptable for trend analysis and reporting.

• The Cost-Freshness Trade-Off

  Periodic maintenance avoids the performance degradation that immediate view updates would impose on transactional operations. The system’s transactional throughput is preserved. The compromise is data freshness; the view is, by design, out of date. The acceptability of this trade-off depends entirely on the requirements of the application using the view.

gemini-2.5-pro

• Version: 1
• Model: gemini-2.5-pro
• Created: 2025-10-15 23:49:58
• Duration: 16.03166699409485
• Cummulative cost: $1.91e-02

Rewriting Queries to Use Materialized Views

Section outline

• Describes how a query optimizer can rewrite a query to use a materialized view, even if the view is not mentioned in the original query.
• This rewriting can significantly improve performance by utilizing pre-computed results, such as joins.
• Provides a set of conditions for rewriting a query Q to use a view V, based on the relations, conditions, and attributes in their respective FROM, WHERE, and SELECT clauses.

#4

read: Section 8.5.3: Rewriting Queries to Use Materialized Views (section)

model: pro
rules: bullet definition math presentation short


Present the following.  Use definition formal definition when it applies.

- Describes how a query optimizer can rewrite a query to use a materialized view, even if the view is not mentioned in the original query.
- This rewriting can significantly improve performance by utilizing pre-computed results, such as joins.
- Provides a set of conditions for rewriting a query `Q` to use a view `V`, based on the relations, conditions, and attributes in their respective `FROM`, `WHERE`, and `SELECT` clauses.

A query optimizer can rewrite a query to utilize a pre-existing materialized view, even if the view is not explicitly mentioned in the query. This process can significantly enhance performance by substituting a portion of the query’s logic with the pre-computed results stored in the materialized view.

The substitution is possible only under specific conditions concerning the relations, attributes, and predicates involved in both the query and the view definition.

Definition: Query Rewriting with Materialized Views

Let a materialized view \(V\) be defined by the query:

\[ \text{SELECT } L_V \text{ FROM } R_V \text{ WHERE } C_V \]

Let a query \(Q\) be of the form:

\[ \text{SELECT } L_Q \text{ FROM } R_Q \text{ WHERE } C_Q \]

The query \(Q\) can be rewritten to use the view \(V\) if the following conditions are met:

1. The set of relations in the view’s FROM clause, \(R_V\), is a subset of the relations in the query’s FROM clause, \(R_Q\).
2. The query’s WHERE clause, \(C_Q\), is logically equivalent to the conjunction of the view’s WHERE clause, \(C_V\), and some other condition, \(C\). That is, \(C_Q \equiv C_V \land C\).
3. All attributes from relations in \(R_V\) that are mentioned in the condition \(C\) must also appear in the view’s SELECT list, \(L_V\).
4. All attributes from relations in \(R_V\) that appear in the query’s SELECT list, \(L_Q\), must also appear in the view’s SELECT list, \(L_V\).

If these conditions hold, the query \(Q\) can be rewritten as follows:

• The Rewritten FROM Clause

  The list of relations \(R_Q\) is replaced by the view \(V\) and the relations in \(R_Q\) that are not in \(R_V\). The new FROM clause is \(\{V\} \cup (R_Q \setminus R_V)\).

• The Rewritten WHERE Clause

  The condition \(C_Q\) is replaced by the residual condition \(C\). If \(C\) is empty (i.e., \(C_Q \equiv C_V\)), then this part of the WHERE clause is removed.

gemini-2.5-pro

• Version: 1
• Model: gemini-2.5-pro
• Created: 2025-10-15 23:50:14
• Duration: 15.748723983764648
• Cummulative cost: $2.00e-02

Automatic Creation of Materialized Views

Section outline

• Extends the concept of automatic index selection to materialized views.
• Notes the difficulty of this task due to the vast number of potential views that could be materialized.
• Suggests strategies to limit candidate views, such as considering only those that can help queries in a given workload.
• Proposes that the ‘benefit’ of a view should consider both the performance improvement and the storage space it consumes.

#5

read: Section 8.5.4: Automatic Creation of Materialized Views (section)

model: pro
rules: bullet definition math presentation short


Present the following.  Use definition formal definition when it applies.

- Extends the concept of automatic index selection to materialized views.
- Notes the difficulty of this task due to the vast number of potential views that could be materialized.
- Suggests strategies to limit candidate views, such as considering only those that can help queries in a given workload.
- Proposes that the 'benefit' of a view should consider both the performance improvement and the storage space it consumes.

The principles guiding the automatic selection of indexes can be extended to the selection of materialized views. The process involves analyzing a given query workload to identify candidate views that could accelerate query execution. However, this task is considerably more complex for views than for indexes.

• The Challenge of Candidate Generation

  Unlike indexes, where the number of candidates is limited by the number of attributes and their combinations, the set of potential materialized views is effectively infinite. Any query, in principle, could be materialized. Therefore, a strategy is required to limit the search to a manageable set of promising candidates.

• Workload-Based Candidate Selection

  The primary strategy for managing this complexity is to generate candidate views based on the expected query workload. We limit consideration to views that are likely to be beneficial for one or more queries in this workload. The candidate views should meet certain criteria:

  1. The view’s FROM clause should be a subset of the relations in the FROM clause of at least one query in the workload.
  2. The view’s WHERE clause should be a conjunction (AND) of conditions that appear in the workload’s queries.
  3. The view’s SELECT list must contain attributes sufficient to be used by at least one query.

• Evaluating the Benefit of a View

  The benefit of a materialized view cannot be assessed on performance improvement alone. Materialized views, particularly those involving joins, can consume significant storage space. An index is typically much smaller than the relation it indexes, but a view can be larger than the base relations. Consequently, the benefit metric must account for this space cost. A possible definition of benefit is the improvement in average query execution time divided by the storage space occupied by the view.

  Let \(T_W\) be the total running time of the workload without the view, and \(T_{W,V}\) be the total running time with the view. Let \(S_V\) be the space required by the view. The benefit of view \(V\) may be defined as: \[ \text{Benefit}(V) = \frac{T_W - T_{W,V}}{S_V} \]

gemini-2.5-pro

• Version: 1
• Model: gemini-2.5-pro
• Created: 2025-10-15 23:50:29
• Duration: 15.218650102615356
• Cummulative cost: $1.62e-02