Dynamic Materialized Views

TLDR

A conventional materialized view blindly materializes and maintains all rows of a view, even rows that are never accessed. The study proposes a flexible materialization strategy to reduce storage space and view maintenance costs. Dynamic materialized views selectively materialize frequently accessed rows, with control tables defining materialized rows that can be changed dynamically, and generate dynamic execution plans to determine view applicability at run time. Experimental results in Microsoft SQL Server show that, compared with conventional materialized views, dynamic materialized views greatly reduce storage requirements and maintenance costs while achieving better query performance with improved buffer pool efficiency.

Abstract

A conventional materialized view blindly materializes and maintains all rows of a view, even rows that are never accessed. We propose a more flexible materialization strategy aimed at reducing storage space and view maintenance costs. A dynamic materialized view selectively materializes only a subset of rows, for example, the most frequently accessed rows. One or more control tables are associated with the view and define which rows are currently materialized. The set of materialized rows can be changed dynamically, either manually or automatically by an internal cache manager using a feedback loop. Dynamic execution plans are generated to decide whether the view is applicable at run time. Experimental results in Microsoft SQL Server show that compared with conventional materialized views, dynamic materialized views greatly reduce storage requirements and maintenance costs while achieving better query performance with improved buffer pool efficiency.