Generalized Projections: A Powerful Approach To Aggregation

In this paper we introduce generalized projections an extension of duplicate-eliminating projections, that capture aggregations, groupbys, conventional projection with duplicate elimination (distinctand duplicate-preserving projections in a common unified framework. Using GPs we extend well known and simple algorithms for SQL queries that use distinct projections to derive algorithms for queries using aggregations like sum-max-min-count and avg. We develop powerful query rewrite rules for aggregate queries that unify and extend rewrite rules previously known in the literature. We then illustrate the power of our approach by solving a very practical and important problem in data warehousing: how to answer an aggregate query about base tables using materialized aggregate views (summary Keywords: aggregation, data warehousing, materialized views, query optimization

[1]  Kyuseok Shim,et al.  Including Group-By in Query Optimization , 1994, VLDB.

[2]  Hamid Pirahesh,et al.  Extensible query processing in starburst , 1989, SIGMOD '89.

[3]  Venky Harinarayan,et al.  Generalized Projections: A Powerful Query-Optimization Technique , 1994 .

[4]  Richard D. Hackathorn.  Data warehousing energizes your enterprise , 1995 .

[5]  Per-Ake Larson,et al.  Performing Group-By before Join , 1994, ICDE 1994.

[6]  Ashish Gupta,et al.  Optimization Using Tuple Subsumption , 1995, ICDT.

[7]  Alon Y. Halevy,et al.  Query Optimization by Predicate Move-Around , 1994, VLDB.

[8]  Per-Åke Larson,et al.  Query Transformation for PSJ-Queries , 1987, VLDB.

[9]  Anand Rajaraman,et al.  Answering queries using templates with binding patterns (extended abstract) , 1995, PODS.

[10]  Goetz Graefe,et al.  Query evaluation techniques for large databases , 1993, CSUR.

[11]  Hamid Pirahesh,et al.  Extensible/rule based query rewrite optimization in Starburst , 1992, SIGMOD '92.

[12]  Per-Åke Larson,et al.  Computing Queries from Derived Relations , 1985, VLDB.

[13]  GraefeGoetz.  Query evaluation techniques for large databases , 1993 .

[14]  Kenneth A. Ross,et al.  Foundations of Aggregation Constraints , 1994, PPCP.

[15]  Umeshwar Dayal,et al.  Of Nests and Trees: A Unified Approach to Processing Queries That Contain Nested Subqueries, Aggregates, and Quantifiers , 1987, VLDB.

[16]  Jeffrey D. Uuman.  Principles of database and knowledge- base systems , 1989 .