Abstract
We examine the problem of producing the optimal evaluation order for queries containing joins, selections, and maps. Specifically, we look at the case where common subexpressions involving expensive UDF calls can be factored out. First, we show that ignoring factorization during optimization can lead to plans that are far off the best possible plan: the difference in cost between the best plan considering factorization and the best plan not considering factorization can easily reach several orders of magnitude. Then, we introduce optimization strategies that produce optimal left-deep and bushy plans when factorization is taken into account. Experiments (1) confirm that factorization is a critical issue when it comes to generating optimal plans and (2) we show that to consider factorization does not make plan generation significantly more expensive.
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.
Similar content being viewed by others
References
Bendtsen, J., Nielsen, H., von Heijne, G., Brunak, S.: Improved prediction of signal peptides: SignalP 3.0. J. Mol. Biol. 340, 783–795 (2004)
Bird, R., Wadler, P.: An Introduction to Functional Programming. Prentice-Hall, Englewood Cliffs (1988)
Blobel, G.: Protein targeting. ChemBioChem 1, 86–102 (2000)
Chaudhuri, S., Ganesan, P., Sarawagi, S.: Factorizing complex predicates in queries to exploit indexes. In: Proc. of the ACM SIGMOD Conf. on Management of Data, pp. 361–372 (2003)
Chaudhuri, S., Shim, K.: Optimization of queries with user-defined predicates. In: Proc. Int. Conf. on Very Large Data Bases (VLDB), pp. 87–98 (1996)
Chaudhuri, S., Shim, K.: Optimization of queries with user-defined predicates. ACM Trans. on Database Systems 24(2), 177–228 (1999)
Hellerstein, J.: Predicate migration: Optimizing queries with expensive predicates. In: Computer Science Division, EECS Department, University of California at Berkeley (December 1992)
Hellerstein, J.: Practical predicate placement. In: Proc. of the ACM SIGMOD Conf. on Management of Data, pp. 325–335 (1994)
Hellerstein, J., Naughton, J.: Query execution techniques for caching expensive methods. In: Proc. of the ACM SIGMOD Conf. on Management of Data, pp. 423–434 (1996)
Hellerstein, J., Stonebraker, M.: Predicate migration: Optimizing queries with expensive predicates. In: Proc. of the ACM SIGMOD Conf. on Management of Data, pp. 267–277 (1993)
Ibaraki, T., Kameda, T.: On the optimal nesting order for computing n-relational joins. ACM Transactions on Database Systems 9(3), 482–502 (1984)
Kemper, A., Moerkotte, G., Peithner, K., Steinbrunn, M.: Optimizing disjunctive queries with expensive predicates. In: Proc. of the ACM SIGMOD Conf. on Management of Data, pp. 336–347 (1994)
Kemper, A., Moerkotte, G., Steinbrunn, M.: Optimization of boolean expressions in object bases. In: Proc. Int. Conf. on Very Large Data Bases (VLDB), pp. 79–90 (1992)
Neumann, T., Helmer, S., Moerkotte, G.: On the optimal ordering of maps and selections under factorization. In: Proc. IEEE Conference on Data Engineering, pp. 490–501 (2005)
Ono, K., Lohman, G.: Measuring the complexity of join enumeration in query optimization. In: Proc. Int. Conf. on Very Large Data Bases (VLDB), pp. 314–325 (1990)
Scheufele, W., Moerkotte, G.: Optimal ordering of selections and joins in acyclic queries with expensive predicates. Technical Report 96-3, RWTH-Aachen (1996)
Scheufele, W., Moerkotte, G.: Efficient dynamic programming algorithms for ordering expensive joins and selections. In: Schek, H.-J., Saltor, F., Ramos, I., Alonso, G. (eds.) EDBT 1998. LNCS, vol. 1377, pp. 201–215. Springer, Heidelberg (1998)
Steinbrunn, M., Peithner, K., Moerkotte, G., Kemper, A.: Bypassing joins in disjunctive queries. In: Proc. Int. Conf. on Very Large Data Bases (VLDB), pp. 228–238 (1995)
Yajima, K., Kitagawa, H., Yamaguchi, K., Ohbo, N., Fujiwara, Y.: Optimization of queries including adt functions. In: International Symposium on Database Systems for Advanced Applications, pp. 366–376 (1991)
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
Neumann, T., Helmer, S., Moerkotte, G. (2006). On the Optimal Ordering of Maps, Selections, and Joins Under Factorization. In: Bell, D.A., Hong, J. (eds) Flexible and Efficient Information Handling. BNCOD 2006. Lecture Notes in Computer Science, vol 4042. Springer, Berlin, Heidelberg. https://doi.org/10.1007/11788911_10
Download citation
DOI: https://doi.org/10.1007/11788911_10
Publisher Name: Springer, Berlin, Heidelberg
Print ISBN: 978-3-540-35969-2
Online ISBN: 978-3-540-35971-5
eBook Packages: Computer ScienceComputer Science (R0)