Advertisement

Progress in Artificial Intelligence

, Volume 8, Issue 1, pp 45–62 | Cite as

A review of different cost-based distributed query optimizers

  • Manik SharmaEmail author
  • Gurvinder Singh
  • Rajinder Singh
Review

Abstract

The paper narrates the review of cost-based query optimizers designed using database strategies, deterministic, stochastic, hybrid and energy efficiency-based techniques. It was endowed that earlier authors have used a different database and deterministic strategy like indexing, query filtering, normalization, query graph, tableau, exhaustive enumeration, query graph and dynamic programming to optimize queries. However, these techniques are not pertinent to the optimization of serpentine database queries. Nonetheless, it can be resourcefully optimized by using divergent individual and hybrid nature-inspired computing techniques. Research divulges that the hybrid approach was and remains effective to unravel the query optimization problem. Moreover, notable work is effectuated to optimize data retrieval queries only; however, little work is carried out to optimize write, delete and update queries. Additionally, energy-efficient query optimization is an emanate area. The copious amount of energy can be defended by using energy-efficient query optimizers. The extensive publication trend of distributed query optimizers has also examined that can be of enormous concern for the researchers who want to publish their article and to pursue their research in this domain area. It is ascertained that momentous volume of query optimization work has been effectuated using genetic algorithm followed by swarm particle optimization. Additionally, the researcher has to use and analyze the performance of different emerging evolutionary techniques (Ant Lion Optimization, Whale Optimization, Monkey Search, Dolphin Echolocation, Chaotic Swarming) in designing cost-based query optimizer.

Keywords

Query optimization Deterministic techniques Nature-inspired computing Database strategies Hybrid approaches Energy-efficient query optimizer 

References

  1. 1.
    Gorla, N., Song, S.K.: Sub-query allocation in DDB using GA. J. Comput. Sci. Technol. 10, 31–37 (2010)Google Scholar
  2. 2.
    Zhou, L., Chen, Y., Li, T., Yu, Y.: The semi-join query optimization in a distributed database system. In: National Conference on Information Technology and Computer Science (CITCS 2012), pp. 606–609 (2012)Google Scholar
  3. 3.
    Ozsu, M.T., Valduriez, P.: Principles of Distributed Database Systems, 2nd edn. Pearson Education, New York City (2009)Google Scholar
  4. 4.
    French, C.D.: “One size fits all” database architectures do not work for DSS. In: ACM SIGMOD Record, vol. 24, no. 2, pp. 449–450. ACM (1995)Google Scholar
  5. 5.
    Elnaffar, S., Martin, P., Schiefer, B., Lightstone, S.: Is it DSS or OLTP: automatically identifying DBMS workloads. J. Intell. Inf. Syst. 30(3), 249–271 (2008)Google Scholar
  6. 6.
    Sharma, M., Singh, G., Singh, R.: Design and analysis of stochastic DSS query optimizers in a distributed database system. Egypt. Inf. J. 17(2), 161–173 (2016)Google Scholar
  7. 7.
    Patel, D., Patel, P.: A review paper on different approaches for query optimization using schema object base view. Int. J. Comput. Appl. 114(4), 1 (2015)Google Scholar
  8. 8.
    Umar, Y.R.M., Welekar, A.R.: Query optimization in distributed database: a review. Int. J. Curr. Eng. Technol. 4(6), 3901–3903 (2014)Google Scholar
  9. 9.
    Jarke, M., Koch, J.: Query optimization in database systems. ACM Comput. Surv. (CsUR) 16(2), 111–152 (1984)MathSciNetzbMATHGoogle Scholar
  10. 10.
    Vellev, S.: Review of algorithms for the join ordering problems in database query optimization. Inf. Technol. Control 1, 32–40 (2009)Google Scholar
  11. 11.
    Banubakode, A., Acharya, H.: Query optimization in object-oriented database management systems: a short review. Int. J. Comput. Sci. Eng. Technol. 1(1), 1–6 (2010)Google Scholar
  12. 12.
    Khan, M., Khan, M.N.A.: Exploring query optimization techniques in relational databases. Int. J. Database Theory Appl. 6(3), 11–20 (2013)Google Scholar
  13. 13.
    Doshi, P., Raisinghani, V.: Review of dynamic query optimization strategies in distributed database. In: 2011 3rd International Conference on Electronics Computer Technology (ICECT), vol. 6, pp. 145–149, IEEE (2011)Google Scholar
  14. 14.
    Aponso, G.C.A.L., Tennakon, T.M.T.I., Arampath, A.M.C.B., Kandeepan, S., Amaratunga, H.P.K.K.S.: Database optimization using GA for distributed databases. Int. J. Comput. 24(1), 23–27 (2017)Google Scholar
  15. 15.
    Hevner, A.R., Yao, S.B.: Query processing in distributed database system. IEEE Trans. Softw. Eng. 3, 177–187 (1979)zbMATHGoogle Scholar
  16. 16.
    Ceri, S., Pelagatti, G.: Allocation of operations in distributed database access. IEEE Trans. Comput. 2, 119–129 (1982)zbMATHGoogle Scholar
  17. 17.
    Martin, T.P., Lam, K.H., Russell, J.I.: Evaluation of site selection algorithms for distributed query processing. Comput. J. 33(1), 61–70 (1990)MathSciNetGoogle Scholar
  18. 18.
    Sharma, M., Singh, G., Singh, R., Singh, G.: Analysis of DSS queries using entropy-based restricted genetic algorithm. Appl. Math. Inf. Sci. 9(5), 2599 (2015)Google Scholar
  19. 19.
    Sinha, M., Chande, S.V.: Query optimization using GA. Res. J. Inf. Technol. 2(3), 139–144 (2010)Google Scholar
  20. 20.
    Rho, S., March, S.T.: Optimizing distributed join queries: a genetic algorithm approach. Ann. Oper. Res. 71, 199–228 (1997)zbMATHGoogle Scholar
  21. 21.
    Sharma, M., Singh, G., Singh, G., Singh, G.: Analysis of DSS queries in distributed database system using exhaustive and genetic approach. Int. J. Adv. Comput. 36(2), 1 (2013)Google Scholar
  22. 22.
    Sharma, M., Singh, G., Singh, R., Singh, G.: Stochastic analysis of DSS queries for a DDB design. Int. J. Comput. Appl. 83(5), 73 (2013)Google Scholar
  23. 23.
    Kumar, T.V.V., Singh, V.: Distributed query processing plans generation using GA. Int. J. Comput. Theory Eng. 3(1), 38–45 (2011)Google Scholar
  24. 24.
    Sevinç, E., Coşar, A.: An evolutionary genetic algorithm for optimization of distributed database queries. Comput. J. 54(5), 717–725 (2010)zbMATHGoogle Scholar
  25. 25.
    Zhou, Z.: Using heuristics and genetic algorithm for large scale database query optimization. J. Inf. Comput. Sci. 2(4), 261–280 (2007)Google Scholar
  26. 26.
    Mishra, S.K., Pattnaik, S.: Evaluation of cost of plans in multiple dependent queries execution using GA techniques. Int. J. Eng. Technol. 3(2), 179–182 (2011)Google Scholar
  27. 27.
    Saedi, A.K.Z.A., Ghazali, R., Deris, M.M.: Materializing multi-join query optimization for RDBMS using swarm intelligent approach. Int. J. Comput. Inf. Syst. Indus. Manag. Appl. 7, 74–83 (2015)Google Scholar
  28. 28.
    Kolaei, A.A., Ahmadzadeh, M.: The optimization of running queries in relational databases using the ant-colony algorithm. arXiv preprint arXiv:1311.4088 (2013)
  29. 29.
    Kumar, T.V., Arun, B., Kumar, L.: Distributed query plan generation using HBMO. In: International Workshop on Multi-disciplinary Trends in Artificial Intelligence, pp. 293–304. Springer, Heidelberg (2013)Google Scholar
  30. 30.
    Joshi, M., Srivastava, P.R.: Query optimization: an intelligent hybrid approach using Cuckoo and Tabu search. Int. J. Intell. Inf. Technol. 9(1), 40–55 (2013)Google Scholar
  31. 31.
    Fister Jr, I., Yang, X.S., Fister, I., Brest, J., Fister, D.: A brief review of nature-inspired algorithms for optimization. arXiv preprint arXiv:1307.4186 (2013)
  32. 32.
    Mirjalili, S., Lewis, A.: The whale optimization algorithm. Adv. Eng. Softw. 95, 51–67 (2016)Google Scholar
  33. 33.
    Mirjalili, S.: Dragonfly algorithm: a new meta-heuristic optimization technique for solving single-objective, discrete, and multi-objective problems. Neural Comput. Appl. 27(4), 1053–1073 (2016)Google Scholar
  34. 34.
    Mirjalili, S.: Moth-flame optimization algorithm: a novel nature-inspired heuristic paradigm. Knowl. Based Syst. 89, 228–249 (2015)Google Scholar
  35. 35.
    Wang, G.G., Deb, S., Gao, X.Z., Coelho, L.D.S.: A new meta-heuristic optimisation algorithm motivated by elephant herding behaviour. Int. J. Bio-Inspir. Comput. 8(6), 394–409 (2016)Google Scholar
  36. 36.
    Mirjalili, S.: The antlion optimizer. Adv. Eng. Softw. 83, 80–98 (2015)Google Scholar
  37. 37.
    Yazdani, M., Jolai, F.: Lion optimization algorithm (LOA): a nature-inspired metaheuristic algorithm. J. Comput. Des. Eng. 3(1), 24–36 (2016)Google Scholar
  38. 38.
    Abedinia, O., Amjady, N., Ghasemi, A.: A new meta-heuristic algorithm based on shark smell optimization. Complexity 21(5), 97–116 (2016)MathSciNetGoogle Scholar
  39. 39.
    Mirjalili, S., Mirjalili, S.M., Lewis, A.: Grey wolf optimizer. Adv. Eng. Softw. 69, 46–61 (2014)Google Scholar
  40. 40.
    Gandomi, A.H., Alavi, A.H.: Krill herd: a new bio-inspired optimization algorithm. Commun. Nonlinear Sci. Numer. Simul. 17(12), 4831–4845 (2012)MathSciNetzbMATHGoogle Scholar
  41. 41.
    Yang, X.S.: A new meta-heuristic bat-inspired algorithm. In: Cruz, C., González, J.R., Krasnogor, N., Pelta, D.A., Terrazas, G. (eds.) Nature inspired cooperative strategies for optimization (NICSO 2010), pp. 65–74. Springer, Berlin (2010)Google Scholar
  42. 42.
    Yang, X.S., Deb, S.: Engineering optimisation by cuckoo search. Int. J. Math. Modell. Numer. Optim. 1(4), 330–343 (2010)zbMATHGoogle Scholar
  43. 43.
    Mucherino, A., Seref, O.: Monkey search: a novel meta-heuristic search for global optimization. In: AIP Conference Proceedings, AIP, vol. 953, no. 1, pp. 162–173 (2007)Google Scholar
  44. 44.
    Yang, X.S.: Firefly algorithms for multimodal optimization. In: International Symposium on Stochastic Algorithms, pp. 169–178. Springer, Berlin (2009)Google Scholar
  45. 45.
    Chu, S.C., Tsai, P.W., Pan, J.S.: Cat swarm optimization. In: Pacific Rim International Conference on Artificial Intelligence, pp. 854–858. Springer, Berlin (2006)Google Scholar
  46. 46.
    Karaboga, D.: An idea based on honey bee swarm for numerical optimization, vol. 200. Technical report-tr06, Erciyes University, Engineering Faculty, Computer Engineering Department (2005)Google Scholar
  47. 47.
    Li, X.L.: An optimizing method based on autonomous animals: fish-swarm algorithm. Syst. Eng. Theory Pract. 22(11), 32–38 (2002)Google Scholar
  48. 48.
    Geem, Z.W., Kim, J.H., Loganathan, G.V.: A new heuristic optimization algorithm: harmony search. Simulation 76(2), 60–68 (2001)Google Scholar
  49. 49.
    John, Holland: Genetic algorithm. Sci. Am. 267(1), 66–73 (1992)Google Scholar
  50. 50.
    Dorigo, M., Birattari, M.: Ant colony optimization. In: Encyclopaedia of Machine Learning, pp. 36–39. Springer, Boston (2011)Google Scholar
  51. 51.
    Eberhart, R., Kennedy, J.: A new optimizer using particle swarm theory. In: Proceedings of the Sixth International Symposium on Micro Machine and Human Science, 1995. MHS’95, pp. 39–43. IEEE (1995Google Scholar
  52. 52.
    Cornell, D.W., Yu, P.S.: On optimal site assignment for relations in the distributed database environment. IEEE Trans. Softw. Eng. 15(8), 1004–1009 (1989)Google Scholar
  53. 53.
    Mor, J., Kashyap, I., Rathy, R.K.: Analysis of query optimization techniques in databases. Int. J. Comput. Appl. 47(15), 5–9 (2012)Google Scholar
  54. 54.
    Bamnote, G.R., Agrawal, S.S.: Introduction to query processing and optimization. Int. J. 3(7), 53–56 (2013)Google Scholar
  55. 55.
    Gupta, M.K., Chandra, P.: An empirical evaluation of LIKE operator in oracle. Bharati Vidyapeeth’s Inst. Comput. Appl. Manag. 3(2), 351–357 (2011)Google Scholar
  56. 56.
    Kumar, S., Khandelwal, G., Varshney, A., Arora, M.: Cost-based query optimization with heuristics. Int. J. Sci. Eng. Res. 2(9), 1 (2011)Google Scholar
  57. 57.
    Hamdoon, S.H., Gawande, V., Al-Barashdi, A.: Pragmatic approach to query optimization. Int. J. Comput. Appl. 66(7), 32 (2013)Google Scholar
  58. 58.
    Kumar, M., Batra, N., Aggarwal, H.: Cache-based query optimization approach in distributed database. Int. J. Comput. Sci. Issues 9(6), 389–395 (2012)Google Scholar
  59. 59.
    Seema, P.Kaur: Query optimization algorithm based on relational algebra equivalence transformation. Int. J. Eng. Manag. Sci. 4(3), 326–331 (2013)Google Scholar
  60. 60.
    Li, X., Li, D., Gao, H.Z., Yao, L.: Study of query of distributed database based on relation semi-join. In: 2010 International Conference on Computer Design and Applications (ICCDA), IEEE, vol. 1, pp. V1–V134 (2010)Google Scholar
  61. 61.
    Aljanaby, A., Abuelrub, E., Odeh, M.: A survey of distributed query optimization. Int. Arab J. Inf. Technol. 2(1), 48–57 (2005)Google Scholar
  62. 62.
    Kossmann, D.: The state of the art in distributed query processing. ACM Comput. Surv. (CSUR) 32(4), 422–469 (2000)Google Scholar
  63. 63.
    Apers, P.M.G., Hevner, A.R., Yao, S.B.: Optimization algorithms for distributed queries. IEEE Trans. Softw. Eng. 1, 57–68 (1983)Google Scholar
  64. 64.
    Najjar, F., Slimani, Y.: Extension of the one-shot semijoin strategy to minimize data transmission cost in distributed query processing. Inf. Sci. 114(1–4), 1–21 (1999)MathSciNetzbMATHGoogle Scholar
  65. 65.
    Azari, I.: Efficient execution of query in distributed database systems. In: 2010 3rd International Conference on Advanced Computer Theory and Engineering (ICACTE), pp. 428–433 (2010)Google Scholar
  66. 66.
    Thangam, A.R., Peter, S.J.: Efficient processing and optimization of queries with set predicates using Filtered Bitmap Index. Int. J. Comput. Sci. Eng. 5(11), 33–29 (2017)Google Scholar
  67. 67.
    Asghari, K., Mamaghani, A.S., Meybodi, M.R.: An Evolutionary Algorithms for Query Optimization in Database. Innovative Techniques in Instruction, E-Learning, E-Assessment and Education, pp. 249–254. Springer, New York (2008)Google Scholar
  68. 68.
    Butey, P.K., Meshram, S., Sonolikar, R.L.: Query optimization using GA. J. Inf. Technol. Eng. 3(1), 44–51 (2012)Google Scholar
  69. 69.
    Hongxing, L., Bingzhang, L.: A Tree-based genetic algorithm for distributed database. In: Proceedings of the IEEE International Conference, on Automation and Logistics, Qingdao China, pp. 2614–2618 (2008)Google Scholar
  70. 70.
    Barker, K., Jun, D., Alhajj, R.: Genetic algorithm based approach to database vertical partition. J. Intell. Inf. Syst. 26, 167–183 (2006)Google Scholar
  71. 71.
    Golshanara, L., Mohammad, S., Rankoohi, T.R., Shah-Hosseini, H.: A multi-colony ant algorithm for optimizing join queries in distributed database systems. Knowl. Inf. Syst. 39(1), 175–206 (2013)Google Scholar
  72. 72.
    Gomathi, R., Sharmila, D.: A Hybrid Nature Inspired Algorithm for Generating Optimal Query Plan. World Acad. Sci. Eng. Technol. Int. J. Comput. Electr. Autom. Control Inf. Eng. 8(8), 1519–1524 (2014)Google Scholar
  73. 73.
    Padia, S., Khulge, S., Gupta, A., Khadilikar, P.: Query optimization strategies in distributed databases. Int. J. Comput. Sci. Inf. Technol. 6(5), 4228–4234 (2015)Google Scholar
  74. 74.
    Joshi, M., Srivastava, P.R.: Query optimization: an intelligent hybrid approach using cuckoo and tabu search. Int. J. Intell. Inf. Technol. (IJIIT) 9(1), 40–55 (2013)Google Scholar
  75. 75.
    Wagh, A., Nemade, V.: Query optimization using modified ant colony algorithm. Int. J. Comput. Appl. 167(2), 29–33 (2017)Google Scholar
  76. 76.
    Tiwari, P., Chande, S.V.: Optimization of distributed database queries using hybrids of ant colony optimization algorithm. Int. J. Adv. Res. Comput. Sci. Softw. Eng. 3(6), 609–614 (2013)Google Scholar
  77. 77.
    Sharma, M., Singh, G., Singh, R., Singh, J.: Design and analysis of stochastic query optimizer for biobank databases. In: 2015 15th International Conference on Computational Science and Its Applications (ICCSA), pp. 47–51. IEEE (2015)Google Scholar
  78. 78.
    Raushan, Y., Welekar, A.R.: Distributed query optimization using hybrid ant colony algorithm. Int. J. Comput. Sci. Commun. Netw. 5(3), 212–215 (2015)Google Scholar
  79. 79.
    Xu, Z., Tu, Y.C., Wang, X.: PET: reducing database energy cost via query optimization. Proc. VLDB Endow. 5(12), 1954–1957 (2012)Google Scholar
  80. 80.
    Lang, W., Kandhan, R., Patel, J.M.: Rethinking query processing for energy efficiency: slowing down to win the race. IEEE Data Eng. Bull. 34(1), 12–23 (2011)Google Scholar
  81. 81.
    Roukh, A., Bellatreche, L., Tziritas, N., Ordonez, C.: Energy-aware query processing on a parallel database cluster node. In: International Conference on Algorithms and Architectures for Parallel Processing, pp. 260–269. Springer, Cham (2016)Google Scholar
  82. 82.
    Guo, B., Yu, J., Liao, B., Yang, D., Lu, L.: A green framework for DBMS based on energy-aware query optimization and energy-efficient query processing. J. Netw. Comput. Appl. 84, 118–130 (2017)Google Scholar
  83. 83.
    Rosemark, R., Lee, W.C., Urgaonkar, B.: Optimizing energy-efficient query processing in wireless sensor networks. In: 2007 International Conference on Mobile Data Management, pp. 24–29. IEEE (2007)Google Scholar
  84. 84.
    Jamsutkar, K., Patil, V., Meshram, B.B.: Query processing strategies in distributed database. Blue Ocean Res. J. 2(7), 71–77 (2013)Google Scholar
  85. 85.
    Arebi, P., Gonbadipoor, N.: A genetic algorithm for query optimization in database grid by dynamic cost estimation. In: 13th International Conference on Computer Modelling and Simulation, pp. 81–86 (2011)Google Scholar
  86. 86.
    Ghaemi, R., Fard, M., Tabatabaee, H., Sadeghizadeh, M.: Evolutionary query optimization for heterogeneous distributed database systems. Int. J. Comput. Inf. Eng. 2(7), 34–40 (2008)Google Scholar

Copyright information

© Springer-Verlag GmbH Germany, part of Springer Nature 2018

Authors and Affiliations

  1. 1.Department of Computer Science and ApplicationsDAVUJalandharIndia
  2. 2.Department of Computer ScienceGNDUAmritsarIndia

Personalised recommendations