Recursive SQL Query Optimization with k-Iteration Lookahead

  • Ahmad Ghazal
  • Alain Crolotte
  • Dawit Seid
Part of the Lecture Notes in Computer Science book series (LNCS, volume 4080)


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.


Transitive Closure Execution Plan Query Optimization Execution Engine Seed Part 


Unable to display preview. Download preview PDF.

Unable to display preview. Download preview PDF.


  1. 1.
    Chen, C.M., Roussopoulos, N.: Adaptive Selectivity Estimation Using Query Feedback. ACM SIGMOD Record 23(2), 161–172 (1994)CrossRefGoogle Scholar
  2. 2.
    Brueining, D., Garnett, T., Amarasinghe, S.: An Infrastructure for Adaptive Dynamic Optimization. In: Int. Symp. on Code Generation and Optimization, pp. 265–275 (2003)Google Scholar
  3. 3.
    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)Google Scholar
  4. 4.
    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)Google Scholar
  5. 5.
    Zaniolo, C., et al.: Advanced Database Systems. Morgan Kauffman, San Francisco (1997)Google Scholar
  6. 6.
    Lipton, R.J., Naughton, J.F.: Estimating the size of generalized transitive closures. In: VLDB, pp. 315–326 (1989)Google Scholar
  7. 7.
    Lipton, R.J.: Query size estimation by adaptive sampling. J. of Computer and System Sciences 51(1), 18–25 (1995)MATHCrossRefMathSciNetGoogle Scholar
  8. 8.
    Cohen, E.: Size-estimation framework with applications to transitive closure and reachability. J. of Computer and System Sciences 55(3), 441–453 (1997)MATHCrossRefGoogle Scholar
  9. 9.
    Ordonez, C.: Optimizing recursive queries in SQL. In: SIGMOD 2005, pp. 834–839 (2005)Google Scholar
  10. 10.
    Markl, V., et al.: Robust query processing through progressive optimization. In: SIGMOD 2004, pp. 659–670 (2004)Google Scholar
  11. 11.
    Babu, S., Bizarro, P., DeWitt, D.: Proactive re-optimization. In: SIGMOD 2005, pp. 107–118 (2005)Google Scholar
  12. 12.
    Zurek, T., Thanisch, P.: Optimization Strategies for Parallel Linear Recursive Query Processing, CSG Technical Report ECS-CSG-16-95 (July 1995)Google Scholar
  13. 13.
    Sellis, T.: Multiple Query Optimization. ACM Transactions on Database Systems, 23–52 (1988)Google Scholar
  14. 14.
    Roy, P., Seshadri, S., Sudarshan, S., Bhobhe, S.: Efficient and extensible algorithms for multi-query optimization. In: SIGMOD, pp. 249–260 (2000)Google Scholar
  15. 15.
    Dalvi, N.N., et al.: Pipelining in multi-query optimization. In: PODS, pp. 59–70 (2001)Google Scholar
  16. 16.
    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)CrossRefGoogle Scholar

Copyright information

© Springer-Verlag Berlin Heidelberg 2006

Authors and Affiliations

  • Ahmad Ghazal
    • 1
  • Alain Crolotte
    • 1
  • Dawit Seid
    • 1
  1. 1.Teradata DivisionNCR CorporationEl Segundo

Personalised recommendations