A Constraint Optimization Method for Large-Scale Distributed View Selection

  • Imene MamiEmail author
  • Zohra Bellahsene
  • Remi Coletta
Part of the Lecture Notes in Computer Science book series (LNCS, volume 9620)


View materialization is a commonly used technique in many data-intensive systems to improve the query performance. Increasing need for large-scale data processing has led to investigating the view selection problem in distributed complex scenarios where a set of cooperating computer nodes may share data and issue numerous queries. In our work, the view selection and data placement problem is studied given a limited amount of resources e.g. storage space capacity per computer node and maximum view maintenance cost. We also consider the IO and CPU costs for each computer node as well as the network bandwidth. To address this problem, we have proposed a constraint programming approach which is based on constraint reasoning to tackle problems that aim to satisfy a set of constraints. Then, we have designed a set of efficient heuristics that result in a drastic reduction in the solution space so that the problem becomes solvable for complex scenarios consisting of realistically large numbers of sites, queries and views. Our experimental study shows that our approach performs consistently better compared to a practical approach designed for large-scale distributed environments which uses a genetic algorithm to compute which view has to be materialized at what computer node.


Distributed database design Modeling and management Query processing and optimization Materialized views Constraint optimization problem 


  1. 1.
    Choco, open-source software for constraint satisfaction problems.
  2. 2.
    The TPC benchmark H (TPC-H).
  3. 3.
    Bauer, A., Lehner, W.: On solving the view selection problem in distributed data warehouse architectures. In: SSDBM, pp. 43-51, Cambridge (2003)Google Scholar
  4. 4.
    Apt, K.: Principles of Constraint Programming. Cambridge University Press, New York (2003)CrossRefzbMATHGoogle Scholar
  5. 5.
    Baril, X., Bellahsene, Z.: Selection of materialized views: a cost-based approach. In: CAiSE, pp. 665–680, Klagenfurt (2003)Google Scholar
  6. 6.
    Bellahsene, Z., Cart, M., Kadi, N.: A cooperative approach to view selection and placement in P2P systems. In: Meersman, R., Dillon, T.S., Herrero, P. (eds.) OTM 2010. LNCS, vol. 6426, pp. 515–522. Springer, Heidelberg (2010)CrossRefGoogle Scholar
  7. 7.
    Chaves, L.W.F., Buchmann, E., Hueske, F., Böhm, K.: Towards materialized view selection for distributed databases. In: Proceedings of the 12th International Conference on Extending Database Technology: Advances in Database Technology, EDBT 2009, pp. 1088–1099. ACM, New York (2009)Google Scholar
  8. 8.
    Derakhshan, R., Dehne, F.K., Korn, O., Stantic, B.: Simulated annealing for materialized view selection in data warehousing environment. In: Databases and Applications, pp. 89–94, (2006)Google Scholar
  9. 9.
    Derakhshan, R., Stantic, B., Korn, O., Dehne, F.K.H.A.: Parallel simulated annealing for materialized view selection in datawarehousing environments. In: ICA3PP, pp. 121–132, island of Cyprus (2008)Google Scholar
  10. 10.
    Deshpande, P.M., Ramasamy, K., Shukla, A., Naughton, J.F.: Caching multidimensional queries using chunks. In: SIGMOD Conference, pp. 259–270, Seattle (1998)Google Scholar
  11. 11.
    Du, W., Krishnamurthy, R., Shan, M.C.: Query optimization in heterogeneous dbms. In: Proceeding of VLDB, pp. 277–91, Vancouver (1992)Google Scholar
  12. 12.
    Gribble, S.D., Halevy, A.Y., Ives, Z.G., Rodrig, M., Suciu, D.: What can database do for peer-to-peer? In: WebDB, pp. 31–36, Santa Barbara (2001)Google Scholar
  13. 13.
    Gupta, H.: Selection of views to materialize in a data warehouse. In: ICDT, pp. 98–112, Delphi (1997)Google Scholar
  14. 14.
    Gupta, H., Mumick, I.S.: Selection of views to materialize under a maintenance cost constraint. In: Beeri, C., Bruneman, P. (eds.) ICDT 1999. LNCS, vol. 1540, pp. 453–470. Springer, Heidelberg (1998)CrossRefGoogle Scholar
  15. 15.
    Gupta, H., Mumick, I.S.: Selection of views to materialize in a data warehouse. IEEE Trans. Knowl. Data Eng. 17(1), 24–43 (2005)CrossRefGoogle Scholar
  16. 16.
    Harinarayan, V., Rajaraman, A., Ullman, J.D.: Implementing data cubes efficiently. In: SIGMOD Conference, pp. 205–216, Montreal (1996)Google Scholar
  17. 17.
    Horng, J.-T., Chang, Y.-J., Liu, B.-J.: Applying evolutionary algorithms to materialized view selection in a data warehouse. Soft Comput. 7(8), 574–581 (2003)CrossRefGoogle Scholar
  18. 18.
    Kalnis, P., Mamoulis, N., Papadias, D.: View selection using randomized search. Data Knowl. Eng. 42(1), 89–111 (2002)CrossRefzbMATHGoogle Scholar
  19. 19.
    Kalnis, P., Ng, W.S., Ooi, B.C., Papadias, D., Tan, K.L.: An adaptive peer-to-peer network for distributed caching of olap results. In: SIGMOD Conference, pp. 25–36, Madison (2002)Google Scholar
  20. 20.
    Kossmann, D.: The state of the art in distributed query processing. ACM Comput. Surv. 32(4), 422–469 (2000)CrossRefGoogle Scholar
  21. 21.
    Kossmann, D., Franklin, M.J., Drasch, G.: Cache investment: integrating query optimization and distributed data placement. ACM TODS 252000 (2000)Google Scholar
  22. 22.
    Kotidis, Y., Roussopoulos, N.: Dynamat: a dynamic view management system for data warehouses. In: SIGMOD Conference, pp. 371–382, Philadephia (1999)Google Scholar
  23. 23.
    Kumar, T.V., Kumar, S.: Materialized view selection using genetic algorithm. In: IC3, pp. 225–237 (2012)Google Scholar
  24. 24.
    Labio, W.J., Quass, D., Adelberg, B.: Physical database design for data warehouses. In Proceedings of the Thirteenth International Conference on Data Engineering, ICDE 1997, pp. 277–288. IEEE Computer Society, Washington, DC (1997)Google Scholar
  25. 25.
    Lee, M., Hammer, J.: Speeding up materialized view selection in data warehouses using a randomized algorithm. Int. J. Cooperative Inf. Syst. 10(3), 327–353 (2001)CrossRefGoogle Scholar
  26. 26.
    Ligoudistianos, S., Theodoratos, D., Sellis, T.: Experimental evaluation of data warehouse configuration algorithms. In: DEXA Workshop, pp. 218–223, Vienna (1998)Google Scholar
  27. 27.
    Mackert, L.F., Lohman, G.M.: R* optimizer validation and performance evaluation for local queries. In: Proceedings of the ACM SIGMOD International Conference on Management of Data, SIGMOD 1986, pp. 84–95. ACM, New York (1986)Google Scholar
  28. 28.
    Mami, I., Bellahsene, Z.: A survey of view selection methods. SIGMOD Record 41(1), 20–29 (2012)CrossRefGoogle Scholar
  29. 29.
    Mami, I., Bellahsene, Z., Coletta, R.: View selection under multiple resource constraints in a distributed context. In: Liddle, S.W., Schewe, K.-D., Tjoa, A.M., Zhou, X. (eds.) DEXA 2012, Part II. LNCS, vol. 7447, pp. 281–296. Springer, Heidelberg (2012)CrossRefGoogle Scholar
  30. 30.
    Mami, I., Bellahsene, Z., Coletta, R.: A declarative approach to view selection modeling. Trans. Large-Scale Data Knowl. Centered Syst. 10, 115–145 (2013)Google Scholar
  31. 31.
    Mami, I., Coletta, R., Bellahsene, Z.: Modeling view selection as a constraint satisfaction problem. In: Hameurlain, A., Liddle, S.W., Schewe, K.-D., Zhou, X. (eds.) DEXA 2011, Part II. LNCS, vol. 6861, pp. 396–410. Springer, Heidelberg (2011)CrossRefGoogle Scholar
  32. 32.
    Mistry, H., Roy, P., Sudarshan, S., Ramamritham, K.: Materialized view selection and maintenance using multi-query optimization. In: SIGMOD Conference, pp. 307–318, Santa Barbara (2001)Google Scholar
  33. 33.
    Nguyen, T.V.A., Bimonte, S., d’Orazio, L., Darmont, J.: Cost models for view materialization in the cloud. In: EDBT/ICDT Workshops, pp. 47–54, Berlin (2012)Google Scholar
  34. 34.
    Özsu, M.T., Valduriez, P.: Principles of Distributed Database Systems, 3rd edn. Springer, New York (2011)Google Scholar
  35. 35.
    De Raedt, L., Guns, T., Nijssen, S.: Constraint programming for itemset mining. In: KDD, pp. 204–212, Las Vegas (2008)Google Scholar
  36. 36.
    Rossi, F., van Beek, P., Walsh, T.: Handbook of Constraint Programming (Foundations of Artificial Intelligence). Elsevier Science Inc., New York (2006)Google Scholar
  37. 37.
    Roy, P., Seshadri, S., Sudarshan, S., Bhobe, S.: Efficient and extensible algorithms for multi query optimization. In: SIGMOD Conference, pp. 249–260, Dallas (2000)Google Scholar
  38. 38.
    Scheuermann, P., Shim, J., Vingralek, R.: A data warehouse intelligent cache manager. In: VLDB, pp. 51–62, Bombay (1996)Google Scholar
  39. 39.
    Selinger, P.G., Astrahan, M.M., Chamberlin, D.D., Lorie, R.A., Price, T.G.: Access path selection in a relational database management system, pp. 23–34 (1979)Google Scholar
  40. 40.
    Steinbrunn, M., Moerkotte, G., Kemper, A.: Heuristic and randomized optimization for the join ordering problem. VLDB J. 6(3), 191–208 (1997)CrossRefGoogle Scholar
  41. 41.
    Theodoratos, D., Ligoudistianos, S., Sellis, T.K.: View selection for designing the global data warehouse. Data Knowl. Eng. 39(3), 219–240 (2001)CrossRefzbMATHGoogle Scholar
  42. 42.
    Theodoratos, D., Sellis, T.K.: Data warehouse configuration. In: VLDB, pp. 126–135, Athens (1997)Google Scholar
  43. 43.
    Ye, W., Gu, N., Yang, G., Liu, Z.: Extended derivation cube based view materialization selection in distributed data warehouse. In: Fan, W., Wu, Z., Yang, J. (eds.) WAIM 2005. LNCS, vol. 3739, pp. 245–256. Springer, Heidelberg (2005)CrossRefGoogle Scholar
  44. 44.
    Yu, J.X., Yao, X., Choi, C.H., Gou, G.: Materialized view selection as constrained evolutionary optimization. IEEE Trans. Syst. Man Cybern. Part C 33(4), 458–467 (2003)CrossRefGoogle Scholar
  45. 45.
    Zhang, C., Yang, J.: Genetic Algorithm for materialized view selection in data warehouse environments. In: Mohania, M., Tjoa, A.M. (eds.) DaWaK 1999. LNCS, vol. 1676, pp. 116–125. Springer, Heidelberg (1999)Google Scholar
  46. 46.
    Zhang, C., Yao, X., Yang, J.: An evolutionary approach to materialized views selection in a data warehouse environment. IEEE Trans. Syst. Man Cybern. Part C 31(3), 282–294 (2001)CrossRefMathSciNetGoogle Scholar
  47. 47.
    Zhou, J., Larson, P-Å, Goldstein, J., Ding, L.: Dynamic materialized views. In: ICDE, pp. 526–535, Istanbul (2007)Google Scholar

Copyright information

© Springer-Verlag Berlin Heidelberg 2016

Authors and Affiliations

  1. 1.University Montpellier 2, LIRMMMontpellierFrance

Personalised recommendations