Reasoning with aggregation constraints

  • Alon Y. Levy
  • Inderpal Singh Mumick
Database Design
Part of the Lecture Notes in Computer Science book series (LNCS, volume 1057)

Abstract

Aggregation queries are becoming increasingly common as databases continue to grow and provide parallel execution engines to enable complex queries over larger and larger amounts of data. Consequently, optimization of aggregation queries is becoming very important. In this paper we present a framework for reasoning with constraints arising from the use of aggregations. The framework introduces a constraint language, three types of inference rules to derive constraints that must hold given a set of aggregations and constraints in the query, and a sound and tractable inference procedure. The constraint language and inference procedure can be used by any system that deals with aggregations — be it constraint programming, databases, or global information systems. However, the prime application of aggregation reasoning is in database query optimizers to optimize SQL (or object-SQL) queries with grouping and aggregation. Our framework allows aggregation reasoning to be incorporated into an optimizer in a modular fashion, and we illustrate this through a detailed example.

Preview

Unable to display preview. Download preview PDF.

Unable to display preview. Download preview PDF.

References

  1. [BK95]
    A. Brodsky and Y. Kornatzky. The lyric language: Querying constraint objects. In Proceedings of ACM SIGMOD 1995 International Conference on Management of Data, San Jose, CA, May 23–25 1995.Google Scholar
  2. [CS94]
    Surajit Chaudhuri and Kyuseok Shim. Including groupby in query optimization. In Proceedings of VLDB-94, pages 354–366.Google Scholar
  3. [DJLS95]
    Shaul Dar, H. V. Jagadish, Alon Y. Levy and Divesh Srivastava. Answering SQL Queries with Aggregation Using Materialized Views. Working notes of the Post-ILPS95 Workshop on Constraints, Databases and Logic Programming.Google Scholar
  4. [Day87]
    Umeshwar Dayal. Of nests and trees: A unified approach to processing queries that contain nested subqueries, aggregates, and quantifiers. In Proceedings of the Thirteenth International Conference on Very Large Databases (VLDB), pages 197–208, Brighton, England, September 1–4 1987.Google Scholar
  5. [GM93]
    Goetz Graefe and William J. McKenna. The volcano optimizer generator: Extensibility and efficient search. In Proceedings of the Ninth IEEE International Conference on Data Engineering, Vienna, Austria, April 1993.Google Scholar
  6. [GW87]
    Richard A. Ganski and Harry K. T. Wong. Optimization of nested SQL queries revisited. In Proceedings of ACM SIGMOD 1987 International Conference on Management of Data, pages 23–33, San Francisco, CA, May 1987.Google Scholar
  7. [GHQ95]
    A. Gupta, V. Harinarayan and D. Quass. Generalized Projections: A Powerful Approach to Aggregation. In Proceedings of VLDB-95.Google Scholar
  8. [Hel94]
    Joseph M. Hellerstein. Practical predicate placement. In Proceedings of SIGMOD-94.Google Scholar
  9. [HS93]
    Joseph M. Hellerstein and Michael Stonebraker. Predicate migration: Optimizing queries with expensive predicates. In Proceedings of ACM SIG-MOD 1993 International Conference on Management of Data, pages 267–276, Washington, DC., May 26–28 1993.Google Scholar
  10. [Kim82]
    Won Kim. On optimizing an SQL-like nested query. ACM Transactions on Database Systems, 7(3), September 1982.Google Scholar
  11. [KKR90]
    Paris C. Kanellakis, Gabriel M. Kuper, and Peter Z. Revesz. Constraint query languages. In Proceedings of the Ninth Symposium on Principles of Database Systems (PODS), pages 299–313, Nashville, TN, April 2–4 1990.Google Scholar
  12. [LMS94]
    Alon Levy, Inderpal Singh Mumick, and Yehoshua Sagiv. Query optimization by predicate movearound. In Proceedings of VLDB-94, pages 96–107.Google Scholar
  13. [LS92]
    Alon Levy and Yehoshua Sagiv. Constraints and redundancy in datalog. In Proceedings of the Eleventh Symposium on Principles of Database Systems (PODS), pages 67–80, San Diego, CA, June 2–4 1992.Google Scholar
  14. [LSK95]
    Alon Y. Levy, Divesh Srivastava, and Thomas Kirk. Data model and query evaluation in global information systems. Journal of Intelligent Information Systems, 5(2), September, 1995.Google Scholar
  15. [MFPR90]
    Inderpal Singh Mumick, Sheldon J. Finkelstein, Hamid Pirahesh, and Raghu Ramakrishnan. Magic is relevant. In Proceedings of ACM SIGMOD 1990 International Conference on Management of Data, pages 247–258, Atlantic City, NJ, May 23–25 1990.Google Scholar
  16. [MP94]
    Inderpal Singh Mumick and Hamid Pirahesh. Implementation of magic in starburst. In Proceedings of SIGMOD-94.Google Scholar
  17. [MS95]
    Inderpal Singh Mumick and Oded Shmueli. How expressive is stratified aggregation. To Appear in Annals of Mathematics and Artificial Intelligence, 1995.Google Scholar
  18. [Mur92]
    M. Muralikrishna. Improved unnesting algorithms for join aggregate SQL queries. In Proceedings of the Eighteenth International Conference on Very Large Databases (VLDB), pages 91–102, Vancouver, Canada, August 23–27 1992.Google Scholar
  19. [PHH92]
    Hamid Pirahesh, Joseph M. Hellerstein, and Waqar Hasan. Extensible/rule based query rewrite optimization in Starburst. In Proceedings of ACM SIG-MOD 1992 International Conference on Management of Data, pages 39–48, San Diego, CA, June 2–5 1992.Google Scholar
  20. [RSSS94]
    Kenneth Ross, Divesh Srivastava, Peter Stuckey, and S. Sudarshan. Foundations of aggregation constraints. In Alan Borning, editor, Principles and Practice of Constraint Programming, 1994. LNCS 874.Google Scholar
  21. [Ull89]
    Jeffrey D. Ullman. Principles of Database and Knowledge-Base Systems, Volumes 1 and 2. Computer Science Press, 1989.Google Scholar
  22. [vdM92]
    Ronald van der Meyden. The Complexity of Querying Indefinite Information: Defined Relations, Recursion, and Linear Order. PhD thesis, Rutgers, The State University of New Jersey, New Brunswick, NJ, October 1992.Google Scholar
  23. [YL95]
    Weipeng P. Yan and Per-Åke Larson. Eager Aggregation and Lazy Aggregation. In Proceedings of VLDB-95.Google Scholar

Copyright information

© Springer-Verlag 1996

Authors and Affiliations

  • Alon Y. Levy
    • 1
  • Inderpal Singh Mumick
    • 1
  1. 1.AT&T Bell LaboratoriesMurray HillUSA

Personalised recommendations