Abstract: We investigate the problem of using materialized views to compute answers to SQL queries with grouping and aggregation, in the presence of multiset tables. This problem is important in many applications, such as mobile computing, global information systems, and maintaining physical data independence, where access to (local or cached) materialized views may be cheaper than access to the underlying database. In addition, this problem has obvious potential in optimizing query evaluation.

The problem is formally stated as finding a rewriting of a query $Q$ where the materialized views occur in the FROM clause, and the rewritten query is multiset-equivalent to $Q$. First, we study the case where the query has grouping and aggregation but the views do not, and show that usability of a view in evaluating a query essentially requires an isomorphism between the view and a portion of the query. We present a rewriting algorithm that generates all possible rewritings of the query using the views; when using multiple views, considering the views iteratively in any order yields all possible rewritings. Second, we study the case where the query and the views both have grouping and aggregation, identify the conditions under which the aggregation information present in a view is sufficient to perform the aggregate computations required in the query, and present a rewriting algorithm. Third, we examine the case where the views have grouping and aggregation but the query does not, and show that it is not possible to use the views in this case. Fourth, we illustrate some simplifications to the usability conditions in the presence of keys, i.e., when database tables are guaranteed to be sets.