Abstract
Relational implementation of recursive queries is part of the ANSI SQL99 and was implemented in Teradata V2R6. Recursive queries allow processing of hierarchical data like air flight schedules, bill-of-materials, data cube dimension hierarchies, and ancestor-descendant information (e.g. XML data stored in relations). The initial Teradata recursive query implementation is based on a static (fixed) execution plan for all recursive iterations. This may not be optimal since the intermediate results from recursive iterations vary in size. To address this problem, this paper proposes dynamic re-optimization techniques to produce execution plans that are optimal for all recursive iterations. The approach employs a mix of multi-iteration pre-planning and dynamic feedback techniques that are generally applicable to any recursive query implementation in an RDBMS. We validate our proposed techniques by conducting experiments on a prototype implementation using airline flights data.
This is a preview of subscription content, log in via an institution to check access.
Preview
Unable to display preview. Download preview PDF.
Similar content being viewed by others
References
Chen, C.M., Roussopoulos, N.: Adaptive Selectivity Estimation Using Query Feedback. ACM SIGMOD Record 23(2), 161–172 (1994)
Brueining, D., Garnett, T., Amarasinghe, S.: An Infrastructure for Adaptive Dynamic Optimization. In: Int. Symp. on Code Generation and Optimization, pp. 265–275 (2003)
Kabra, N., DeWitt, D.J.: Efficient Mid-Query Re-Optimization of Sub-Optimal Query Execution Plans. In: Proceedings of the 1998 ACM SIGMOD International Conference on Management of Data, Seattle, pp. 106–117 (1998)
Derr, M.: Adaptive Query Optimization in a Deductive Database System. In: Proc. of the 2nd Int. Conf. on Information and Knowledge Management, pp. 206–215 (1993)
Zaniolo, C., et al.: Advanced Database Systems. Morgan Kauffman, San Francisco (1997)
Lipton, R.J., Naughton, J.F.: Estimating the size of generalized transitive closures. In: VLDB, pp. 315–326 (1989)
Lipton, R.J.: Query size estimation by adaptive sampling. J. of Computer and System Sciences 51(1), 18–25 (1995)
Cohen, E.: Size-estimation framework with applications to transitive closure and reachability. J. of Computer and System Sciences 55(3), 441–453 (1997)
Ordonez, C.: Optimizing recursive queries in SQL. In: SIGMOD 2005, pp. 834–839 (2005)
Markl, V., et al.: Robust query processing through progressive optimization. In: SIGMOD 2004, pp. 659–670 (2004)
Babu, S., Bizarro, P., DeWitt, D.: Proactive re-optimization. In: SIGMOD 2005, pp. 107–118 (2005)
Zurek, T., Thanisch, P.: Optimization Strategies for Parallel Linear Recursive Query Processing, CSG Technical Report ECS-CSG-16-95 (July 1995)
Sellis, T.: Multiple Query Optimization. ACM Transactions on Database Systems, 23–52 (1988)
Roy, P., Seshadri, S., Sudarshan, S., Bhobhe, S.: Efficient and extensible algorithms for multi-query optimization. In: SIGMOD, pp. 249–260 (2000)
Dalvi, N.N., et al.: Pipelining in multi-query optimization. In: PODS, pp. 59–70 (2001)
Ionnidis, Y., Christodoulakis, S.: Optimal histograms for limiting worst-case error propagation in the size of join results. ACM TODS 18(4), 709–748 (1993)
Author information
Authors and Affiliations
Editor information
Editors and Affiliations
Rights and permissions
Copyright information
© 2006 Springer-Verlag Berlin Heidelberg
About this paper
Cite this paper
Ghazal, A., Crolotte, A., Seid, D. (2006). Recursive SQL Query Optimization with k-Iteration Lookahead. In: Bressan, S., Küng, J., Wagner, R. (eds) Database and Expert Systems Applications. DEXA 2006. Lecture Notes in Computer Science, vol 4080. Springer, Berlin, Heidelberg. https://doi.org/10.1007/11827405_34
Download citation
DOI: https://doi.org/10.1007/11827405_34
Publisher Name: Springer, Berlin, Heidelberg
Print ISBN: 978-3-540-37871-6
Online ISBN: 978-3-540-37872-3
eBook Packages: Computer ScienceComputer Science (R0)