Materialized View Selection Using Iterative Improvement

  • T. V. Vijay Kumar
  • Santosh Kumar
Part of the Advances in Intelligent Systems and Computing book series (AISC, volume 178)


A data warehouse is designed for the purpose of answering analytical queries, posed on them for decision making. The complex and exploratory nature of analytical queries which, when processed against large historical information in the data warehouse, consume a lot of time for processing. As a result, the query response time is high. Materialized views provide an alternative platform to address this problem of poor query response time. These views store aggregated and summarized information separately from a data warehouse with the aim of answering analytical queries. All views cannot be materialized, as the number of views is exponential in respect of number of dimensions. Also, optimal view selection is an NP-Complete Problem. Several view selection algorithms exist with most selecting views empirically or based on heuristics like greedy or evolutionary. In this paper, an algorithm based on iterative improvement, a randomized search heuristic technique for selecting top-K views for materialization is proposed. It is shown that the proposed algorithm, in comparison to a well known greedy algorithm, is able to select comparatively better quality views for higher dimensional data sets.


Greedy Algorithm Data Warehouse Propose Algorithm Query Response Time Iterative Improvement 
These keywords were added by machine and not by the authors. This process is experimental and the keywords may be updated as the learning algorithm improves.


Unable to display preview. Download preview PDF.

Unable to display preview. Download preview PDF.


  1. 1.
    Agrawal, S., Chaudhari, S., Narasayya, V.: Automated Selection of Materialized Views and Indexes in SQL databases. In: 26th International Conference on Very Large Data Bases (VLDB 2000), Cairo, Egypt, pp. 495–505 (2000)Google Scholar
  2. 2.
    Aouiche, K., Jouve, P.-E., Darmont, J.: Clustering-Based Materialized View Selection in Data Warehouses. In: Manolopoulos, Y., Pokorný, J., Sellis, T.K. (eds.) ADBIS 2006. LNCS, vol. 4152, pp. 81–95. Springer, Heidelberg (2006)CrossRefGoogle Scholar
  3. 3.
    Aouiche, K., Darmont, J.: Data mining-based materialized view and index selection in data warehouse. Journal of Intelligent Information Systems, 65–93 (2009)Google Scholar
  4. 4.
    Baralis, E., Paraboschi, S., Teniente, E.: Materialized View Selection in a Multidimansional Database. In: 23rd International Conference on Very Large Data Bases (VLDB 1997), Athens, Greece, pp. 156–165 (1997)Google Scholar
  5. 5.
    Chaudhuri, S., Shim, K.: Including Groupby in Query Optimization. In: Proceedings of the International Conference on Very Large Database Systems (1994)Google Scholar
  6. 6.
    Chirkova, R., Halevy, A.Y., Suciu, D.: A Formal Perspective on the View Selection Problem. In: Proceedings of VLDB, pp. 59–68 (2001)Google Scholar
  7. 7.
    Golfarelli, M., Rizzi, S.: View Materialization for Nested GPSJ Queries. In: Proceedings of the International Workshop on Design and Management of Data Warehouses (DMDW 2000), Stockholm, Sweden (2000)Google Scholar
  8. 8.
    Gupta, H., Mumick, I.S.: Selection of Views to Materialize in a Data warehouse. IEEE Transactions on Knowledge & Data Engineering 17(1), 24–43 (2005)CrossRefGoogle Scholar
  9. 9.
    Gupta, A., Harinarayan, V., Quass, D.: Generalized Projections: A Powerful Approach to Aggregation. In: Proceedings of the International Conference of Very Large Database Systems (1995)Google Scholar
  10. 10.
    Gupta, H., Harinarayan, V., Rajaraman, V., Ullman, J.: Index Selection for OLAP. In: Proceedings of the 13th International Conference on Data Engineering, ICDE 1997, Birmingham, UK (1997)Google Scholar
  11. 11.
    Harinarayan, V., Rajaraman, A., Ullman, J.D.: Implementing Data Cubes Efficiently. In: ACM SIGMOD, Montreal, Canada, pp. 205–216 (1996)Google Scholar
  12. 12.
    Horng J. T., Chang Y. J., Liu B. J., Kao, C.Y.: 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 (1999)Google Scholar
  13. 13.
    Inmon, W.H.: Building the Data Warehouse, 3rd edn. Wiley Dreamtech India Pvt. Ltd. (2003)Google Scholar
  14. 14.
    Ioannidis, Y.E., Kang, Y.C.: Randomized Algorithms for Optimizing Large Join Queries. In: Proceedings of the 1990 ACM SIGMOD International Conference on Management of Data, ACM SIGMOD Record, vol. 19(2), pp. 312–321 (1990)Google Scholar
  15. 15.
    Lawrence, M.: Multiobjective Genetic Algorithms for Materialized View Selection in OLAP Data Warehouses. In: GECCO 2006, Seattle, Washington, USA, July 8-12 (2006)Google Scholar
  16. 16.
    Lehner, W., Ruf, T., Teschke, M.: 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 1996, Zurich(1996)Google Scholar
  17. 17.
    Lin, Z., Yang, D., Song, G., Wang, T.: User-oriented Materialized View Selection. In: The 7th IEEE International Conference on Computer and Information Technology (2007)Google Scholar
  18. 18.
    Luo, G.: Partial Materialized Views. In: International Conference on Data Engineering (ICDE 2007), Istanbul, Turkey (April 2007)Google Scholar
  19. 19.
    Mohania, M., Samtani, S., Roddick, J., Kambayashi, Y.: Advances and Research Directions in Data Warehousing Technology. Australian Journal of Information Systems (1998)Google Scholar
  20. 20.
    Nahar, S., Sahni, S., Shragowitz, E.: Simulated Annealing and Combinatorial Optimization. In: Proceedings of the 23rd Design Automation Conference, pp. 293–299 (1986)Google Scholar
  21. 21.
    Roussopoulos, N.: Materialized Views and Data Warehouse. In: 4th Workshop KRDB 1997, Athens, Greece (August 1997)Google Scholar
  22. 22.
    Shah, B., Ramachandran, K., Raghavan, V.: A Hybrid Approach for Data Warehouse View Selection. International Journal of Data Warehousing and Mining 2(2), 1–37 (2006)zbMATHCrossRefGoogle Scholar
  23. 23.
    Teschke, M., Ulbrich, A.: Using Materialized Views to Speed Up Data Warehousing, Technical Report, IMMD 6, Universität Erlangen-Nümberg (1997)Google Scholar
  24. 24.
    Theodoratos, D., Sellis, T.: Data Warehouse Configuration. In: Proceeding of VLDB, Athens, Greece, pp. 126–135 (1997)Google Scholar
  25. 25.
    Valluri, S., Vadapalli, S., Karlapalem, K.: View Relevance Driven Materrialized View Selection in Data Warehousing Environment. Australian Computer Science Communications 24(2), 187–196 (2002)Google Scholar
  26. 26.
    Vijay Kumar, T.V., Ghoshal, A.: A Reduced Lattice Greedy Algorithm for Selecting Materialized Views. In: Prasad, S.K., Routray, S., Khurana, R., Sahni, S. (eds.) ICISTM 2009. CCIS, vol. 31, pp. 6–18. Springer, Heidelberg (2009)CrossRefGoogle Scholar
  27. 27.
    Vijay Kumar, T.V., Haider, M., Kumar, S.: Proposing Candidate Views for Materialization. In: Prasad, S.K., Vin, H.M., Sahni, S., Jaiswal, M.P., Thipakorn, B. (eds.) ICISTM 2010. CCIS, vol. 54, pp. 89–98. Springer, Heidelberg (2010)CrossRefGoogle Scholar
  28. 28.
    Vijay Kumar, T.V., Haider, M.: A Query Answering Greedy Algorithm for Selecting Materialized Views. In: Pan, J.-S., Chen, S.-M., Nguyen, N.T. (eds.) ICCCI 2010. LNCS(LNAI), vol. 6422, pp. 153–162. Springer, Heidelberg (2010)CrossRefGoogle Scholar
  29. 29.
    Vijay Kumar, T.V., Jain, N.: Selection of Frequent Queries for Constructing Materialized Views in Data Warehouse. The IUP Journal of Systems Management 8(2), 46–64 (2010)Google Scholar
  30. 30.
    Vijay Kumar, T.V., Goel, A., Jain, N.: Mining Information for Constructing Materialised Views. International Journal of Information and Communication Technology 2(4), 386–405 (2010)CrossRefGoogle Scholar
  31. 31.
    Vijay Kumar, T.V., Haider, M.: Greedy Views Selection Using Size and Query Frequency. In: Unnikrishnan, S., Surve, S., Bhoir, D. (eds.) ICAC3 2011. CCIS, vol. 125, pp. 11–17. Springer, Heidelberg (2011)CrossRefGoogle Scholar
  32. 32.
    Vijay Kumar, T.V., Haider, M., Kumar, S.: A View Recommendation Greedy Algorithm for Materialized Views Selection. In: Dua, S., Sahni, S., Goyal, D.P. (eds.) ICISTM 2011. CCIS, vol. 141, pp. 61–70. Springer, Heidelberg (2011)CrossRefGoogle Scholar
  33. 33.
    Vijay Kumar, T.V., Devi, K.: Frequent Queries Identification for Constructing Materialized Views. In: The Proceedings of the International Conference on Electronics Computer Technology (ICECT-2011), April 8-10, vol. 6, pp. 177–181. IEEE, Kanyakumari (2011)Google Scholar
  34. 34.
    Vijay Kumar, T.V., Haider, M.: Selection of Views for Materialization Using Size and Query Frequency. In: Das, V.V., Thomas, G., Lumban Gaol, F. (eds.) AIM 2011. CCIS, vol. 147, pp. 150–155. Springer, Heidelberg (2011)CrossRefGoogle Scholar
  35. 35.
    Vijay Kumar, T.V., Haider, M.: Materialized Views Selection for Answering Queries. In: Kannan, R., Andres, F. (eds.) ICDEM 2010. LNCS, vol. 6411, pp. 44–51. Springer, Heidelberg (2012)CrossRefGoogle Scholar
  36. 36.
    Widom, J.: Research Problems in Data Warehousing. In: 4th International Conference on Information and Knowledge Management, Baltimore, Maryland, pp. 25–30 (1995)Google Scholar
  37. 37.
    Yang, J., Karlapalem, K., Li, Q.: Algorithms for Materialized View Design in Data Warehousing Environment. The Very Large databases (VLDB) Journal, 136–145 (1997)Google Scholar
  38. 38.
    Yousri, N.A.R., Ahmed, K.M., El-Makky, N.M.: Algorithms for Selecting Materialized Views in a Data Warehouse. In: The Proceedings of the ACS/IEEE 2005 International Conference on Computer Systems and Applications, AICCSA 2005, pp. 27–1. IEEE Computer Society (2005)Google Scholar
  39. 39.
    Zhang, C., Yao, X., Yang, J.: Evolving Materialized Views in a Data Warehouse. IEEE CEC, 823–829 (1999)Google Scholar
  40. 40.
    Zhang, C., Yao, X., Yang, J.: An Evolutionary Approach to Materialized Views Selection in a Data Warehouse Environment. IEEE Transactions on Systems, Man and Cybernatics, 282–294 (2001)Google Scholar

Copyright information

© Springer-Verlag Berlin Heidelberg 2013

Authors and Affiliations

  • T. V. Vijay Kumar
    • 1
  • Santosh Kumar
    • 1
  1. 1.School of Computer and Systems SciencesJawaharlal Nehru UniversityNew DelhiIndia

Personalised recommendations