Selection of materialized views using stochastic ranking based Backtracking Search Optimization Algorithm

  • Anjana Gosain
  • Kavita SachdevaEmail author
Original Article


Selection of materialized view plays an important part in structuring decisions effectively in datawarehouse. Materialized view selection (MVS) is recognized as NP-hard and optimization problem, involving disk space and cost constraints. Numerous algorithms exist in literature for selection of materialized views. In this study, authors have proposed stochastic ranking (SR) method, together with Backtracking Search Optimization Algorithm (BSA) for solving MVS problem. The faster exploration and exploitation capabilities of BSA and the ranking method of SR technique for handling constraints are the motivating factors for proposing these two together for MVS problem. Authors have compared results with the constrained evolutionary optimization algorithm proposed by Yu et al. (IEEE Trans Syst Man Cybernet Part C Appl Rev 33(4):458–467, 2003). The proposed method handles the constraints effectively, lessens the total processing cost of query and scales well with problem size.


Aggregation Stochastic Evolutionary Constrained Ranking Optimization Dimension 



  1. Agrawal S, Chaudhuri S, Narasayya VR (2001) Materialized view and index selection tool for microsoft SQL server 2000. In: Proceedings of the special interest group on management of data conference (ACM SIGMOND), p 608Google Scholar
  2. Bezdek JC (2001) What is computational intelligence? In: Computational intelligence lmitating life. IEEE Press, New York, pp 1–12Google Scholar
  3. Chaudhuri S, Narasayya VR (1998) AutoAdmin ‘what-if’ index analysis utility. In: Proceedings of the special interest group on management of data conference ACM SIGMOND, pp 367–378Google Scholar
  4. Chaudhuri S, Krishnamurthy R, Potamianos S, Shim K (1995) Optimizing queries with materialized views. In: Proceedings of the 11th international conference on data engineering, pp 190–200Google Scholar
  5. Choi CH, Yu JX, Lu H (2004) A simple but effective dynamic materialized view caching. In: International conference on web-age information management. Springer, pp 147–156Google Scholar
  6. Civicioglu P (2013) Backtracking search optimization algorithm for numerical optimization problems. Appl Math Comput 219:8121–8144MathSciNetzbMATHGoogle Scholar
  7. Coello CAC (2002) Theoretical and numerical constraint-handling techniques used with evolutionary algorithms: a survey of the state of the art. In: Computer methods in applied mechanics and engineering. Elsevier, pp 1245–1287Google Scholar
  8. Coello CAC (2012) Constraint-handling techniques used with evolutionary algorithms. In: Genetic and evolutionary computation conference GECCO’12. ACMGoogle Scholar
  9. Gosain A, Heena (2016) Materialized cube selection using particle swarm optimization algorithm. In: 7th international conference on communication, computing and virtualization, vol 79. Elsevier, pp 2–7Google Scholar
  10. Gray J, Layman A, Bosworth A, Pirahesh H (1997) Data cube: a relational aggregation operator generalizing group-by, cross-tabs and subtotals. Data Min Knowl Discov 1(1):29–53CrossRefGoogle Scholar
  11. Gupta H (1997) Selection of views to materialize in a data warehouse. In: Proceedings of the 6th international conference on database theory. Springer, pp 98–112Google Scholar
  12. Gupta H, Mumick IS (1999) Selection of views to materialize under a maintenance cost constraint. In: Proceedings of the 7th international conference on database theory. Springer, pp 453–470Google Scholar
  13. Gupta H, Mumick IS (2005) Selection of views to materialize in a data warehouse. IEEE Trans Knowl Data Eng 17(1):24–43CrossRefGoogle Scholar
  14. Gupta H, Harinarayan V, Rajaraman A, Ullman JD (1997) Index selection for OLAP. In: Proceedings of 13th international conference on data engineering, pp 208–219Google Scholar
  15. Halevy AY (2001) Answering queries using views: a survey. VLDB J 10(4):270–294CrossRefzbMATHGoogle Scholar
  16. Han J, Kamber M (2001) Data mining: concepts and techniques. Morgan Kaufman, San FranciscozbMATHGoogle Scholar
  17. Harinarayan V, Rajaraman A, Ullman JD (1996) Implementing data cubes efficiently. In: Proceedings of the 1996 ACM SIGMOD international conference on management of data, Montreal, Que., Canada, pp 205–216Google Scholar
  18. Horng JT, Chang YJ, Liu BJ (2003) Applying evolutionary algorithms to materialized view selection in a data warehouse. Soft Comput 7(8):574–581CrossRefGoogle Scholar
  19. Hung MC, Huang ML, Yang DL, Hsueh NL (2007) Efficient approaches for materialized views selection in a data warehouse. Inf Sci 177(6):1333–1348CrossRefGoogle Scholar
  20. Jain H, Gosain A (2012) A comprehensive study of view maintenance approaches in data warehousing evolution. In: ACM SIGSOFT software engineering notes, vol 37, no 5, pp 1–8Google Scholar
  21. Kotidis Y, Roussopoulos N (1999) Dynamat: a dynamic view management system for data warehouses. In: ACM SIGMOD record, vol 28. ACM, pp 371–382Google Scholar
  22. Kumar TV, Arun B (2015) Materialized view selection using improvement based bee colony optimization. Int J Softw Sci Comput Intell 7(4):35–61CrossRefGoogle Scholar
  23. Lawrence M, Rau-Chaplin A (2006) Dynamic view selection for OLAP. In: International conference on data warehousing and knowledge discovery. Springer, pp 33–44Google Scholar
  24. Lee M, Hammer J (2001) Speeding up materialized view selection in data warehouses using a randomized algorithm. Int J Cooper Inf Syst 10(3):327–353CrossRefGoogle Scholar
  25. Lin WY, Kuo IC (2004) A genetic selection algorithm for OLAP data cubes. Knowl Inf Syst 6(1):83–102CrossRefGoogle Scholar
  26. Mami I, Coletta R, Bellahsene Z (2011) Modeling view selection as a constraint satisfaction problem. In: Hameurlain A., Liddle SW, Schewe KD, Zhou X (eds) Database and expert systems applications, vol 6861. Springer, Berlin, pp 396–410CrossRefGoogle Scholar
  27. Morse S, Isaac D (1998) Parallel systems in the data warehouse. Prentice Hall, Upper saddle RiverGoogle Scholar
  28. O’Neil PE, O’Neil EJ, Chen X (2007) The star schema benchmark (SSB). PatGoogle Scholar
  29. Runarsson TP, Yao X (2000) Stochastic ranking for constrained evolutionary optimization. IEEE Trans Evolut Comput 4:284–294CrossRefGoogle Scholar
  30. Shukla A, Deshpande P, Naughton J F (1998) Materialized view selection for multidimensional datasets. In: Proceedings of 24th international conference on very large data bases, pp 488–499Google Scholar
  31. Sohrabi MK, Azgomi H (2019) Evolutionary game theory approach to materialized view selection in datawarehouse. Knowl Based Syst 163:558–571CrossRefGoogle Scholar
  32. Sun X, Ziqiang W (2009) An efficient materialized views selection algorithm based on PSO. In: Proceedings of the international workshop on intelligent systems and applications, ISA 2009, Wuhan, ChinaGoogle Scholar
  33. Talebi ZA, Chirkova R, Fathi Y (2013) An integer programming approach for the view and index selection problem. Data Knowl Eng 83:111–125CrossRefGoogle Scholar
  34. Tamiozzo AS, Ale JM (2014) A solution to the materialized view selection problem in data warehousing. In: XX Congreso Argentino de Ciencias de la Computación (Buenos Aires, 2014)Google Scholar
  35. Yang J, Karlapalem K, Li Q (1997) Algorithms for materialized view design in data warehousing environment. In: VLDB, Proceedings of the 23rd international conference on very large data bases, vol 97, pp 136–145Google Scholar
  36. Yu JX, Yao X, Choi CH, Gou G (2003) Materialized view selection as constrained evolutionary optimization. IEEE Trans Syst Man Cybernet Part C Appl Rev 33(4):458–467CrossRefGoogle Scholar
  37. Yu D, Dou W, Zhu Z, Wang J (2015) Materialized view selection based on adaptive genetic algorithm and its implementation with Apache Hive. Int J Comput Intell Syst 8(6):1091–1102Google Scholar
  38. Zhang C, Yao X, Yang J (2001) An evolutionary approach to materialized views selection in a data warehouse environment. IEEE Trans Syst Man Cybernet Part C Appl Rev 31(3):282–294CrossRefGoogle Scholar

Copyright information

© The Society for Reliability Engineering, Quality and Operations Management (SREQOM), India and The Division of Operation and Maintenance, Lulea University of Technology, Sweden 2019

Authors and Affiliations

  1. 1.Guru Gobind Singh Indraprastha UniversityNew DelhiIndia
  2. 2.Shree Guru Gobind Singh Tricentenary UniversityGurugramIndia

Personalised recommendations