Skip to main content
SpringerLink
Log in

Optimization of Nested SQL Queries by Tableau Equivalence

  • Conference paper
  • First Online:
Research Issues in Structured and Semistructured Database Programming (DBPL 1999)

Part of the book series: Lecture Notes in Computer Science ((LNCS,volume 1949))

Included in the following conference series:

  • 294 Accesses

Abstract

We present a new optimization method for nested SQL query blocks with aggregation operators. The method is derived from the the- ory of dependency implication and tableau minimization. It unifies and generalizes previously proposed (seemingly unrelated) algorithms, and can incorporate general database dependencies given in the database schema.

We apply our method to query blocks with max, min aggregation op- erators. We obtain an algorithm which does not infer arithmetical or aggregation constraints, and reduces optimization of such query blocks to the well-studied problem of tableau minimization. We prove a com- pleteness result for this algorithm: if two max, min blocks can be merged, the algorithm will detect this fact.

This is a preview of subscription content, log in via an institution to check access.

Access this chapter

Log in via an institution
Chapter
USD 29.95
Price excludes VAT (USA)
  • Available as PDF
  • Read on any device
  • Instant download
  • Own it forever
eBook
USD 39.99
Price excludes VAT (USA)
  • Available as PDF
  • Read on any device
  • Instant download
  • Own it forever
Softcover Book
USD 54.99
Price excludes VAT (USA)
  • Compact, lightweight edition
  • Dispatched in 3 to 5 business days
  • Free shipping worldwide - see info

Tax calculation will be finalised at checkout

Purchases are for personal use only

Institutional subscriptions

Preview

Unable to display preview. Download preview PDF.

Unable to display preview. Download preview PDF.

References

  1. S. Abiteboul, R. Hull, V. Vianu. Foundations of databases. Addison-Wesley, 1995.

    Google Scholar 

  2. A. Aho, Y. Sagiv, J. Ullman. Efficient optimization of a class of relational expressions. ACM TODS 4(4), 1979.

    Google Scholar 

  3. A. Aho, Y. Sagiv, J. Ullman. Equivalence of relational expressions. SIAM J. on Computing 8(2), 1979.

    Google Scholar 

  4. A. Chandra, P. Merlin. Optimal implementation of conjunctive queries in relational databases. In STOC 1977.

    Google Scholar 

  5. S. Chaudhuri, M. Vardi. Optimization of real conjunctive queries. In PODS 1993.

    Google Scholar 

  6. C. Chekuri, A. Rajaraman. Conjunctive query containment revisited. In ICDT 1997.

    Google Scholar 

  7. S. S. Cosmadakis. Database theory and cylindric lattices. In IEEE Conf. on Foundations of Computer Science 1987.

    Google Scholar 

  8. U. Dayal. Of nests and trees: a unified approach to processing queries that contain nested subqueries, aggregates, and quantifiers. In VLDB 1987.

    Google Scholar 

  9. P. Dublish, J. Biskup, Y. Sagiv. Optimization of a subclass of conjunctive queries. In ICDT 1990.

    Google Scholar 

  10. R. Fagin. Horn clauses and database dependencies. JACM 29(4), 1982.

    Google Scholar 

  11. R. Ganski, H. Wong. Optimization of nested SQL queries revisited. In SIGMOD 1987.

    Google Scholar 

  12. V. Harinarayan, A. Gupta. Generalized projections: a powerful query-optimization technique. Stanford University CS-TN-94-14, 1994.

    Google Scholar 

  13. T. Imielinski, W. Lipski. The relational model of data and cylindrical algebras. JCSS 28(1), 1984.

    Google Scholar 

  14. E. Ioannidis, R. Ramakrishnan. Containment of conjunctive queries: beyond relations as sets. ACM TODS 20(3), 1995.

    Google Scholar 

  15. D. Johnson, A. Klug. Testing containment of conjunctive queries under functional and inclusion dependencies. JCSS 28, 1984.

    Google Scholar 

  16. M. Jarke, J. Koch. Query optimization in database systems. ACM Computing Surveys 16(2), 1984.

    Google Scholar 

  17. W. Kim. On optimizing an SQL-like nested query. ACM TODS 7(3), 1982.

    Google Scholar 

  18. A. Klug. Equivalence of relational algebra and relational calculus query languages having aggregate functions. JACM 29(3), 1982.

    Google Scholar 

  19. A. Levy, I. Mumick, Y. Sagiv. Query optimization by predicate move-around. In VLDB 1994.

    Google Scholar 

  20. I. Mumick, S. Finkelstein, H. Pirahesh, R. Ramakrishnan. Magic is relevant. In SIGMOD 1990.

    Google Scholar 

  21. I. Mumick, S. Finkelstein, H. Pirahesh, R. Ramakrishnan. Magic conditions. In PODS 1990.

    Google Scholar 

  22. I. Mumick, H. Pirahesh, R. Ramakrishnan. The magic of duplicates and aggregates. In VLDB 1990.

    Google Scholar 

  23. J. Melton, A. Simon. Understanding the new SQL: a complete guide. Morgan Kaufmann, 1993.

    Google Scholar 

  24. M. Muralikrishna. Improved unnesting algorithms for join aggregate SQL queries. In VLDB 1992.

    Google Scholar 

  25. W. Nutt, Y. Sagiv, S. Shurin. Deciding equivalences among aggregate queries. In PODS 1998.

    Google Scholar 

  26. H. Pirahesh, J. Hellerstein, W. Hasan. Extensible/rule based query rewrite optimization in Starburst. In SIGMOD 1992.

    Google Scholar 

  27. J. Rao, K. A. Ross. Reusing invariants: a new strategy for correlated queries. In SIGMOD 1998.

    Google Scholar 

  28. P. Selinger, M. Astrahan, D. Chamberlin, R. Lorie, T. Price. Access path selection in a relational database management system. In SIGMOD 1979.

    Google Scholar 

  29. P. Seshadri et al. Cost based optimization for magic: algebra and implementation. In SIGMOD 1996.

    Google Scholar 

  30. P. Seshadri, H. Pirahesh, T. Y. C. Leung. Complex Query Decorrelation. In ICDE 1996.

    Google Scholar 

  31. D. Srivastava, K. Ross, P. Stuckey, S. Sudarshan. Foundations of Aggregation Constraints. In PPCP 1994.

    Google Scholar 

  32. J. D. Ullman. Database and Knowledge-Base Systems, Vols I and II. Computer Science Press, 1989.

    Google Scholar 

  33. W. Yan, P. Larson. Performing Group-By before Join. In ICDE 1994.

    Google Scholar 

  34. M. Yannakakis, C. Papadimitriou. Algebraic dependencies. JCSS 25(2), 1982.

    Google Scholar 

Download references

Author information

Authors and Affiliations

  1. University of Patras, Patras, Greece

    Vasilis Aggelis & Stavros Cosmadakis

Authors
  1. Vasilis Aggelis
    View author publications

    You can also search for this author in PubMed Google Scholar

  2. Stavros Cosmadakis
    View author publications

    You can also search for this author in PubMed Google Scholar

Editor information

Editors and Affiliations

  1. Department of Computer Science, University of Strathclyde, G1 1XH, Glasgow, Scotland, UK

    Richard Connor

  2. Department of Computer Science, University of Toronto, 6 King’s College Road, M5S 3H5, Ontario, Toronto, Canada

    Alberto Mendelzon

Rights and permissions

Reprints and permissions

Copyright information

© 2000 Springer-Verlag Berlin Heidelberg

About this paper

Cite this paper

Aggelis, V., Cosmadakis, S. (2000). Optimization of Nested SQL Queries by Tableau Equivalence. In: Connor, R., Mendelzon, A. (eds) Research Issues in Structured and Semistructured Database Programming. DBPL 1999. Lecture Notes in Computer Science, vol 1949. Springer, Berlin, Heidelberg. https://doi.org/10.1007/3-540-44543-9_3

Download citation

  • DOI: https://doi.org/10.1007/3-540-44543-9_3

  • Published:

  • Publisher Name: Springer, Berlin, Heidelberg

  • Print ISBN: 978-3-540-41481-0

  • Online ISBN: 978-3-540-44543-2

  • eBook Packages: Springer Book Archive

Publish with us

Policies and ethics

Search

Navigation