Exploiting uniqueness in query optimization

Consider an SQL query that specifies duplicate elimination via a DISTINCT clause. Because duplicate elimination often requires an expensive sort of the query result, it is often worthwhile to identify unnecessary DISTINCT clauses and avoid the sort altogether. We prove a necessary and sufficient condition for deciding if a query requires duplicate elimination. The condition exploits knowledge about keys, table constraints, and query predicates. Because the condition cannot always be tested efficiently, we offer a practical algorithm that tests a simpler, sufficient condition. We consider applications of this condition for various types of queries, and show that we can exploit this condition in both relational and nonregulation database systems.<<ETX>>

[1]  Anthony C. Klug.  Calculating constraints on relational expression , 1980, TODS.

[2]  Yannis E. Ioannidis,et al.  Randomized algorithms for optimizing large join queries , 1990, SIGMOD '90.

[3]  Hamid Pirahesh,et al.  Extensible/rule based query rewrite optimization in Starburst , 1992, SIGMOD '92.

[4]  Eugene Wong,et al.  Decomposition—a strategy for query processing , 1976, TODS.

[5]  Patricia G. Selinger,et al.  Access path selection in a relational database management system , 1979, SIGMOD '79.

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

[7]  Per-Ake Larson,et al.  Performing Group-By before Join , 1994, ICDE 1994.

[8]  C. J. Date,et al.  Relational database writings: 1985-1989 , 1990 .

[9]  Jonathan J. King,et al.  Query optimization by semantic reasoning , 1981 .

[10]  Jennifer Widom,et al.  Deriving Production Rules for Incremental View Maintenance , 1991, VLDB.

[11]  Günter von Bültzingsloewen.  Translating and Optimizing SQL Queries Having Aggregates , 1987, VLDB.

[12]  Giuseppe Pelagatti,et al.  Formal semantics of SQL queries , 1991, TODS.

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

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

[15]  Martin L. Kersten,et al.  Fast, Randomized Join-Order Selection - Why Use Transformations? , 1994, VLDB.

[16]  M. Muralikrishna,et al.  Improved Unnesting Algorithms for Join Aggregate SQL Queries , 1992, VLDB.

[17]  Jeffrey D. Ullman,et al.  Principles Of Database And Knowledge-Base Systems , 1979 .

[18]  Georg Gottlob,et al.  Translating SQL Into Relational Algebra: Optimization, Semantics, and Equivalence of SQL Queries , 1985, IEEE Transactions on Software Engineering.

[19]  Goetz Graefe,et al.  Optimization of dynamic query evaluation plans , 1994, SIGMOD '94.

[20]  Arun N. Swami,et al.  Optimization of large join queries: combining heuristics and combinatorial techniques , 1989, SIGMOD '89.

[21]  Jeffrey D. Uuman.  Principles of database and knowledge- base systems , 1989 .

[22]  Patrick Valduriez,et al.  On the Effectiveness of Optimization Search Strategies for Parallel Execution Spaces , 1993, VLDB.

[23]  C. J. Date.  An Introduction to Database Systems, Volume II , 1980 .

[24]  Werner Kießling.  On Semantic Reefs and Efficient Processing of Correlation Queries with Aggregates , 1985, VLDB.

[25]  David Maier,et al.  The Theory of Relational Databases , 1983 .

[26]  Arun N. Swami,et al.  Optimization of large join queries , 1988, SIGMOD '88.

[27]  Giinter von Biiltzingsloewen.  Translating and Optimizing SQL Queries Having Aggregates , 1987 .

[28]  Eugene Wong,et al.  Query optimization by simulated annealing , 1987, SIGMOD '87.