Applied Intelligence

, Volume 49, Issue 11, pp 3965–3989 | Cite as

A novel coral reefs optimization algorithm for materialized view selection in data warehouse environments

  • Hossein Azgomi
  • Mohammad Karim SohrabiEmail author


High response time of analytical queries is one of the most challenging issues of data warehouses. Complicated nature of analytical queries and enormous volume of data are the most important reasons of this high response time. The aim of materialized view selection is to reduce the response time of these analytical queries. For this purpose, the search space is firstly constructed by producing the set of all possible views based on given queries and then, the (semi-) optimal set of materialized views will be selected so that the queries can be answered at the lowest cost using them. Various materialized view selection methods have been proposed in the literature, most of which are randomized methods due to the time-consuming nature of this problem. Randomized view selection methods choose a semi-optimal set of proper views for materialization in an appropriate time using one or a combination of some meta-heuristic(s). In this paper, a novel coral reefs optimization-based method is introduced for materialized view selection in a data warehouse. Coral reefs optimization algorithm is an optimization method that solves problems by simulating the coral behaviors for placement and growth in reefs. In the proposed method, each solution of the problem is considered as a coral, which is always trying to be placed and grow in the reefs. In each step, special operators of the coral reefs optimization algorithm are applied on the solutions. After several steps, better solutions are more likely to survive and grow on the reefs. The best solution is finally chosen as the final solution of the problem. The practical evaluations of the proposed method show that this method offers higher quality solutions than other similar random methods in terms of coverage rate of queries.


Materialized view selection Coral reefs optimization algorithm Data warehouse Multiple view processing plan Randomized algorithms 



  1. 1.
    Chandra P, Gupta MK (2018) Comprehensive survey on data warehousing research. Int J Inf Technol 10(2):217–224Google Scholar
  2. 2.
    Inmon WH (2005) Building the data warehouse. Wiley, New YorkGoogle Scholar
  3. 3.
    Sohrabi MK, Ghods V (2016) Materialized view selection for a data warehouse using frequent Itemset mining. JCP 11(2):140–148CrossRefGoogle Scholar
  4. 4.
    Dhote CA, Ali MS (2009) Materialized view selection in data warehousing: a survey. J Appl Sci 9(3):401–414CrossRefGoogle Scholar
  5. 5.
    Harinarayan V, Rajaraman A, Ullman JD (1996) Implementing data cubes efficiently. ACM SIGMOD Rec 25(2):205–216CrossRefGoogle Scholar
  6. 6.
    Roy P, Seshadri S, Sudarshan S, Bhobe S (2000) Efficient and extensible algorithms for multi query optimization. In: ACM SIGMOD Record (Vol. 29, No. 2, p 249–260). ACMGoogle Scholar
  7. 7.
    Yang J, Karlapalem K, Li Q (1997) Algorithms for materialized view design in data warehousing environment. In: VLDB (Vol. 97, p 25–29)Google Scholar
  8. 8.
    Sohrabi MK, Azgomi H (2018) A survey on the combined use of optimization methods and game theory. Arch Comput Meth Eng.
  9. 9.
    Salcedo-Sanz S, Del Ser J, Landa-Torres I, Gil-López S, Portilla-Figueras JA (2014) The coral reefs optimization algorithm: a novel metaheuristic for efficiently solving optimization problems. Sci World J.
  10. 10.
    Ficco M, Esposito C, Palmieri F, Castiglione A (2018) A coral-reefs and game theory-based approach for optimizing elastic cloud resource allocation. Futur Gener Comput Syst 78(1):343–352CrossRefGoogle Scholar
  11. 11.
    Salcedo-Sanz S, Pastor-Sánchez A, Prieto L, Blanco-Aguilera A, García-Herrera R (2014) Feature selection in wind speed prediction systems based on a hybrid coral reefs optimization–extreme learning machine approach. Energy Convers Manag 87:10–18CrossRefGoogle Scholar
  12. 12.
    Yan C, Ma J, Luo H, Patel A (2019) Hybrid binary coral reefs optimization algorithm with simulated annealing for feature selection in high-dimensional biomedical datasets. Chemom Intell Lab Syst 184:102–111CrossRefGoogle Scholar
  13. 13.
    Durán-Rosal AM, Gutiérrez PA, Salcedo-Sanz S, Hervás-Martínez C (2018) A statistically-driven coral reef optimization algorithm for optimal size reduction of time series. Appl Soft Comput 63:139–153CrossRefGoogle Scholar
  14. 14.
    Tsai C-W, Chang W-Y, Wang Y-C, Chen H (2019) A high-performance parallel coral reef optimization for data clustering. Soft Comput.
  15. 15.
    Bermejo E, Chica M, Damas S, Salcedo-Sanz S, Cordón O (2018) Coral reef optimization with substrate layers for medical image registration. Swarm Evol Comput 42:138–159CrossRefGoogle Scholar
  16. 16.
    Gupta H (1997) Selection of views to materialize in a data warehouse. In: Database theory—ICDT'97. Springer, Berlin Heidelberg, pp 98–112CrossRefGoogle Scholar
  17. 17.
    Mistry H, Roy P, Sudarshan S, Ramamritham K (2001) Materialized view selection and maintenance using multi-query optimization. In: ACM SIGMOD Record (Vol. 30, No. 2, p 307–318). ACMGoogle Scholar
  18. 18.
    Sohrabi MK, Azgomi H (2017) TSGV: a table-like structure-based greedy method for materialized view selection in data warehouses. Turk J Electr Eng Comput Sci 25(4):3175–3187CrossRefGoogle Scholar
  19. 19.
    Theodoratos D, Ligoudistianos S, Sellis T (2001) View selection for designing the global data warehouse. Data Knowl Eng 39(3):219–240CrossRefzbMATHGoogle Scholar
  20. 20.
    Wu X, Theodoratos D, Wang WH, Sellis T (2013) Optimizing XML queries: bitmapped materialized views vs. indexes. Inf Syst 38(6):863–884CrossRefGoogle Scholar
  21. 21.
    Katsifodimos A, Manolescu I, Vassalos V (2012) Materialized view selection for XQuery workloads. In Proceedings of the 2012 ACM SIGMOD International Conference on Management of Data. ACM, p 565–576Google Scholar
  22. 22.
    Afrati F, Damigos M, Gergatsoulis M (2014) On solving efficiently the view selection problem under bag and bag-set semantics. Inf Syst 42:153–176CrossRefzbMATHGoogle Scholar
  23. 23.
    Letrache K, El Beggar O, Ramdani M (2018) OLAP cube partitioning based on association rules method. Appl Intell 49(2):420–434CrossRefGoogle Scholar
  24. 24.
    Bouakkaz M, Ouinten Y, Loudcher S, Fournier-Viger P (2018) Efficiently mining frequent itemsets applied for textual aggregation. Appl Intell 48(4):1013–1019CrossRefGoogle Scholar
  25. 25.
    Sohrabi MK, Azgomi H (2019) Finding similar documents using frequent pattern mining methods. Int J Uncertainty Fuzziness Knowledge Based Syst 27(1):73–96CrossRefGoogle Scholar
  26. 26.
    Aouiche K, Jouve PE, Darmont J (2006) Clustering-based materialized view selection in data warehouses. In: East European conference on advances in databases and information systems. Springer, Berlin Heidelberg, pp 81–95CrossRefGoogle Scholar
  27. 27.
    Aouiche K, Darmont J (2009) Data mining-based materialized view and index selection in data warehouses. J Intell Inf Syst 33(1):65–93CrossRefGoogle Scholar
  28. 28.
    Sohrabi MK, Azgomi H (2019) A survey on the combined use of optimization methods and game theory. Arch Comput Meth Eng.
  29. 29.
    Azgomi H, Sohrabi MK (2018) A game theory based framework for materialized view selection in data warehouses. Eng Appl Artif Intell 71:125–137CrossRefGoogle Scholar
  30. 30.
    Zhang C, Yang J (1999) Genetic algorithm for materialized view selection in data warehouse environments. In: Data warehousing and knowledge discovery. Springer, Berlin Heidelberg, pp 116–125Google Scholar
  31. 31.
    Lee M, Hammer J (2001) Speeding up materialized view selection in data warehouses using a randomized algorithm. Int J Coop Inf Syst 10(03):327–353CrossRefGoogle Scholar
  32. 32.
    Yu JX, Yao X, Choi CH, Gou G (2003) Materialized view selection as constrained evolutionary optimization. IEEE Trans Syst Man Cybern Part C Appl Rev 33(4):458–467CrossRefGoogle Scholar
  33. 33.
    Vijay Kumar TV, Kumar S (2012) Materialized view selection using genetic algorithm. Contemporary Computing, IC3: International Conference on Contemporary Computing, p 225–237Google Scholar
  34. 34.
    Kalnis P, Mamoulis N, Papadias D (2002) View selection using randomized search. Data Knowl Eng 42(1):89–111CrossRefzbMATHGoogle Scholar
  35. 35.
    Derakhshan R, Dehne FK, Korn O, Stantic B (2006) Simulated annealing for materialized view selection in data warehousing environment. In: Databases and applications, pp 89–94Google Scholar
  36. 36.
    Kumar, T. V., & Kumar, S. (2012). Materialized view selection using simulated annealing. In: BDA. p 168–179Google Scholar
  37. 37.
    Song X, Gao L (2010) An ant colony based algorithm for optimal selection of materialized view. In: Intelligent Computing and Integrated Systems (ICISS), 2010 International Conference on. IEEE, p 534–536Google Scholar
  38. 38.
    Sun X, Wang Z (2009). An efficient materialized views selection algorithm based on PSO. In: Intelligent Systems and Applications, 2009. ISA 2009. International Workshop on. IEEE, p 1–4Google Scholar
  39. 39.
    Li X, Qian X, Jiang J, Wang Z (2010). Shuffled frog leaping algorithm for materialized views selection. In: Education Technology and Computer Science (ETCS), 2010 Second International Workshop on (Vol. 3). IEEE, p 7–10Google Scholar
  40. 40.
    Kumar TV, Arun B (2015) Materialized view selection using improvement based bee colony optimization. International Journal of Software Science and Computational Intelligence 7(4):35–61CrossRefGoogle Scholar
  41. 41.
    Arun B, Kumar TV (2017) Materialized view selection using artificial bee colony optimization. Int J Intell Inf Technol 13(1):26–49CrossRefGoogle Scholar
  42. 42.
    Vijay Kumar TV, Kumar S (2014) Materialized view selection using differential evolution. Int J Innov Comput Appl 6(2):102–113CrossRefGoogle Scholar
  43. 43.
    Zhou L, Geng H, Xu M (2011) An improved algorithm for materialized view selection. J Comput 6(1):130–138Google Scholar
  44. 44.
    Phuboon-ob J, Auepanwiriyakul R (2007) Selecting materialized views using two-phase optimization with multiple view processing plan. World Academy of Science, Engineering and Technology, 27Google Scholar
  45. 45.
    Suchyukorn B, Auepanwiriyakul R (2013) Dynamic materialized view selection using 2PO based on re-optimized multiple view processing plan. International Journal of Advancements in Computing Technology 5(14):150Google Scholar
  46. 46.
    Gosain A, Sachdeva K (2018) Materialized view selection using backtracking search optimization algorithm. In: Intelligent engineering informatics. Springer, Singapore, pp 241–251CrossRefGoogle Scholar
  47. 47.
    Zhang C, Yao X, Yang J (2001) An evolutionary approach to materialized views selection in a data warehouse environment. IEEE Trans Syst Man Cybern Part C Appl Rev 31(3):282–294CrossRefGoogle Scholar
  48. 48.
    Molina D, LaTorre A, Herrera F (2018) An insight into bio-inspired and evolutionary algorithms for global optimization: review, analysis, and lessons learnt over a decade of competitions. Cogn Comput 10(4):517–544CrossRefGoogle Scholar
  49. 49.
    Srivastava S, Sahana SK (2017) Nested hybrid evolutionary model for traffic signal optimization. Appl Intell 46(1):113–123CrossRefGoogle Scholar
  50. 50.
    Cheng F, Fu G, Zhang X, Qiu J (2019) Multi-objective evolutionary algorithm for optimizing the partial area under the ROC curve. Knowl-Based Syst 170:61–69CrossRefGoogle Scholar
  51. 51.
    Ramírez A, Romero JR, Ventura S (2018) Interactive multi-objective evolutionary optimization of software architectures. Inf Sci 463–464:92–109CrossRefMathSciNetGoogle Scholar
  52. 52.
    Sohrabi MK, Azgomi H (2019) Evolutionary game theory approach to materialized view selection in data warehouses. Knowl-Based Syst 163:558–571CrossRefGoogle Scholar
  53. 53.
    Kumar S, Kumar TVV (2018) A novel quantum-inspired evolutionary view selection algorithm. Sādhanā 43:166CrossRefGoogle Scholar
  54. 54.
    Goswami R, Bhattacharyya DK, Dutta M (2017) Materialized view selection using evolutionary algorithm for speeding up big data query processing. J Intell Inf Syst 49(3):407–433CrossRefGoogle Scholar
  55. 55.
    Sohrabi MK, Barforoush AA (2013) Parallel frequent itemset mining using systolic arrays. Knowl-Based Syst 37:462–471CrossRefGoogle Scholar
  56. 56.
    Sohrabi MK (2018) A gossip based information fusion protocol for distributed frequent itemset mining. Enterp Inform Syst 12(6):674–694CrossRefGoogle Scholar
  57. 57.
    Sohrabi MK, Taheri N (2018) A haoop-based parallel mining of frequent itemsets using N-lists. J Chin Inst Eng 41(3):229–238CrossRefGoogle Scholar
  58. 58.
    Sohrabi MK, Azgomi H (2017) Parallel set similarity join on big data based on locality-sensitive hashing. Sci Comput Program 145:1–12CrossRefGoogle Scholar
  59. 59.
    Golov N, Rönnbäck L (2017) Big data normalization for massively parallel processing databases. Comput Stand Inter 54(2):86–93CrossRefGoogle Scholar
  60. 60.
    Wang H, Qin X, Zhou X, Li F, Qin Z, Zhu Q, Wang S (2015) Efficient query processing framework for big data warehouse: an almost join-free approach. Front Comp Sci 9(2):224–236CrossRefMathSciNetGoogle Scholar
  61. 61.
    Sebaa A, Chikh F, Nouicer A, Tari A (2018) Medical big data warehouse: architecture and system design, a case study: improving healthcare resources distribution. J Med Syst 42:59. CrossRefGoogle Scholar
  62. 62.
    Zhang C, Bi J, Xu S, Ramentol E, Fan G, Qiao B, Fujita H (2019) Multi-imbalance: an open-source software for multi-class imbalance learning. Knowl-Based Syst.
  63. 63.
    Fujita H, Cimr D (2019) Computer aided detection for fibrillations and flutters using deep convolutional neural network. Inf Sci 486:231–239CrossRefGoogle Scholar
  64. 64.
    Zhang C, Liu C, Zhang X, Almpanidis G (2017) An up-to-date comparison of state-of-the-art classification algorithms. Expert Syst Appl 82:128–150CrossRefGoogle Scholar
  65. 65.
    Hemmatian F, Sohrabi MK (2017) A survey on classification techniques for opinion mining and sentiment analysis. Artif Intell Rev.
  66. 66.
    Salcedo-Sanz S, Pastor-Sánchez A, Gallo-Marazuela D, Portilla-Figueras A (2013) A novel coral reefs optimization algorithm for multi-objective problems. In: International conference on intelligent data engineering and automated learning. Springer, Berlin, Heidelberg, pp 326–333Google Scholar
  67. 67.
    Sohrabi MK, Azgomi H RTLTDS dataset. Available at
  68. 68.
  69. 69.
    Cuevas A, Febrero M, Fraiman R (2004) An anova test for functional data. Comput Stat Data Anal 47(1):111–122CrossRefMathSciNetzbMATHGoogle Scholar
  70. 70.
    Kim TK (2015) T test as a parametric statistic. Korean J Anesthesiol 68(6):540–546CrossRefGoogle Scholar

Copyright information

© Springer Science+Business Media, LLC, part of Springer Nature 2019

Authors and Affiliations

  1. 1.Young Researchers and Elite Club, Rasht BranchIslamic Azad UniversityRashtIran
  2. 2.Department of Computer Engineering, Semnan BranchIslamic Azad UniversitySemnanIran

Personalised recommendations