Skip to main content

Advertisement

SpringerLink
Log in

Multi-objective materialized view selection using MOGA

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

Abstract

Materialized views are used as an alternative means for reducing the response time of analytical queries posed against a data warehouse. Since all views cannot be materialized and since optimal view selection is an NP-Hard problem, there is a need to select an appropriate subset of views for materialization that reduce the response times for analytical queries. This problem, referred to as view selection, is a widely studied problem in data warehousing. Several materialized view selection (MVS) algorithms exist that address the view selection problem, as a single objective optimization problem where the objective is to minimize the total cost of evaluating all the views (TVEC). This cost comprises two costs, i.e. the total cost of evaluation due to materialized views and the total cost of evaluation due to non-materialized views. Minimization of these two costs simultaneously would lead to the minimization of TVEC. In this paper, this bi-objective optimization problem, where the two costs are minimized simultaneously, has been solved using the Multi-Objective Genetic Algorithm (MOGA). The proposed MOGA based MVS algorithm selects the Top-K views from a multidimensional lattice with the purpose of achieving an optimal trade-off between the two aforementioned objectives. Materializing these selected Top-K views would reduce the response times for analytical queries and thereby would result in effective and efficient decision making.

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

Similar content being viewed by others

References

  • Agrawal S, Chaudhari S, Narasayya V (2000) Automated selection of materialized views and indexes in SQL databases. In: 26th international conference on very large data bases (VLDB 2000), Cairo, Egypt, 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 

  • Aouiche K, Jouve P-E, Darmont J (2006) Clustering-based materialized view selection in data warehouses. In: Proceeding of 10th East-European conference on advances in databases and information systems (ADBIS06), Thessaloniki, Greece, LNCS, vol 4152, pp 81–95

  • 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

    Google Scholar 

  • 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

    Google Scholar 

  • 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 (2014) Multi-objective optimization using evolutionary algorithms. Wiley India Pvt. Ltd., New Delhi

    MATH  Google Scholar 

  • Deb K, Goldberg DE (1989) An investigation of niche and species formation in genetic function optimization. In: Proceedings of the 3rd international conference on genetic algorithms, Fairfax, Virginia, USA, pp 42–50

  • Dondi R, Mauri G, Zoppis I (1999) On the complexity of the view-selection problem. In: PODS’99 proceedings of the eighteenth ACM SIGMOD-SIGACT-SIGART symposium on principles of database systems, Philadelphia PA, pp 167–173

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

  • Fonseca CM, Fleming PJ (1993) Genetic algorithms for multiobjective optimization: formulation, discussion and generalization. In: Proceedings of the 5th international conference on genetic algorithms, San Mateo, CA, USA, pp 416–423

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

    Book  MATH  Google Scholar 

  • Goldberg DE, Richardson J (1987) Genetic algorithms with sharing for multimodal function optimization. In: Proceedings of the 2nd international conference on genetic algorithms on genetic algorithms and their application, Cambridge, Massachusetts, USA, pp 41–49

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

  • Gupta H, Mumick IS (2005) Selection of views to materialize in a data warehouse. IEEE Trans Knowl 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 Manag (IJVCM) 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, 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, Washington D. C., USA, vol 3, IEEE CEC, pp 2221–2227

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

    Google Scholar 

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

    MATH  Google Scholar 

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

    Google Scholar 

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

    Article  Google Scholar 

  • Kumar A, Vijay Kumar TV (2018a) Materialized view selection using set based particle swarm optimization. Int J Cogn Inform Nat Intell 12(3):18–39

    Google Scholar 

  • Kumar S, Vijay Kumar TV (2018b) A novel quantum inspired evolutionary view selection algorithm. Journal Sadhana, Springer and Indian Academy of Sciences, vol 43, issue 10, Article 166

  • Lawrence M (2006) Multiobjective genetic algorithms for materialized view selection in OLAP data warehouses. GECCO’06, July 8–12, Seattle Washington, USA, pp 699–706

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

    Google Scholar 

  • Lehner W, Ruf T, 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

  • Lin W, Kuo I (2004) A Genetic Algorithm for OLAP data cubes. International Journal on Knowledge and Information Systems 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

  • Mohania M, Samtani S, Roddick JF, Kambayashi Y (1999) Advances and research directions in data-warehousing technology. Australas J Inf Syst 7(1):41–59

    Google Scholar 

  • 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 

  • Roussopoulos N (1982) The logical access path schema of a database. IEEE Trans Softw Eng SE-8(6):563–573. https://doi.org/10.1109/TSE.1982.235886

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

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

    Google Scholar 

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

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

  • Theodoratos D, Sellis T (1997) Data warehouse configuration. Proceeding of VLDB. Greece, Athens, pp 126–135

    Google Scholar 

  • Theodoratos D, Dalamagas T, Simitsis A, Stavropoulos M (2001) A randomized approach for the incremental design of an evolving data warehouse. Lecture notes in Computer Science (LNCS), vol 2224, pp 325–338

  • Uchiyama H, Runapongsa K, Teorey TJ (1999) A progressive view materialization algorithm. In: Proceedings of DOLAP, pp 36–41

  • 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 (2013) Answering query-based selection of materialised views. Int J Inf Decis Sci (IJIDS) 5(1):103–116

    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 Assur Eng Manag 8(1):379–392

    Google Scholar 

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

    Google Scholar 

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

  • Vijay Kumar TV, Ghoshal A (2009) A reduced lattice greedy algorithm for selecting materialized views. In: Communications in computer and information science (CCIS), vol 31. Springer, New York, pp 6–18

  • Vijay Kumar TV, Haider M (2010) A query answering greedy algorithm for selecting materialized views. In: Lecture notes in artificial intelligence (LNAI), vol 6422. Springer, New York, pp 153–162

  • Vijay Kumar TV, Haider M (2011a) Greedy views selection using size and query frequency. In: Communications in computer and information science (CCIS), vol 125. Springer, New York, pp 11-17

  • Vijay Kumar TV, Haider M (2011b) Selection of views for materialization using size and query frequency. In: Communications in computer and information science (CCIS), vol 147. Springer, New York, pp 150–155

  • Vijay Kumar TV, Haider M (2012) Materialized views selection for answering queries. In: Lecture notes in computer science (LNCS), vol 6411. Springer, New York, pp 43–51

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

    Google Scholar 

  • Vijay Kumar TV, Kumar S (2012a) Materialized view selection using iterative improvement. In: Advances in intelligent systems and computing (AISC), vol 178. Springer, New York, pp 205–214

  • Vijay Kumar TV, Kumar S (2012b) Materialized view selection using genetic algorithm. In: Communications in computer and information science (CCIS), vol 306. Springer, New York, pp 225–237

  • Vijay Kumar TV, Kumar S (2012c) Materialized view selection using simulated annealing. In: Lecture notes in computer science (LNCS), vol 7678. Springer, New York, pp 168–179

  • Vijay Kumar TV, Kumar S (2013) Materialized view selection using memetic algorithm. In: Lecture notes in artificial intelligence (LNAI), vol 8284. Springer, New York, pp 316–327

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

    Google Scholar 

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

    Google Scholar 

  • Vijay Kumar TV, Haider M, Kumar S (2010a) Proposing candidate views for materialization. In: Communications in computer and information science (CCIS), vol 54. Springer, New York, pp 89–98

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

    Google Scholar 

  • Vijay Kumar TV, Haider M, Kumar S (2011) A view recommendation greedy algorithm for materialized views selection. In: Communications in computer and information science (CCIS), vol 141. Springer, New York, pp 61–70

  • 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

  • Yang J, Karlapalem K, Li Q (1997a) Algorithms for materialized view design in data warehousing environment. Very Large Databases (VLDB) J 136–145

  • Yang J, Karlapalem K, Li Q (1997b) A framework for designing materialized views in data warehousing environment. In: Proceedings of 17th international conference on distributed computing systems (ICDCS’97), Baltimore, MD, USA, pp 458–465

  • 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

  • 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 (1999) Evolving materialized views in a data warehouse. IEEE CEC’99, Washington, DC, USA, pp 823–829

  • 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 

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., Vijay Kumar, T.V. Multi-objective materialized view selection using MOGA. Int J Syst Assur Eng Manag 11 (Suppl 2), 220–231 (2020). https://doi.org/10.1007/s13198-020-00947-2

Download citation

  • Received:

  • Revised:

  • Published:

  • Issue Date:

  • DOI: https://doi.org/10.1007/s13198-020-00947-2

Keywords

Search

Navigation