Skip to main content

Advertisement

SpringerLink
Log in

A review of different cost-based distributed query optimizers

  • Review
  • Published:
Progress in Artificial Intelligence Aims and scope Submit manuscript

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.

This is a preview of subscription content, log in via an institution to check access.

Access this article

Log in via an institution

Price excludes VAT (USA)
Tax calculation will be finalised during checkout.

Instant access to the full article PDF.

Institutional subscriptions

Fig. 1
Fig. 2
Fig. 3
Fig. 4
Fig. 5
Fig. 6
Fig. 7
Fig. 8
Fig. 9

Similar content being viewed by others

References

  1. Gorla, N., Song, S.K.: Sub-query allocation in DDB using GA. J. Comput. Sci. Technol. 10, 31–37 (2010)

    Google Scholar 

  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)

  3. Ozsu, M.T., Valduriez, P.: Principles of Distributed Database Systems, 2nd edn. Pearson Education, New York City (2009)

    Google Scholar 

  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)

  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)

    Article  Google Scholar 

  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)

    Article  Google Scholar 

  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. 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. Jarke, M., Koch, J.: Query optimization in database systems. ACM Comput. Surv. (CsUR) 16(2), 111–152 (1984)

    Article  MathSciNet  MATH  Google Scholar 

  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. 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. 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. 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)

  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. Hevner, A.R., Yao, S.B.: Query processing in distributed database system. IEEE Trans. Softw. Eng. 3, 177–187 (1979)

    Article  MATH  Google Scholar 

  16. Ceri, S., Pelagatti, G.: Allocation of operations in distributed database access. IEEE Trans. Comput. 2, 119–129 (1982)

    Article  MATH  Google Scholar 

  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)

    Article  MathSciNet  Google Scholar 

  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. Sinha, M., Chande, S.V.: Query optimization using GA. Res. J. Inf. Technol. 2(3), 139–144 (2010)

    Google Scholar 

  20. Rho, S., March, S.T.: Optimizing distributed join queries: a genetic algorithm approach. Ann. Oper. Res. 71, 199–228 (1997)

    Article  MATH  Google Scholar 

  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. 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. Kumar, T.V.V., Singh, V.: Distributed query processing plans generation using GA. Int. J. Comput. Theory Eng. 3(1), 38–45 (2011)

    Article  Google Scholar 

  24. Sevinç, E., Coşar, A.: An evolutionary genetic algorithm for optimization of distributed database queries. Comput. J. 54(5), 717–725 (2010)

    Article  MATH  Google Scholar 

  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. 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)

    Article  Google Scholar 

  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. 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. 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)

  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)

    Article  Google Scholar 

  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. Mirjalili, S., Lewis, A.: The whale optimization algorithm. Adv. Eng. Softw. 95, 51–67 (2016)

    Article  Google Scholar 

  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)

    Article  Google Scholar 

  34. Mirjalili, S.: Moth-flame optimization algorithm: a novel nature-inspired heuristic paradigm. Knowl. Based Syst. 89, 228–249 (2015)

    Article  Google Scholar 

  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)

    Article  Google Scholar 

  36. Mirjalili, S.: The antlion optimizer. Adv. Eng. Softw. 83, 80–98 (2015)

    Article  Google Scholar 

  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. Abedinia, O., Amjady, N., Ghasemi, A.: A new meta-heuristic algorithm based on shark smell optimization. Complexity 21(5), 97–116 (2016)

    Article  MathSciNet  Google Scholar 

  39. Mirjalili, S., Mirjalili, S.M., Lewis, A.: Grey wolf optimizer. Adv. Eng. Softw. 69, 46–61 (2014)

    Article  Google Scholar 

  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)

    Article  MathSciNet  MATH  Google Scholar 

  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)

    Chapter  Google Scholar 

  42. Yang, X.S., Deb, S.: Engineering optimisation by cuckoo search. Int. J. Math. Modell. Numer. Optim. 1(4), 330–343 (2010)

    MATH  Google Scholar 

  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)

  44. Yang, X.S.: Firefly algorithms for multimodal optimization. In: International Symposium on Stochastic Algorithms, pp. 169–178. Springer, Berlin (2009)

  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)

  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)

  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. Geem, Z.W., Kim, J.H., Loganathan, G.V.: A new heuristic optimization algorithm: harmony search. Simulation 76(2), 60–68 (2001)

    Article  Google Scholar 

  49. John, Holland: Genetic algorithm. Sci. Am. 267(1), 66–73 (1992)

    Article  Google Scholar 

  50. Dorigo, M., Birattari, M.: Ant colony optimization. In: Encyclopaedia of Machine Learning, pp. 36–39. Springer, Boston (2011)

  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 (1995

  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)

    Article  Google Scholar 

  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. Bamnote, G.R., Agrawal, S.S.: Introduction to query processing and optimization. Int. J. 3(7), 53–56 (2013)

    Google Scholar 

  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. 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. Hamdoon, S.H., Gawande, V., Al-Barashdi, A.: Pragmatic approach to query optimization. Int. J. Comput. Appl. 66(7), 32 (2013)

    Google Scholar 

  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. 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. 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)

  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. Kossmann, D.: The state of the art in distributed query processing. ACM Comput. Surv. (CSUR) 32(4), 422–469 (2000)

    Article  Google Scholar 

  63. Apers, P.M.G., Hevner, A.R., Yao, S.B.: Optimization algorithms for distributed queries. IEEE Trans. Softw. Eng. 1, 57–68 (1983)

    Article  Google Scholar 

  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)

    Article  MathSciNet  MATH  Google Scholar 

  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)

  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. 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. Butey, P.K., Meshram, S., Sonolikar, R.L.: Query optimization using GA. J. Inf. Technol. Eng. 3(1), 44–51 (2012)

    Google Scholar 

  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)

  70. Barker, K., Jun, D., Alhajj, R.: Genetic algorithm based approach to database vertical partition. J. Intell. Inf. Syst. 26, 167–183 (2006)

    Article  Google Scholar 

  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)

    Article  Google Scholar 

  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. 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. 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)

    Article  Google Scholar 

  75. Wagh, A., Nemade, V.: Query optimization using modified ant colony algorithm. Int. J. Comput. Appl. 167(2), 29–33 (2017)

    Google Scholar 

  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. 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)

  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. Xu, Z., Tu, Y.C., Wang, X.: PET: reducing database energy cost via query optimization. Proc. VLDB Endow. 5(12), 1954–1957 (2012)

    Article  Google Scholar 

  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. 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)

  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)

    Article  Google Scholar 

  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)

  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. 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)

  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 

Download references

Author information

Authors and Affiliations

  1. Department of Computer Science and Applications, DAVU, Jalandhar, India

    Manik Sharma

  2. Department of Computer Science, GNDU, Amritsar, Punjab, India

    Gurvinder Singh & Rajinder Singh

Authors
  1. Manik Sharma
    View author publications

    You can also search for this author in PubMed Google Scholar

  2. Gurvinder Singh
    View author publications

    You can also search for this author in PubMed Google Scholar

  3. Rajinder Singh
    View author publications

    You can also search for this author in PubMed Google Scholar

Corresponding author

Correspondence to Manik Sharma.

Rights and permissions

Reprints and permissions

About this article

Check for updates. Verify currency and authenticity via CrossMark

Cite this article

Sharma, M., Singh, G. & Singh, R. A review of different cost-based distributed query optimizers. Prog Artif Intell 8, 45–62 (2019). https://doi.org/10.1007/s13748-018-0154-8

Download citation

  • Received:

  • Accepted:

  • Published:

  • Issue Date:

  • DOI: https://doi.org/10.1007/s13748-018-0154-8

Keywords

Search

Navigation