Advertisement

Efficient generation of query plans containing group-by, join, and groupjoin

  • Marius Eich
  • Pit Fender
  • Guido Moerkotte
Special Issue Paper
  • 77 Downloads

Abstract

It has been a recognized fact for many years that query execution can benefit from pushing grouping operators down in the operator tree and applying them before a join. This so-called eager aggregation reduces the size(s) of the join argument(s), making join evaluation faster. Lately, the idea enjoyed a revival when it was applied to outer joins for the first time and incorporated in a state-of-the-art plan generator. However, the recent approach is highly dependent on the use of heuristics because of the exponential growth of the search space that goes along with eager aggregation. Finding an optimal solution for larger queries calls for effective optimality-preserving pruning mechanisms to reduce the search space size as far as possible. By a more thorough investigation of functional dependencies and keys, we provide a set of new pruning criteria and extend the idea of eager aggregation further by combining it with the introduction of groupjoins. We evaluate the resulting plan generator with respect to runtime and memory consumption.

Keywords

Aggregation Join ordering Dynamic programming Query optimization 

Notes

Acknowledgements

We thank Simone Seeger for her help preparing the manuscript and the reviewers for their helpful feedback.

References

  1. 1.
    von Bültzingsloewen, G.: Optimizing SQL queries for parallel execution. SIGMOD Rec. 18, 17–22 (1989)CrossRefGoogle Scholar
  2. 2.
    Chaudhuri, S., Shim, K.: Including group-by in query optimization. In: Proceedings of International Conference on Very Large Data Bases (VLDB), vol 94, pp. 354–366 (1994)Google Scholar
  3. 3.
    Cluet, S., Moerkotte, G.: Efficient evaluation of aggregates on bulk types. In: International Workshop on Database Programming Languages (1995)Google Scholar
  4. 4.
    Cormen, T.H., Leiserson, C.E., Rivest, R.L., Stein, C.: Introduction to Algorithms, 3rd edn. The MIT Press, Cambridge (2009)zbMATHGoogle Scholar
  5. 5.
    Eich, M., Fender, P., Moerkotte, G.: Faster plan generation through consideration of functional dependencies and keys. In: Proceedings of International Conference on Very Large Data Bases (VLDB), vol 9(10), pp. 756–767 (2016)Google Scholar
  6. 6.
    Eich, M., Fender, P., Moerkotte, G.: Efficient generation of query plans containing group-by, join, and groupjoin. Technical report, University of Mannheim (2017)Google Scholar
  7. 7.
    Eich, M., Moerkotte, G.: Dynamic programming: The next step. Technical report, University of Mannheim (2014)Google Scholar
  8. 8.
    Eich, M., Moerkotte, G.: Dynamic programming: the next step. In: Proceedings of IEEE Conference on Data Engineering, pp. 903–914 (2015)Google Scholar
  9. 9.
    Galindo-Legaria, C., Joshi, M.: Orthogonal optimization of subqueries and aggregation. In: Proceedings of the ACM SIGMOD Conference on Management of Data, pp. 571–581 (2001)Google Scholar
  10. 10.
    Galindo-Legaria, C., Rosenthal, A.: Outerjoin simplification and reordering for query optimization. ACM Trans. Database Syst. 22(1), 43–74 (1997)CrossRefGoogle Scholar
  11. 11.
    Kemper, A., Neumann, T.: Hyper: A hybrid OLTP & OLAP main memory database system based on virtual memory snapshots. In: Proceedings of IEEE Conference on Data Engineering, pp. 195–206 (2011)Google Scholar
  12. 12.
    Moerkotte, G., Fender, P., Eich, M.: On the correct and complete enumeration of the core search space. In: Proceedings of the ACM SIGMOD Conference on Management of Data, pp. 493–504 (2013)Google Scholar
  13. 13.
    Moerkotte, G., Neumann, T.: Dynamic programming strikes back. In: Proceedings of the ACM SIGMOD Conference on Management of Data, pp. 539–552 (2008)Google Scholar
  14. 14.
    Moerkotte, G., Neumann, T.: Accelerating queries with group-by and join by groupjoin. In: Proceedings of International Conference on Very Large Data Bases (VLDB), vol 4(11) (2011)Google Scholar
  15. 15.
    Paulley, G.: Exploiting functional dependence in query optimization. Ph.D. thesis, University of Waterloo (2000)Google Scholar
  16. 16.
    Yan, W.: Rewriting optimization of SQL queries containing group-by. Ph.D. thesis, University of Waterloo (1995)Google Scholar
  17. 17.
    Yan, W., Larson, P.A.: Performing group-by before join. Technical Report CS 93-46, Dept. of Computer Science, University of Waterloo, Canada (1993)Google Scholar
  18. 18.
    Yan, W., Larson, P.A.: Performing group-by before join. In: Proceedings of IEEE Conference on Data Engineering, pp. 89–100 (1994)Google Scholar
  19. 19.
    Yan, W., Larson, P.A.: Eager aggregation and lazy aggregation. In: Proceedings of International Conference on Very Large Data Bases (VLDB), vol 95, pp. 345–357 (1995)Google Scholar
  20. 20.
    Yan, W., Larson, P.A.: Interchanging the order of grouping and join. Technical Report CS 95-09, Dept. of Computer Science, University of Waterloo, Canada (1995)Google Scholar

Copyright information

© Springer-Verlag GmbH Germany 2017

Authors and Affiliations

  1. 1.School of Business Informatics and MathematicsUniversity of MannheimMannheimGermany
  2. 2.Oracle LabsRedwood ShoresUSA

Personalised recommendations