Advertisement

Knowledge and Information Systems

, Volume 20, Issue 2, pp 187–220 | Cite as

Exploiting maximal redundancy to optimize SQL queries

  • Bin CaoEmail author
  • Antonio Badia
Regular Paper

Abstract

Detecting and dealing with redundancy is an ubiquitous problem in query optimization, which manifests itself in many areas of research such as materialized views, multi-query optimization, and query-containment algorithms. In this paper, we focus on the issue of intra-query redundancy, redundancy present within a query. We present a method to detect the maximal redundancy present between a main (outer) query block and a subquery block. We then use the method for query optimization, introducing query plans and a new operator that take full advantage of the redundancy discovered. Our approach can deal with redundancy in a wider spectrum of queries than existing techniques. We show experimental evidence that our approach works under certain conditions, and compares favorably to existing optimization techniques when applicable.

Keywords

Intra-query redundancy Nested queries Query optimization 

Preview

Unable to display preview. Download preview PDF.

Unable to display preview. Download preview PDF.

References

  1. 1.
    Akinde M, Bohlen M (2003) Efficient computation of subqueries in complex olap. In: Proceedings of the ICDE Conference, pp 163–174Google Scholar
  2. 2.
    Chatziantoniou D, Ross KA (1996) Querying multiple features of groups in relational databases. In: Proceedings of the VLDB Conference, pp 295–306Google Scholar
  3. 3.
    Chatziantoniou D, Ross KA (1997) Groupwise processing of relational queries. In: Proceedings of the VLDB Conference, pp 476–485Google Scholar
  4. 4.
    Council TPP (n.d.) The tpc-h benchmark. http://www.tpc.org/tpch
  5. 5.
    Dayal U (1987) Of nests and trees: a unified approach to processing queries that contain nested subqueries, aggregates, and quantifiers. In: Proceedings of the VLDB Conference, pp 197–208Google Scholar
  6. 6.
    Galindo-Legaria CA, Joshi MM (2001) Orthogonal optimization of subqueries and aggregation. In: Proceedings of the SIGMOD Conference, pp 571–581Google Scholar
  7. 7.
    Ganski RA, Wong HKT (1987) Optimization of nested sql queries revisited. In: Proceedings of the SIGMOD Conference, pp 23–33Google Scholar
  8. 8.
    Goel P, Iyer B (1996) Sql query optimization: Reordering for a general class of queries. In: Proceedings of the SIGMOD Conference, pp 47–56Google Scholar
  9. 9.
    Goldstein J, Larson P-A (2001) Optimizing queries using materialized views: a practical, scalable solution. In: Proceedings of the SIGMOD Conference, pp 331–342Google Scholar
  10. 10.
    Gupta A, Harinarayan V, Quass D (1995) Aggregate-query processing in data warehousing environments. In: Proceedings of the VLDB Conference, pp 358–369Google Scholar
  11. 11.
    Halevy A (2001) Answering queries using views: a survey. VLDB J 10(4): 270–294zbMATHCrossRefGoogle Scholar
  12. 12.
    Hellerstein J (1998) Optimization techniques for queries with expensive methods. ACM Trans Database Syst 23(2): 113–157CrossRefMathSciNetGoogle Scholar
  13. 13.
    Kim W (1982) On optimizing an sql-like nested query. ACM Trans Database Syst 7(3): 443–469zbMATHCrossRefGoogle Scholar
  14. 14.
    Kossmann D (2000) The state of the art in distributed query processing. ACM Comput Surv 32(4): 422–469CrossRefGoogle Scholar
  15. 15.
    Li Z, Ng WK, Sun A (2005) Web data extraction based on structural similarity. Knowl Inf Syst 8(4): 438–461CrossRefGoogle Scholar
  16. 16.
    Lu H, Chan HC, Wei KK (1993) A survey on usage of sql. ACM SIGMOD Record 22(4): 60–65CrossRefGoogle Scholar
  17. 17.
    Millstein T, Halevy A, Friedman M (2003) Query containment for data integration systems. J Comp Syst Sci 66(1): 10–39CrossRefMathSciNetGoogle Scholar
  18. 18.
    Muralikrishna M (1989) Optimization and dataflow algorithms for nested tree queries. In: Proceedings of the VLDB conference, pp 77–85Google Scholar
  19. 19.
    Muralikrishna M (1992) Improved unnesting algorithms for join aggregate sql queries. In: Proceedings of the VLDB conference, pp 91–102Google Scholar
  20. 20.
    Park J, Segev A (1988) Using common subexpressions to optimize multiple queries. In: Proceedings of the ICDE Conference, pp 311–319Google Scholar
  21. 21.
    Rao J, Ross KA (1998) Reusing invariants: a new strategy for correlated queries. In: Proceedings of the SIGMOD conference, pp 37–48Google Scholar
  22. 22.
    Roy P, Seshadri S, Sudarshan S, Bhobe S (2000) Efficient and extensible algorithms for multi query optimization. In: Proceedings of the SIGMOD conference, pp 249–260Google Scholar
  23. 23.
    Sellis TK (1988) Multiple-query optimization. ACM Trans Database Syst 13(1): 23–52CrossRefGoogle Scholar
  24. 24.
    Seshadri P, Pirahesh H, Leung TYC (1996) Complex query decorrelation. In: Proceedings of the ICDE conference, pp 450–458Google Scholar
  25. 25.
    Ullman J (2000) Information integration using logical views. Theor Comp Sci 239(2): 189–210zbMATHCrossRefMathSciNetGoogle Scholar
  26. 26.
    Wan X (2008) Beyond topical similarity: a structural similarity measure for retrieving highly similar documents. Knowl Inf Syst 15(1): 55–73CrossRefGoogle Scholar
  27. 27.
    Zaharioudakis M, Cochrane R, Lapis G, Pirahesh H, Urata M (2000) Answering complex sql queries using automatic summary tables. In: Proceedings of the SIGMOD conference, pp 105–116Google Scholar
  28. 28.
    Zhou J, Larson P-A, Freytag J-C, Lehner W (2007) Efficient explointation of similar subexpressions for query processing. In: Proceedings of the SIGMOD conference, pp 533–544Google Scholar
  29. 29.
    Zhu Q, Tao Y, Zuzarte C (2005) Optimizing complex queries based on similarities of subqueries. Knowl Inf Syst 8(3): 350–373CrossRefGoogle Scholar
  30. 30.
    Zuzarte C, Pirahesh H, Ma W, Cheng Q, Liu L, Wong K (2003) Winmagic: Subquery elimination using window aggregation. In: Proceedings of the SIGMOD conference, pp 652–656Google Scholar

Copyright information

© Springer-Verlag London Limited 2008

Authors and Affiliations

  1. 1.Computer Science DepartmentIndiana UniversityBloomingtonUSA
  2. 2.Computer Engineering and Computer Science DepartmentUniversity of LouisvilleLouisvilleUSA

Personalised recommendations