Foto Afrati, Rada Chirkova, Shalu Gupta, and Charles Loftis: "Designing and Using Views to Improve Performance of Aggregate Queries", technical report NCSU CSC TR-2004-26, September 9, 2004.

Abstract

Data-intensive systems routinely use derived data, such as indexes or materialized views, to improve query-evaluation performance. In this context, the problem of designing derived data is as follows: Given a set of queries and a database, return definitions of derived data that, when materialized in the database, would reduce the evaluation costs of the queries. Designing materialized views and indexes is an important part of automated query-performance tuning in data-management systems that experience changes over time, where a system addresses the performance requirements of current frequent and important queries by periodically reconsidering and rematerializing the stored derived data.

In this paper we present an extensible system architecture for Query-Performance Enhancement by Tuning (QPET). QPET combines design and use of derived data in an end-to-end approach to automated query-performance tuning, and selects appropriate data-design algorithms depending on the characteristics of the prevalent queries. Our focus in automated query-performance tuning is on a tradeoff between the amount of system resources spent on designing derived data and the degree of the resulting improvement in query performance. We present algorithms and experimental results in designing and using materialized views for practically important classes of aggregate queries, including range-aggregate queries on star-schema data warehouses.