Optimization of Linear Recursive Queries in SQL

Recursion is a fundamental computation mechanism which has been incorporated into the SQL language. This work focuses on the optimization of linear recursive queries in SQL. Query optimization is studied with two important graph problems: computing the transitive closure of a graph and getting the power matrix of its adjacency matrix. We present SQL implementations for two fundamental algorithms: seminaive and direct. Five query optimizations are studied: 1) storage and indexing; 2) early selection; 3) early evaluation of nonrecursive joins; 4) pushing duplicate elimination; and 5) pushing aggregation. Experiments compare both evaluation algorithms and systematically evaluate the impact of optimizations with large input tables. Optimizations are evaluated on four types of graphs: binary trees, lists, cyclic graphs, and complete graphs, going from the best to worst case. In general, Seminaive is faster than direct, except for complete graphs. Storing and indexing rows by vertex and pushing aggregation work well on trees, lists, and cyclic graphs. Pushing duplicate elimination is essential for complete graphs, but slows computation for acyclic graphs. Early selection with equality predicates significantly accelerates computation for all types of graphs.

[1]  Hamid Pirahesh,et al.  Magic conditions , 1990, PODS.

[2]  Moshe Y. Vardi.  Decidability and undecidability results for boundedness of linear recursive queries , 1988, PODS.

[3]  Volker Linnemann.  Non first normal form relations and recursive queries: An SQL-based approach , 1987, 1987 IEEE Third International Conference on Data Engineering.

[4]  Limsoon Wong,et al.  Incremental Recomputation of Recursive Queries with Nested Sets and Aggregate Functions , 1997, DBPL.

[5]  Hamid Pirahesh,et al.  Magic is relevant , 1990, SIGMOD '90.

[6]  Lawrence J. Henschen,et al.  Handling redundancy in the processing of recursive database queries , 1987, SIGMOD '87.

[7]  Henry S. Warren,et al.  A modification of Warshall's algorithm for the transitive closure of binary relations , 1975, Commun. ACM.

[8]  Surajit Chaudhuri,et al.  An overview of query optimization in relational systems , 1998, PODS.

[9]  Divesh Srivastava,et al.  The CORAL deductive system , 1994, The VLDB Journal.

[10]  Lawrence J. Henschen,et al.  Classification and Compilation of Linear Recursive Queries in Deductive Databases , 1992, IEEE Trans. Knowl. Data Eng..

[11]  Julius T. Tou,et al.  Information Systems , 1973, GI Jahrestagung.

[12]  Rakesh Agrawal,et al.  Extending SQL with Generalized Transitive Closure Functionality , 1993, IEEE Trans. Knowl. Data Eng..

[13]  Carlos Ordonez.  Optimizing recursive queries in SQL , 2005, SIGMOD '05.

[14]  Jeffrey F. Naughton,et al.  On the expected size of recursive Datalog queries , 1991, J. Comput. Syst. Sci..

[15]  H. V. Jagadish,et al.  Direct transitive closure algorithms: design and performance evaluation , 1990, TODS.

[16]  Kemal Koymen,et al.  SQL*: a recursive SQL , 1993, Inf. Syst..

[17]  Serge Abiteboul,et al.  Foundations of Databases , 1994 .

[18]  Jianwen Su,et al.  Incremental maintenance of recursive views using relational calculus/SQL , 2000, SGMD.

[19]  Patrick Valduriez,et al.  Evaluation of Recursive Queries Using Join Indices , 1986, Expert Database Conf..

[20]  Serge Abiteboul,et al.  Foundations of Databases: The Logical Level , 1995 .

[21]  Divesh Srivastava,et al.  Implementation of the CORAL deductive database system , 1993, SIGMOD Conference.

[22]  Seppo Sippu,et al.  An analysis of magic sets and related optimization strategies for logic queries , 1996, JACM.

[23]  Hamid Pirahesh,et al.  Implementation of magic-sets in a relational database system , 1994, SIGMOD '94.

[24]  Hamid Pirahesh,et al.  Data Cube: A Relational Aggregation Operator Generalizing Group-By, Cross-Tab, and Sub-Totals , 1996, Data Mining and Knowledge Discovery.

[25]  Jeffrey D. Ullman,et al.  Implementation of logical query languages for databases , 1985, TODS.

[26]  Goetz Graefe,et al.  Query evaluation techniques for large databases , 1993, CSUR.

[27]  Raghu Ramakrishnan,et al.  Transitive closure algorithms based on graph traversal , 1993, TODS.