Optimizing queries using materialized views

Abstract

Materialized views can provide massive improvements in query processing time, especially for aggregation queries over large tables. To realize this potential, the query optimizer must know how and when to exploit materialized views. This paper presents a fast and scalable algorithm for determining whether part or all of a query can be computed from materialized views and describes how it can be incorporated in transformation-based optimizers. The current version handles views composed of selections, joins and a final group-by. Optimization remains fully cost based, that is, a single "best" rewrite is not selected by heuristic rules but multiple rewrites are generated and the optimizer chooses the best alternative in the normal way. Experimental results based on an implementation in Microsoft SQL Server show outstanding performance and scalability. Optimization time increases slowly with the number of views but remains low even up to a thousand. KeywordsMaterialized views, view matching, query optimization.

A program optimization for automatic database result caching

“…There is a conceptual connection to the classic idea of materialized views in databases (Larson and Yang 1985), where extra logical tables are maintained as caches of the results of particular queries over primary tables. Much past work has studied how to use materialized views to answer queries efficiently (Goldstein and Larson 2001) and how to update materialized views as the underlying tables are modified (Blakeley et al 1986). Here views are generally used to cache the direct results of SQL queries, rather than full application outputs that combine query results with computation in a Turing-complete language.…”

Enabling Fine-Grained HTTP Caching of SPARQL Query Results

“…Goldstein and Larson [7] show the potential of materializing views within a relational system to dramatically improve performance of expensive queries. Both Amiri, et al [8] and Larson, Goldstein, and Zhou [9] addresses caching relational query results using materialized views.…”

Form-based proxy caching for database-backed web sites: keywords and functions

“…The median length (in words) of the queries was 3 and the average length was 3.3. The 1-word queries, 2-word queries, …, and 5-word queries made up 13,25,24,15, and 11% of the trace, respectively (Table 8).…”

“…Our work is also related to answering queries using views [15,17,21,23,33,40]. Although table-valued functions can be regarded as a kind of parameterized views, they usually have non-SQL application-specific semantics (as an evidence, they are often implemented in programming languages other than SQL).…”