Skip to main content

Advertisement

SpringerLink
Log in

Multi-objective materialized view selection using NSGA-II

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

Abstract

Data warehouse is constructed with the purpose of supporting decision making. Decision making queries, being long and complex, consume a lot of time in processing against a continuously growing data warehouse. View materialization is one of the alternative ways of improving the response time of such analytical or decision making queries. This involves selection and materialization of views that minimize the analytical query response times while adhering to the resource constraints. This is referred to as the view selection problem, which is a NP-Hard problem. The view selection problem is concerned with simultaneously minimizing the cost of evaluating materialized and non-materialized views. This being a bi-objective optimization problem is addressed using NSGA-II in this paper. The proposed approach aims to achieve an acceptable trade-off between the afore-mentioned two objectives.

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
Fig. 10
Fig. 11
Fig. 12
Fig. 13
Fig. 14
Fig. 15
Fig. 16
Fig. 17
Fig. 18
Fig. 19
Fig. 20
Fig. 21
Fig. 22

Similar content being viewed by others

References

  • Agrawal S, Chaudhari S, Narasayya V (2000) Automated selection of materialized views and indexes in SQL databases. 26th International conference on very large data bases (VLDB 2000). Egypt, Cairo, pp 496–505

  • Aouiche K, Darmont J (2009) Data mining-based materialized view and index selection in data warehouse. J Intell Inf Syst 33(1):65–93

    Google Scholar 

  • Arun B, Vijay Kumar TV (2015a) Materialized view selection using marriage in honey bees optimization. Int J Nat Comput Res 5(3):1–25

    Google Scholar 

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

    Google Scholar 

  • Arun B, Vijay Kumar TV (2017a) Materialized view selection using artificial bee colony optimization. Int J Intell Inf Technol 13(1):26–49

    Google Scholar 

  • Arun B, Vijay Kumar TV (2017b) Materialized view selection using bumble bee mating optimization. Int J Decis Support Syst Technol 9(3):1–27

    Google Scholar 

  • Baralis E, Paraboschi S, Teniente E (1997) Materialized view selection in a multidimansional database. 23rd International conference on very large data bases (VLDB 1997). Greece, Athens, pp 156–165

  • Chirkova R, Halevy AY, Suciu D (2001) A formal perspective on the view selection problem. 27th International conference on very large data bases (VLDB 2001). Roma, Italy, pp 59–68

  • Davis L (1985) Applying adaptive algorithms to epistatic domains. In: Proceedings of the international joint conference on artificial intelligence, Los Angeles, California, pp 162–164.

  • Deb K, Pratap A, Agarwal S, Meyarivan T (2002) A fast and elitist multiobjective genetic algorithm: NSGA-II. IEEE Tran Evolut Comput 6(2):182–197. https://doi.org/10.1109/4235.996017

    Article  Google Scholar 

  • Deb K (2014) Multi-objective optimization using evolutionary algorithms. Wiley, New Delhi

    MATH  Google Scholar 

  • Encinas-Serna and Montano-Hoya (2007) Algorithm for selection of materialized views: based on a costs model. In: Proceedings of ICCT, pp. 18–24

  • Goldberg DE (1989) Genetic algorithms in search, optimization, and machine learnin, vol 1. Addison Wesley, Boston. https://doi.org/10.1007/s10589-009-9261-6

  • Golfarelli M, Rizzi S (2000) View materialization for nested GPSJqueries. In: Proceedings of the international workshop on design and management of data warehouses (DMDW’ 2000), Stockholm, Sweden, pp 1–9

  • Gupta H (1997) Selection of views to materialize in a data Warehouse. In: Proceedings of the 6th international conference on database theory. Springer-Verlag, London, pp 98–112. Retrieved from https://dl.acm.org/citation.cfm?id=645502.656089

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

    Google Scholar 

  • Gupta H, Harinarayan V, Rajaraman V, Ullman J (1997) Index Selection for OLAP. In: Proceedings of the 13th international conference on data engineering, ICDE 97, Birmingham, UK, pp 208–219.

  • Haider M, Vijay Kumar TV (2011) ‘Materialised views selection using size and query frequency. Int J Value Chain Manage 5(2):95–105

    Google Scholar 

  • Haider M, Vijay Kumar TV (2017) Query frequency based view selection. Int J Bus Anal 4(1):36–55

    Google Scholar 

  • Harinarayan V, Rajaraman A, Ullman JD (1996) Implementing data cubes efficiently. ACM SIGMOD, Montreal, Canada, pp 205–216

    Google Scholar 

  • Horng JT, Chang YJ, Liu BJ, Kao CY (1999) Materialized view selection using genetic algorithms in a data warehouse system. In: Proceedings of the 1999 congress on evolutionary computation, vol 3, Washington D. C., USA, IEEE CEC, pp 2221–2227

  • Inmon WH (2003) Building the data warehouse, 3rd edn. Wiley Dreamtech India Pvt, Ltd

    Google Scholar 

  • Kalnis P, Mamoulis N, Papadias D (2002) View selection using randomized search. Data Knowledge Eng 42(1):89–111

    MATH  Google Scholar 

  • Karloff H, Mihail M (1999) On the Complexity of the view selection problem. In: Proceeding of the eighteenth ACM-SIGMOD-SIGACT-SIGART symposium on principles of database systems (PODS), May 1999, pp. 167–173.

  • Kimball R, Ross M (2002) The data warehouse toolkit, 2nd edn. Wiley Computer Publishing, New York

    Google Scholar 

  • Kumar A, Vijay Kumar TV (2017) Improved quality view selection for analytical query performance enhancement using particle swarm optimization. Int J Reliability Qual Safety Eng 24(6):1740001. https://doi.org/10.1142/S0218539317400010

  • Kumar A, Vijay Kumar TV (2018a) Materialized view selection using set based particle swarm optimization. Int J Cogn Inf Nat Intell 12(3):18–39. https://doi.org/10.4018/IJCINI.2018070102

    Article  Google Scholar 

  • Kumar S, Vijay Kumar TV (2018b) A novel quantum-inspired evolutionary view selection algorithm. Sādhanā 43:166

    Google Scholar 

  • Lee M, Hammer J (2001) Speeding up materialized view selection in data warehouses using a randomized algorithm. Int J Cooperative Inf Syst 10(3):327–353

    Google Scholar 

  • Lehner, W., Ruf, T. and Teschke, M. (1996) ‘Improving query response time in scientific databases using data aggregation. In proceedings of 7th international conference and workshop on database and expert systems applications, DEXA 96, Zurich, pp 201–206

  • Li J, Talebi ZA, Chirkova R, Fathi Y (2005) A formal model for the problem of view selection for aggregate queries. In: Eder J, Haav H, Kalja A, Penjam J (eds) Advances in databases and information systems. Springer, Berlin, Heidelberg, pp. 125–138. https://doi.org/10.1007/11547686_10

  • Lin W, Kuo I (2004) A Genetic algorithm for OLAP data cubes. Int J Knowl Inf Syst 6(1):83–102

    Google Scholar 

  • Lin Z, Yang D, Song G, Wang T (2007) User-oriented materialized view selection. In: The 7th IEEE International conference on computer and information technology (CIT-2007), IEEE Computer Society, pp. 133–138

  • Luo G (2007) Partial materialized views. In: International conference on data engineering (ICDE 2007), Istanbul, Turkey, April 2007, pp. 756–765

  • Prakash J, Vijay Kumar TV (2019a) A multi-objective approach for materialized view selection. Int J Oper Res Inf Syst 10(2):1–19

    Google Scholar 

  • Prakash J, Vijay Kumar TV (2019b) Multi-objective materialized view selection using improved strength pareto evolutionary algorithm. Int J Artif Intell Mach Learn 9(2):1–21

    Google Scholar 

  • Prakash J, Vijay Kumar TV (2020) Multi-objective materialized view selection using MOGA. Int J Syst Assurance Eng Manage. https://doi.org/10.1007/s13198-020-00947-2

    Article  Google Scholar 

  • Roussopoulos N (1997) Materialized views and data warehouse. In: 4th Workshop KRDB, Athens, Greece, August 1997

  • Shah B, Ramachandran K, Raghavan V (2006) ‘A hybrid approach for data warehouse view selection. Int J Data Warehousing Mining 2(2):1–37

    Google Scholar 

  • Shukla A, Deshpande PM, Naughton JF (1998) Matreialized view selection for Multidimensional Datasets. In: Proceedings of of VLDB, pp. 488–500

  • Srinivas N, Deb K (1994) Muiltiobjective optimization using nondominated sorting in genetic algorithms. Evolut Comput 2(3):221–248. https://doi.org/10.1162/evco.1994.2.3.221

    Article  Google Scholar 

  • Sun X, Wang Z (2009). An efficient materialized views selection algorithm based on PSO. In: Proceedings of the international workshop on intelligent systems and applications, pp. 1–4.

  • Teschke M, Ulbrich A (1997) Using materialized views to speed up data warehousing, Technical Report, IMMD 6, Universität Erlangen-Nümberg

  • Theodoratos D, Dalamagas T, Simitsis A, Stavropoulos M (2001) A randomized approach for the incremental design of an evolving data warehouse. Lect Notes Comput Sci 2224:325–338

    MATH  Google Scholar 

  • Valluri S, Vadapalli S, Karlapalem K (2002) View relevance driven materialized view selection in data warehousing environment. Aust Comput Sci Commun 24(2):187–196

    Google Scholar 

  • Vijay Kumar TV, Ghoshal A (2009) A reduced lattice greedy algorithm for selecting materialized views. Commun Comput Inf Sci 31:6–18

    Google Scholar 

  • Vijay Kumar TV, Haider M, Kumar S (2010a) Proposing candidate views for materialization. Commun Comput Inf Sci 54:89–98

    Google Scholar 

  • Vijay Kumar TV, Goel A, Jain N (2010b) Mining information for constructing materialised views. Int J Inf Commun Technol 2(4):386–405

    Google Scholar 

  • Vijay Kumar TV, Haider M (2010) A query answering greedy algorithm for selecting materialized views. Lecture Notes in Artificial Intelligence (LNAI), vol 6422, Springer Verlag, Heidelberg, pp. 153–162.

  • Vijay Kumar TV, Haider M (2011a) ‘Greedy views selection using size and query frequency. Commun Comput Inf Sci 125:11–17

  • Vijay Kumar TV, Haider M (2011) Selection of views for materialization using size and query frequency. Commun Comput Inf Sci 147:150–155

    Google Scholar 

  • Vijay Kumar TV, Haider M, Kumar S (2011) A view recommendation greedy algorithm for materialized views selection. Commun Comput Inf Sci 141:61–70

    Google Scholar 

  • Vijay Kumar TV, Devi K (2012) Materialized view construction in data warehouse for decision making. Int J Bus Inf Syst 11(4):379–396

    Google Scholar 

  • Vijay Kumar V, Haider M (2012) ‘Materialized views selection for answering queries. Lecture Notes in Computer Science (LNCS), volume 6411, Springer Verlag, pp. 43–51

  • Vijay Kumar TV, Kumar S (2012a) Materialized view selection using iterative improvement. Adv Intell Syst Comput 178:205–214

    Google Scholar 

  • Vijay Kumar TV, Kumar S (2012) Materialized view selection using genetic algorithm. Commun ComputInf Sci 306:225–237

    Google Scholar 

  • Vijay Kumar TV, Kumar S (2012c) Materialized view selection using simulated annealing. Lecture Notes in Computer Science (LNCS), vol. 7678. Springer Verlag, Heidelberg, pp. 168–179.

  • Vijay Kumar TV (2013) ‘Answering query-based selection of materialised views. Int J Inf Decision Sci 5(1):103–116

    Google Scholar 

  • Vijay Kumar TV, Devi K (2013) An architectural framework for constructing materialized views in a data warehouse. Int J Innovation Manage Technol 4(2):192–197

  • Vijay Kumar TV, Kumar S (2013) Materialized view selection using memetic algorithm. Lecture Notes in Artificial Intelligence (LNAI), vol 8284, Springer Verlag, Heidelberg, pp. 316–327

  • Vijay Kumar TV, Kumar S (2014) Materialized view selection using differential evolution. Int J Innovative ComputAppl 6(2):102–113

    Google Scholar 

  • Vijay Kumar TV, Haider M (2015) ‘Query answering based view selection. Int J Bus Inf Syst 18(3):338–353

    Google Scholar 

  • Vijay Kumar TV, Kumar S (2015) ‘Materialized view selection using randomized algorithms. Int J Bus Inf Syst 19(2):224–240

    Google Scholar 

  • Vijay Kumar TV, Arun B (2016) Materialized view selection using BCO. Int J Bus Inf Syst 22(3):280–301

    Google Scholar 

  • Vijay Kumar TV, Arun B (2017) Materialized view selection using HBMO. Int J Syst Assurance Eng Manage

  • Wang Z, Zhang D (2005) Optimal genetic view selection algorithm under space constraint. Int J Inf Technol 11(5):44–51

    Google Scholar 

  • Widom J (1995) Research problems in data warehousing. In: Proceedings of international conference on information and knowledge management (ICIKM-1995), pp. 25–30

  • Yousri NAR, Ahmed KM, El-Makky NM (2005) Algorithms for selecting materialized views in a data warehouse. In: The proceedings of international conference on computer systems and applications, AICCSA’ 2005, Cairo, Egypt, pp. 27–34.

  • Yang J, Karlapalem K, Li Q (1997) Algorithms for materialized view design in data warehousing environment. In: the proceedings of 23rd International conference on very large data bases (VLDB-1997), August 25–29, 1997, pp.136–145.

  • Yu JX, Yao X, Choi C, Gou G (2003) Materialized view selection as constrained evolutionary optimization systems. IEEE Trans Syst Man Cybern C Appl Rev 33(4):458–467

    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 Cybern 31(3):282–294

    Google Scholar 

  • Zhou L, He X, Li K (2012) An improved approach for materialized view selection based on genetic algorithm. J Comput 7(7):1591–1598

    Google Scholar 

  • Zitzler E, Thiele L (1999) Multiobjective evolutionary algorithms: a comparative case study and the Sstrength Pareto approach. IEEE Trans Evolut Comput 3(4):257–271

    Google Scholar 

  • Zitzler E, Deb K, Thiele L (2000) Comparison of multiobjective evolutionary algorithms: empirical results. Evolut Comput 8(2):173–195

    Google Scholar 

  • Zitzler E, Thiele L, Laumanns M, Fonseca CM, da Fonseca VG (2003) Performance assessment of multiobjective optimizers: an analysis and review’. IEEE Trans Evolut Comput 7:117–132

    Google Scholar 

Download references

Author information

Authors and Affiliations

  1. School of Computer and Systems Sciences, Jawaharlal Nehru University, New Delhi, 110067, India

    Jay Prakash & T. V. Vijay Kumar

Authors
  1. Jay Prakash
    View author publications

    You can also search for this author in PubMed Google Scholar

  2. T. V. Vijay Kumar
    View author publications

    You can also search for this author in PubMed Google Scholar

Corresponding author

Correspondence to T. V. Vijay Kumar.

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

Prakash, J., Kumar, T.V.V. Multi-objective materialized view selection using NSGA-II. Int J Syst Assur Eng Manag 11, 972–984 (2020). https://doi.org/10.1007/s13198-020-01030-6

Download citation

  • Received:

  • Revised:

  • Published:

  • Issue Date:

  • DOI: https://doi.org/10.1007/s13198-020-01030-6

Keywords

Search

Navigation