Abstract
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.
This is a preview of subscription content, log in via an institution to check access.
Access this chapter
Tax calculation will be finalised at checkout
Purchases are for personal use only
Preview
Unable to display preview. Download preview PDF.
References
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)
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)
Wadler, P.: Deforestation: Transforming programs to eliminate trees. Theor. Comput. Sci. 73(2), 231–248 (1990)
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)
Gill, A.J., Launchbury, J., Jones, S.L.P.: A short cut to deforestation. In: FPCA, pp. 223–232 (1993)
Johann, P.: Short cut fusion: Proved and improved. In: Taha, W. (ed.) SAIG 2001. LNCS, vol. 2196, pp. 47–71. Springer, Heidelberg (2001)
Ohori, A., Sasano, I.: Lightweight fusion by fixed point promotion. In: Hofmann, M., Felleisen, M. (eds.) POPL, pp. 143–154. ACM, New York (2007)
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)
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)
Author information
Authors and Affiliations
Editor information
Editors and Affiliations
Rights and permissions
Copyright information
© 2009 Springer-Verlag Berlin Heidelberg
About this paper
Cite this paper
Burzańska, M., Stencel, K., Wiśniewski, P. (2009). Pushing Predicates into Recursive SQL Common Table Expressions. In: Grundspenkis, J., Morzy, T., Vossen, G. (eds) Advances in Databases and Information Systems. ADBIS 2009. Lecture Notes in Computer Science, vol 5739. Springer, Berlin, Heidelberg. https://doi.org/10.1007/978-3-642-03973-7_15
Download citation
DOI: https://doi.org/10.1007/978-3-642-03973-7_15
Publisher Name: Springer, Berlin, Heidelberg
Print ISBN: 978-3-642-03972-0
Online ISBN: 978-3-642-03973-7
eBook Packages: Computer ScienceComputer Science (R0)