Fighting Redundancy in SQL

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


Many SQL queries with aggregated subqueries exhibit redundancy (overlap in FROM and WHERE clauses). We propose a method, called the for-loop, to optimize such queries by ensuring that redundant computations are done only once. We specify a procedure to build a query plan implementing our method, give an example of its use and argue that it offers performance advantages over traditional approaches.


Correlate Attribute Base Relation Common Part Query Optimization Query Plan 
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.
    Badia, A., Niehues, M.: Optimization of sequences of relational queries in decision-support environments. In: Mohania, M., Tjoa, A.M. (eds.) DaWaK 1999. LNCS, vol. 1676, pp. 126–131. Springer, Heidelberg (1999)Google Scholar
  2. 2.
    Cao, B., Badia, A.: Subquery Rewriting for Optimization of SQL Queries (submitted for publication) Google Scholar
  3. 3.
    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
  4. 4.
    Fegaras, L., Maier, D.: Optimizing Queries Using an Effective Calculus. ACM TODS 25(4) (2000)Google Scholar
  5. 5.
    Freytag, J., Goodman, N.: On the Translation of Relational Queries into Iterative Programs. ACM Transactions on Database Systems 14(1) (March 1989)Google Scholar
  6. 6.
    Ganski, R., Wong, H.: Optimization of Nested SQL Queries Revisited. In: Proceedings of the ACM SIGMOD Conference (1987)Google Scholar
  7. 7.
    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
  8. 8.
    Kim, W.: On Optimizing an SQL-Like Nested Query. ACM Transactions On Database Systems 7(3) (September 1982)Google Scholar
  9. 9.
    Lieuwen, D., DeWitt, D.: A Transformation-Based Approach to Optimizing Loops in database Programming Languages. In: Proceedings of the ACM SIGMOD Conference (1992)Google Scholar
  10. 10.
    Lu, H., Chan, H.C., Wei, K.K.: A Survey on Usage of SQL. SIGMOD Record (1993)Google Scholar
  11. 11.
    Muralikrishna, M.: Improving Unnesting Algorithms for Join Aggregate Queries in SQL. In: Proceedings of the VLDB Conference (1992)Google Scholar
  12. 12.
    Park, J., Segev, A.: Using common subexpressions to optimize multiple queries. In: Proceedings of the 1988 IEEE CS ICDE (1988)Google Scholar
  13. 13.
    Ross, K., Rao, J.: Reusing Invariants: A New Strategy for Correlated Queries. In: Proceedings of the ACM SIGMOD Conference (1998)Google Scholar
  14. 14.
    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)Google Scholar
  15. 15.
    Seshadri, P., Pirahesh, H., Cliff Leung, T.Y.: Complex Query Decorrelation. In: Proceedings of ICDE (1996)Google Scholar
  16. 16.
    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 (1996)Google Scholar
  17. 17.
    Mumick, I.S., Pirahesh, H.: Implementation of Magic-sets in a Relational Database System. In: Proceedings of the SIGMOD Conference (1994)Google Scholar
  18. 18.
    TPC-H Benchmark, TPC Council,

Copyright information

© Springer-Verlag Berlin Heidelberg 2003

Authors and Affiliations

  • Antonio Badia
    • 1
  • Dev Anand
    • 1
  1. 1.Computer Engineering and Computer Science DepartmentUniversity of LouisvilleLouisvilleUSA

Personalised recommendations