NON-EXHAUSTIVE JOIN ORDERING SEARCH ALGORITHMS FOR LJQO

Tarcizio Alexandre Bini, Adriano Lange, Marcos Sfair Sunye, Fabiano Silva, Eduardo Cunha de Almeida

2011

Abstract

In relational database systems the optimization of select-project-join queries is a combinatorial problem. The use of exhaustive search methods is prohibitive because of the exponential increase of the search space. Randomized searches are used to find near optimal plans in polynomial time. In this paper, we investigate the large join query optimization (LJQO) problem by extending randomized algorithms and implementing a 2PO algorithm as a query optimizer in a popular open-source DBMS. We compare our solution with an implementation of a genetic algorithm. Through a multidimensional test schema, we discuss pros and cons about the behavior of these algorithms. Our results show that 2PO algorithm is fast to run and the costs of generated plans are better in most cases when compared to those of the genetic algorithms.

References

  1. Abadi, D. J., Madden, S. R., and Hachem, N. (2008). Column-stores vs. row-stores: how different are they really? In Proceedings of the 2008 ACM SIGMOD international conference on Management of data, SIGMOD 7808, pages 967-980, New York, NY, USA. ACM.
  2. Bennett, K., Ferris, M. C., and Ioannidis, Y. E. (1991). A genetic algorithm for database query optimization. In In Proceedings of the fourth International Conference on Genetic Algorithms, pages 400-407. Morgan Kaufmann Publishers.
  3. Bini, T. A., Lange, A., Sunye, M. S., and Silva, F. (2009). Stableness in large join query optimization. In ISCIS, pages 639-644.
  4. Bruno, N. (2009). Teaching an old elephant new tricks. In CIDR, pages 1-6.
  5. Dong, H. and Liang, Y. (2007). Genetic algorithms for large join query optimization. In GECCO 7807: Proceedings of the 9th annual conference on Genetic and evolutionary computation, pages 1211-1218, New York, NY, USA. ACM.
  6. Ibaraki, T. and Kameda, T. (1984). On the optimal nesting order for computing n-relational joins. ACM Transactions on Database Systems, 9(3):482-502.
  7. Ioannidis, Y. E. and Kang, Y. (1990). Randomized algorithms for optimizing large join queries. SIGMOD Rec., 19(2):312-321.
  8. Ioannidis, Y. E. and Kang, Y. C. (1991). Left-deep vs. bushy trees: an analysis of strategy spaces and its implications for query optimization. SIGMOD Rec., 20(2):168-177.
  9. Ioannidis, Y. E. and Wong, E. (1987). Query optimization by simulated annealing. In SIGMOD Conference, pages 9-22.
  10. Louis, S. J. and Zhang, Y. (1998). An empirical comparison of randomized algorithms for large join query optimization. In FLAIRS Conference, pages 95-100.
  11. Neumann, T. (2009). Query simplification: graceful degradation for join-order optimization. In SIGMOD Conference, pages 403-414.
  12. Owais, S. S. J., Krömer, P., and Snásel, V. (2005). Query optimization by genetic algorithms. In DATESO, pages 125-137.
  13. object-relational Available at URL: Selinger, P. G., Astrahan, M. M., Chamberlin, D. D., Lorie, R. A., and Price, T. G. (1979). Access path selection in a relational database management system. In Proceedings of the 1979 ACM SIGMOD international conference on Management of data, SIGMOD 7879, pages 23-34, New York, NY, USA. ACM.
  14. Shapiro, L. D., Maier, D., Benninghoff, P., Billings, K., Fan, Y., Hatwal, K., Wang, Q., Zhang, Y., min Wu, H., and Vance, B. (2001). Exploiting upper and lower bounds in top-down query optimization. In IDEAS, pages 20- 33.
  15. Steinbrunn, M., Moerkotte, G., and Kemper, A. (1997). Heuristic and randomized optimization for the join ordering problem. The VLDB Journal, 6(3):191-208.
  16. Stonebraker, M., Abadi, D. J., Batkin, A., Chen, X., Cherniack, M., Ferreira, M., Lau, E., Lin, A., Madden, S., O'Neil, E., O'Neil, P., Rasin, A., Tran, N., and Zdonik, S. (2005). C-store: a column-oriented dbms. In Proceedings of the 31st international conference on Very large data bases, VLDB 7805, pages 553-564. VLDB Endowment.
  17. Swami, A. and Gupta, A. (1988). Optimization of large join queries. In SIGMOD 7888: Proceedings of the 1988 ACM SIGMOD international conference on Management of data, pages 8-17, New York, NY, USA. ACM.
  18. Thusoo, A., Sarma, J. S., Jain, N., Shao, Z., Chakka, P., Anthony, S., Liu, H., Wyckoff, P., and Murthy, R. (2009). Hive: a warehousing solution over a mapreduce framework. Proc. VLDB Endow., 2:1626- 1629.
  19. Vance, B. and Maier, D. (1996). Rapid bushy join-order optimization with cartesian products. In SIGMOD Conference, pages 35-46.
Download


Paper Citation


in Harvard Style

Alexandre Bini T., Lange A., Sfair Sunye M., Silva F. and Cunha de Almeida E. (2011). NON-EXHAUSTIVE JOIN ORDERING SEARCH ALGORITHMS FOR LJQO . In Proceedings of the 13th International Conference on Enterprise Information Systems - Volume 1: ICEIS, ISBN 978-989-8425-53-9, pages 151-156. DOI: 10.5220/0003426501510156


in Bibtex Style

@conference{iceis11,
author={Tarcizio Alexandre Bini and Adriano Lange and Marcos Sfair Sunye and Fabiano Silva and Eduardo Cunha de Almeida},
title={NON-EXHAUSTIVE JOIN ORDERING SEARCH ALGORITHMS FOR LJQO},
booktitle={Proceedings of the 13th International Conference on Enterprise Information Systems - Volume 1: ICEIS,},
year={2011},
pages={151-156},
publisher={SciTePress},
organization={INSTICC},
doi={10.5220/0003426501510156},
isbn={978-989-8425-53-9},
}


in EndNote Style

TY - CONF
JO - Proceedings of the 13th International Conference on Enterprise Information Systems - Volume 1: ICEIS,
TI - NON-EXHAUSTIVE JOIN ORDERING SEARCH ALGORITHMS FOR LJQO
SN - 978-989-8425-53-9
AU - Alexandre Bini T.
AU - Lange A.
AU - Sfair Sunye M.
AU - Silva F.
AU - Cunha de Almeida E.
PY - 2011
SP - 151
EP - 156
DO - 10.5220/0003426501510156