Pushing Predicates into Recursive SQL Common Table Expressions

  • Marta Burzańska
  • Krzysztof Stencel
  • Piotr Wiśniewski
Part of the Lecture Notes in Computer Science book series (LNCS, volume 5739)


A recursive SQL-1999 query consists of a recursive CTE (Common Table Expression) and a query which uses it. If such a recursive query is used in a context of a selection predicate, this predicate can possibly be pushed into the CTE thus limiting the breadth and/or depth of the recursive search. This can happen e.g. after the definition of a view containing recursive query has been expanded in place. In this paper we propose a method of pushing predicates and other query operators into a CTE. This allows executing the query with smaller temporary data structures, since query operators external w.r.t. the CTE can be computed on the fly together with the CTE. Our method is inspired on the deforestation (a.k.a. program fusion) successfully applied in functional programming languages.


Unable to display preview. Download preview PDF.

Unable to display preview. Download preview PDF.


  1. 1.
    Bancilhon, F., Maier, D., Sagiv, Y., Ullman, J.D.: Magic sets and other strange ways to implement logic programs. In: PODS, pp. 1–15. ACM, New York (1986)Google Scholar
  2. 2.
    Levy, A.Y., Mumick, I.S., Sagiv, Y.: Query optimization by predicate move-around. In: Bocca, J.B., Jarke, M., Zaniolo, C. (eds.) VLDB, pp. 96–107. Morgan Kaufmann, San Francisco (1994)Google Scholar
  3. 3.
    Wadler, P.: Deforestation: Transforming programs to eliminate trees. Theor. Comput. Sci. 73(2), 231–248 (1990)MathSciNetCrossRefMATHGoogle Scholar
  4. 4.
    Jones, S.P., Tolmach, A., Hoare, T.: Playing by the rules: rewriting as a practical optimisation technique in GHC. In: Haskell Workshop, ACM SIGPLAN, pp. 203–233 (2001)Google Scholar
  5. 5.
    Gill, A.J., Launchbury, J., Jones, S.L.P.: A short cut to deforestation. In: FPCA, pp. 223–232 (1993)Google Scholar
  6. 6.
    Johann, P.: Short cut fusion: Proved and improved. In: Taha, W. (ed.) SAIG 2001. LNCS, vol. 2196, pp. 47–71. Springer, Heidelberg (2001)CrossRefGoogle Scholar
  7. 7.
    Ohori, A., Sasano, I.: Lightweight fusion by fixed point promotion. In: Hofmann, M., Felleisen, M. (eds.) POPL, pp. 143–154. ACM, New York (2007)Google Scholar
  8. 8.
    Grust, T., Grust, T., Scholl, M.H., Scholl, M.H.: Query deforestation. Technical report, Faculty of Mathematics and Computer Science, Database Research Group, University of Konstanz (1998)Google Scholar
  9. 9.
    Ordonez, C.: Optimizing recursive queries in SQL. In: SIGMOD 2005: Proceedings of the 2005 ACM SIGMOD international conference on Management of data, pp. 834–839. ACM, New York (2005)Google Scholar

Copyright information

© Springer-Verlag Berlin Heidelberg 2009

Authors and Affiliations

  • Marta Burzańska
    • 1
  • Krzysztof Stencel
    • 1
  • Piotr Wiśniewski
    • 1
  1. 1.Faculty of Mathematics and Computer ScienceNicolaus Copernicus UniversityToruńPoland

Personalised recommendations