Skip to main content
SpringerLink
Log in

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

  • Original Article
  • Published:
International Journal of System Assurance Engineering and Management Aims and scope Submit manuscript

Abstract

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.

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
Algorithm 1
Algorithm 2
Fig. 2
Fig. 3
Fig. 4
Fig. 5
Fig. 6
Fig. 7
Fig. 8
Fig. 9

Similar content being viewed by others

References

  • 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 608

  • Bezdek JC (2001) What is computational intelligence? In: Computational intelligence lmitating life. IEEE Press, New York, pp 1–12

  • 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–378

    Article  Google Scholar 

  • 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–200

  • 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–156

  • Civicioglu P (2013) Backtracking search optimization algorithm for numerical optimization problems. Appl Math Comput 219:8121–8144

    MATH  MathSciNet  Google Scholar 

  • 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–1287

  • Coello CAC (2012) Constraint-handling techniques used with evolutionary algorithms. In: Genetic and evolutionary computation conference GECCO’12. ACM

  • 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–7

  • 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–53

    Article  Google Scholar 

  • 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–112

  • 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–470

  • Gupta H, Mumick IS (2005) Selection of views to materialize in a data warehouse. IEEE Trans Knowl Data Eng 17(1):24–43

    Article  Google Scholar 

  • Gupta H, Harinarayan V, Rajaraman A, Ullman JD (1997) Index selection for OLAP. In: Proceedings of 13th international conference on data engineering, pp 208–219

  • Halevy AY (2001) Answering queries using views: a survey. VLDB J 10(4):270–294

    Article  Google Scholar 

  • Han J, Kamber M (2001) Data mining: concepts and techniques. Morgan Kaufman, San Francisco

    MATH  Google Scholar 

  • 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–216

  • Horng JT, Chang YJ, Liu BJ (2003) Applying evolutionary algorithms to materialized view selection in a data warehouse. Soft Comput 7(8):574–581

    Article  Google Scholar 

  • Hung MC, Huang ML, Yang DL, Hsueh NL (2007) Efficient approaches for materialized views selection in a data warehouse. Inf Sci 177(6):1333–1348

    Article  Google Scholar 

  • 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–8

    Google Scholar 

  • Kotidis Y, Roussopoulos N (1999) Dynamat: a dynamic view management system for data warehouses. In: ACM SIGMOD record, vol 28. ACM, pp 371–382

  • Kumar TV, Arun B (2015) Materialized view selection using improvement based bee colony optimization. Int J Softw Sci Comput Intell 7(4):35–61

    Article  Google Scholar 

  • Lawrence M, Rau-Chaplin A (2006) Dynamic view selection for OLAP. In: International conference on data warehousing and knowledge discovery. Springer, pp 33–44

  • 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–353

    Article  Google Scholar 

  • Lin WY, Kuo IC (2004) A genetic selection algorithm for OLAP data cubes. Knowl Inf Syst 6(1):83–102

    Article  Google Scholar 

  • 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–410

    Chapter  Google Scholar 

  • Morse S, Isaac D (1998) Parallel systems in the data warehouse. Prentice Hall, Upper saddle River

    Google Scholar 

  • O’Neil PE, O’Neil EJ, Chen X (2007) The star schema benchmark (SSB). Pat

  • Runarsson TP, Yao X (2000) Stochastic ranking for constrained evolutionary optimization. IEEE Trans Evolut Comput 4:284–294

    Article  Google Scholar 

  • 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–499

  • Sohrabi MK, Azgomi H (2019) Evolutionary game theory approach to materialized view selection in datawarehouse. Knowl Based Syst 163:558–571

    Article  Google Scholar 

  • 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, China

  • Talebi ZA, Chirkova R, Fathi Y (2013) An integer programming approach for the view and index selection problem. Data Knowl Eng 83:111–125

    Article  Google Scholar 

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

  • 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–145

  • 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–467

    Article  Google Scholar 

  • 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–1102

    Google Scholar 

  • 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–294

    Article  Google Scholar 

Download references

Author information

Authors and Affiliations

  1. Guru Gobind Singh Indraprastha University, New Delhi, 110078, India

    Anjana Gosain

  2. Shree Guru Gobind Singh Tricentenary University, Gurugram, 122505, India

    Kavita Sachdeva

Authors
  1. Anjana Gosain
    View author publications

    You can also search for this author in PubMed Google Scholar

  2. Kavita Sachdeva
    View author publications

    You can also search for this author in PubMed Google Scholar

Corresponding author

Correspondence to Kavita Sachdeva.

Additional information

Publisher's Note

Springer Nature remains neutral with regard to jurisdictional claims in published maps and institutional affiliations.

Rights and permissions

Reprints and permissions

About this article

Check for updates. Verify currency and authenticity via CrossMark

Cite this article

Gosain, A., Sachdeva, K. Selection of materialized views using stochastic ranking based Backtracking Search Optimization Algorithm. Int J Syst Assur Eng Manag 10, 801–810 (2019). https://doi.org/10.1007/s13198-019-00812-x

Download citation

  • Received:

  • Revised:

  • Published:

  • Issue Date:

  • DOI: https://doi.org/10.1007/s13198-019-00812-x

Keywords

Search

Navigation