Advertisement

On the Optimal Ordering of Maps, Selections, and Joins Under Factorization

  • Thomas Neumann
  • Sven Helmer
  • Guido Moerkotte
Conference paper
Part of the Lecture Notes in Computer Science book series (LNCS, volume 4042)

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.

Keywords

Dependency Graph Optimal Ordering Query Plan Partial Plan Query Graph 
These keywords were added by machine and not by the authors. This process is experimental and the keywords may be updated as the learning algorithm improves.

Preview

Unable to display preview. Download preview PDF.

Unable to display preview. Download preview PDF.

References

  1. 1.
    Bendtsen, J., Nielsen, H., von Heijne, G., Brunak, S.: Improved prediction of signal peptides: SignalP 3.0. J. Mol. Biol. 340, 783–795 (2004)CrossRefGoogle Scholar
  2. 2.
    Bird, R., Wadler, P.: An Introduction to Functional Programming. Prentice-Hall, Englewood Cliffs (1988)Google Scholar
  3. 3.
    Blobel, G.: Protein targeting. ChemBioChem 1, 86–102 (2000)CrossRefGoogle Scholar
  4. 4.
    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)Google Scholar
  5. 5.
    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)Google Scholar
  6. 6.
    Chaudhuri, S., Shim, K.: Optimization of queries with user-defined predicates. ACM Trans. on Database Systems 24(2), 177–228 (1999)CrossRefGoogle Scholar
  7. 7.
    Hellerstein, J.: Predicate migration: Optimizing queries with expensive predicates. In: Computer Science Division, EECS Department, University of California at Berkeley (December 1992)Google Scholar
  8. 8.
    Hellerstein, J.: Practical predicate placement. In: Proc. of the ACM SIGMOD Conf. on Management of Data, pp. 325–335 (1994)Google Scholar
  9. 9.
    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)Google Scholar
  10. 10.
    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)Google Scholar
  11. 11.
    Ibaraki, T., Kameda, T.: On the optimal nesting order for computing n-relational joins. ACM Transactions on Database Systems 9(3), 482–502 (1984)CrossRefMathSciNetGoogle Scholar
  12. 12.
    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)Google Scholar
  13. 13.
    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)Google Scholar
  14. 14.
    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)Google Scholar
  15. 15.
    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)Google Scholar
  16. 16.
    Scheufele, W., Moerkotte, G.: Optimal ordering of selections and joins in acyclic queries with expensive predicates. Technical Report 96-3, RWTH-Aachen (1996)Google Scholar
  17. 17.
    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)CrossRefGoogle Scholar
  18. 18.
    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)Google Scholar
  19. 19.
    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)Google Scholar

Copyright information

© Springer-Verlag Berlin Heidelberg 2006

Authors and Affiliations

  • Thomas Neumann
    • 1
  • Sven Helmer
    • 2
  • Guido Moerkotte
    • 3
  1. 1.Max-Planck Institute of Computer ScienceSaarbrückenGermany
  2. 2.University of London, Birkbeck CollegeLondonUnited Kingdom
  3. 3.University of MannheimMannheimGermany

Personalised recommendations