Computing SQL Queries with Boolean Aggregates

  • Antonio Badia
Conference paper
Part of the Lecture Notes in Computer Science book series (LNCS, volume 2737)


We introduce a new method for optimization of SQL queries with nested subqueries. The method is based on the idea of Boolean aggregates, aggregates that compute the conjunction or disjunction of a set of conditions. When combined with grouping, Boolean aggregates allow us to compute all types of non-aggregated subqueries in a uniform manner. The resulting query trees are simple and amenable to further optimization. Our approach can be combined with other optimization techniques and can be implemented with a minimum of changes in any cost-based optimizer.


Query Optimization Query Tree Aggregate Query Outer Block Single Tuple 
These keywords were added by machine and not by the authors. This process is experimental and the keywords may be updated as the learning algorithm improves.


Unable to display preview. Download preview PDF.

Unable to display preview. Download preview PDF.


  1. 1.
    Cao, B., Badia, A.: Subquery Rewriting for Optimization of SQL Queries (submitted for publication)Google Scholar
  2. 2.
    Chaudhuri, S., Shim, K.: Including Group-By in Query Optimization. In: Proceedings of the 2th VLDB Conference (1994)Google Scholar
  3. 3.
    Chaudhuri, S., Shim, K.: An Overview of Cost-Based Optimization of Queries with Aggregates. Data Engineering Bulletin 18(3) (1995)Google Scholar
  4. 4.
    Cohen, S., Nutt, W., Serebrenik, A.: Algorithms for Rewriting Aggregate Queries using Views. In: Proceedings of the Design and Management of Data Warehouses Conference (1999)Google Scholar
  5. 5.
    Dayal, U.: Of Nests and Trees: A Unified Approach to Processing Queries That Contain Nested Subqueries, Aggregates, and Quantifiers. In: Proceedings of the VLDB Conference (1987)Google Scholar
  6. 6.
    Galindo-Legaria, C., Rosenthal, A.: Outerjoin Simplification and Reordering for Query Optimization. ACM TODS 22(1) (1997)Google Scholar
  7. 7.
    Ganski, R., Wong, H.: Optimization of Nested SQL Queries Revisited. In: Proceedings of the ACM SIGMOD Conference (1987)Google Scholar
  8. 8.
    Goel, P., Iyer, B.: SQL Query Optimization: Reordering for a General Class of Queries. In: Proceedings of the 1996 ACM SIGMOD Conference (1996)Google Scholar
  9. 9.
    Gupta, A., Harinayaran, V., Quass, D.: Aggregate-Query Processing in Data Warehousing Environments. In: Proceedings of the VLDB Conference (1995)Google Scholar
  10. 10.
    Kim, W.: On Optimizing an SQL-Like Nested Query. ACM Transactions On Database Systems 7(3) (September 1982)Google Scholar
  11. 11.
    Gupta, A., Mumick, I.S. (eds.): Materialized Views: Techniques, Implementations and Applications. MIT Press, Cambridge (1999)Google Scholar
  12. 12.
    Melton, J.: Advanced SQL: 1999, Understanding Object-Relational and Other Advanced Features. Morgan Kaufmann, San Francisco (2003)Google Scholar
  13. 13.
    Muralikrishna, M.: Improving Unnesting Algorithms for Join Aggregate Queries in SQL. In: Proceedings of the VLDB Conference (1992)Google Scholar
  14. 14.
    Ross, K., Rao, J.: Reusing Invariants: A New Strategy for Correlated Queries. In: Proceedings of the ACM SIGMOD Conference (1998)Google Scholar
  15. 15.
    Ross, K., Chatziantoniou, D.: Groupwise Processing of Relational Queries. In: Proceedings of the 23rd VLDB Conference (1997)Google Scholar
  16. 16.
    Rao, J., Lindsay, B., Lohman, G., Pirahesh, H., Simmen, D.: Using EELs, a Practical Approach to Outerjoin and Antijoin Reordering. In: Proceedings of ICDE 2001 (2001)Google Scholar
  17. 17.
    Seshadri, P., Pirahesh, H., Cliff Leung, T.Y.: Complex Query Decorrelation. In: Proceedings of ICDE 1996, pp. 450–458 (1996)Google Scholar
  18. 18.
    Seshadri, P., Hellerstein, J.M., Pirahesh, H., Cliff Leung, T.Y., Ramakrishnan, R., Srivastava, D., Stuckey, P.J., Sudarshan, S.: Cost-Based Optimization for Magic: Algebra and Implementation. In: Proceedings of the SIGMOD Conference, pp. 435–446 (1996)Google Scholar
  19. 19.
    Mumick, I.S., Pirahesh, H.: Implementation of Magic-sets in a Relational Database System. In: Proceedings of the SIGMOD Conference, pp. 103–114 (1994)Google Scholar
  20. 20.
    Mumick, I.S., Finkelstein, S.J., Pirahesh, H., Ramakrishnan, R.: Magic is Relevant. In: Proceedings of the SIGMOD Conference, pp. 247–258 (1990)Google Scholar

Copyright information

© Springer-Verlag Berlin Heidelberg 2003

Authors and Affiliations

  • Antonio Badia
    • 1
  1. 1.Computer Engineering and Computer Science DepartmentUniversity of Louisville 

Personalised recommendations