Redundancy Awareness in SQL Queries

In this paper, we study SQL queries with aggregate subqueries that share common tables and conditions with the outer query. While several approaches can deal with such queries, they have limited applicability. We propose the redundancy awareness method to detect the largest common part shared by query and subquery, compute it once, and determine what operations are needed to finish evaluation of the original query. Our approach can deal with redundancy in all types of subqueries. We offer the possibility for the optimizer to choose the most efficient plan for a given query. We have implemented our approach on top of a commercial DBMS; our experiments show that our approach compares favorably to existing optimization techniques.

[1]  Won Kim,et al.  On optimizing an SQL-like nested query , 1982, TODS.

[2]  Calisto Zuzarte,et al.  Optimizing complex queries based on similarities of subqueries , 2004, Knowledge and Information Systems.

[3]  Lars Bækgaard,et al.  Incremental computation of nested relational query expressions , 1995, TODS.

[4]  Umeshwar Dayal,et al.  Of Nests and Trees: A Unified Approach to Processing Queries That Contain Nested Subqueries, Aggregates, and Quantifiers , 1987, VLDB.

[5]  Hamid Pirahesh,et al.  Answering complex SQL queries using automatic summary tables , 2000, SIGMOD '00.

[6]  Antonio Badia,et al.  A nested relational approach to processing SQL subqueries , 2005, SIGMOD '05.

[7]  Hamid Pirahesh,et al.  Complex query decorrelation , 1996, Proceedings of the Twelfth International Conference on Data Engineering.

[8]  Harry K. T. Wong,et al.  Optimization of nested SQL queries revisited , 1987, SIGMOD '87.

[9]  Kenneth A. Ross,et al.  Reusing invariants: a new strategy for correlated queries , 1998, SIGMOD '98.

[10]  Hamid Pirahesh,et al.  WinMagic: subquery elimination using window aggregation , 2003, SIGMOD '03.

[11]  Michael H. Böhlen,et al.  Efficient computation of subqueries in complex OLAP , 2003, Proceedings 19th International Conference on Data Engineering (Cat. No.03CH37405).

[12]  Jonathan Goldstein,et al.  Optimizing queries using materialized views: a practical, scalable solution , 2001, SIGMOD '01.

[13]  César A. Galindo-Legaria,et al.  Orthogonal optimization of subqueries and aggregation , 2001, SIGMOD '01.

[14]  M. Muralikrishna.  Optimization and Dataflow Algorithms for Nested Tree Queries , 1989, VLDB.

[15]  Jeffrey Xu Yu,et al.  Algebraic equivalences of nested relational operators , 2005, Inf. Syst..

[16]  Marc H. Scholl,et al.  Theoretical Foundation of Algebraic Optimization Utilizing Unnormalized Relations , 1986, ICDT.

[17]  Y. Jan.  Algebraic optimization for nested relations , 1990, Twenty-Third Annual Hawaii International Conference on System Sciences.

[18]  Alon Y. Halevy,et al.  Answering queries using views: A survey , 2001, The VLDB Journal.