A Formal Model for the Problem of View Selection for Aggregate Queries

  • Jingni Li
  • Zohreh Asgharzadeh Talebi
  • Rada Chirkova
  • Yahya Fathi
Part of the Lecture Notes in Computer Science book series (LNCS, volume 3631)


We present a formal analysis of the following view-selection problem: Given a set of queries and a database, return definitions of views that, when materialized in the database, would reduce the evaluation costs of the queries. Optimizing the layout of stored data using view selection has a direct impact on the performance of the entire database system. At the same time, the optimization problem is intractable, even under natural restrictions on the types of queries of interest. In this paper we use an integer-programming model to obtain optimal solutions to the problem of view selection for aggregate queries on data warehouses. We also report the results of the post-optimality analysis that we performed to determine/observe the impact of changing certain input characteristics on the optimal solution.


Facility Location Problem Integer Programming Model Storage Limit View Versus Aggregate Query 
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.
    Afrati, F., Chirkova, R.: Selecting and using views to compute aggregate queries. In: Eiter, T., Libkin, L. (eds.) ICDT 2005. LNCS, vol. 3363, pp. 383–397. Springer, Heidelberg (2004)CrossRefGoogle Scholar
  2. 2.
    Agarwal, S., Agrawal, R., Deshpande, P., Gupta, A., Naughton, J.F., Ramakrishnan, R., Sarawagi, S.: On the computation of multidimensional aggregates. In: Proceedings of VLDB, pp. 506–521 (1996)Google Scholar
  3. 3.
    Agrawal, S., Chaudhuri, S., Narasayya, V.R.: Automated selection of materialized views and indexes in SQL databases. In: Proc. VLDB, pp. 496–505 (2000)Google Scholar
  4. 4.
    Agrawal, S., Chaudhuri, S., Narasayya, V.R.: Materialized view and index selection tool for Microsoft SQL Server 2000. In: Proc. ACM SIGMOD (2001)Google Scholar
  5. 5.
    Baralis, E., Paraboschi, S., Teniente, E.: Materialized view selection in a multidimensional database. In: Proc. VLDB, pp. 156–165 (1997)Google Scholar
  6. 6.
    Barcelo, J., Casanovas, J.: A heuristic lagrangean algorithm for the capacitated plant location problem. European J. Operations Research 15, 212–226 (1984)zbMATHCrossRefGoogle Scholar
  7. 7.
    Chaudhuri, S., Dayal, U.: An overview of data warehousing and OLAP technology. SIGMOD Record 26(1), 65–74 (1997)CrossRefGoogle Scholar
  8. 8.
    Chaudhuri, S., Krishnamurthy, R., Potamianos, S., Shim, K.: Optimizing queries with materialized views. In: Proceedings of ICDE, pp. 190–200 (1995)Google Scholar
  9. 9.
    Chaudhuri, S., Narasayya, V.R.: An efficient cost-driven index selection tool for Microsoft SQL server. In: Proceedings of VLDB, pp. 146–155 (1997)Google Scholar
  10. 10.
    Chaudhuri, S., Narasayya, V.R.: AutoAdmin ’What-if’ index analysis utility. In: Proceedings of ACM SIGMOD, pp. 367–378 (1998)Google Scholar
  11. 11.
    Chirkova, R., Halevy, A.Y., Suciu, D.: A formal perspective on the view selection problem. VLDB Journal 11(3), 216–237 (2002)zbMATHCrossRefGoogle Scholar
  12. 12.
    Cohen, S., Nutt, W., Serebrenik, A.: Rewriting aggregate queries using views. In: Proceedings of PODS, pp. 155–166 (1999)Google Scholar
  13. 13.
    Cornuejols, G., Nemhauser, G.L., Wolsey, L.A.: The uncapacitated facility location problem. Technical Report 605, Operations Research and Industrial Engineering, Cornell University (1984)Google Scholar
  14. 14.
    Fourer, R., Gay, D.M., Kernighan, B.W.: AMPL: A Modeling Language for Mathematical Programming. Boyd and Fraser, Danvers (2002)Google Scholar
  15. 15.
    Gray, J., Chaudhuri, S., Bosworth, A., Layman, A., Reichart, D., Venkatrao, M.: Data cube: A relational aggregation operator generalizing Group-by, Cross-Tab, and Sub Totals. Data Mining and Knowledge Discovery 1(1), 29–53 (1997)CrossRefGoogle Scholar
  16. 16.
    Gupta, A., Harinarayan, V., Quass, D.: Aggregate-query processing in data warehousing environments. In: Proceedings of VLDB, pp. 358–369 (1995)Google Scholar
  17. 17.
    Gupta, H., Harinarayan, V., Rajaraman, A., Ullman, J.D.: Index selection for OLAP. In: Proceedings of ICDE, pp. 208–219 (1997)Google Scholar
  18. 18.
    Halevy, A.Y.: Answering queries using views: A survey. VLDB Journal 10(4), 270–294 (2001)zbMATHCrossRefGoogle Scholar
  19. 19.
    Harinarayan, V., Rajaraman, A., Ullman, J.D.: Implementing data cubes efficiently. In: Proceedings of ACM SIGMOD, pp. 205–216 (1996)Google Scholar
  20. 20.
    IBM. Autonomic Computing,
  21. 21.
    Kimball, R., Ross, M.: The Data Warehouse Toolkit, 2nd edn. Wiley Computer Publishing, Chichester (2002)Google Scholar
  22. 22.
    Krarup, J., Pruzan, P.M.: The simple plant location problem: Survey and synthesis. European Journal of Operations Research 12, 36–81 (1983)zbMATHCrossRefMathSciNetGoogle Scholar
  23. 23.
    Li, J., Chirkova, R., Fathi, V.: An IP Model for the View Selection Problem. Technical report, NC State University (2005)Google Scholar
  24. 24.
    Mulvey, J.M., Crowder, H.P.: Cluster analysis: An application of lagrangian relaxation. Management Science 25, 329–340 (1979)zbMATHCrossRefGoogle Scholar
  25. 25.
    Parker, R.G., Rardin, R.L.: Discrete Optimization. Academic Press, London (1988)zbMATHGoogle Scholar
  26. 26.
    Microsoft Research AutoAdmin Project. Self-Tuning and Self-Administering Databases,
  27. 27.
    ILOG S.A. CPLEX 7.0 software package (2000),
  28. 28.
    Shasha, D., Bonnet, P.: Database Tuning: Principles, Experiments, and Troubleshooting Techniques. Morgan Kaufmann, San Francisco (2002)Google Scholar
  29. 29.
    Shukla, A., Deshpande, P., Naughton, J.F.: Materialized view selection for multidimensional datasets. In: Proceedings of VLDB, pp. 488–499 (1998)Google Scholar
  30. 30.
    Spielberg, K.: Algorithms for the simple plant location problem with some side constraints. Operations Research 17, 85–111 (1969)zbMATHCrossRefGoogle Scholar
  31. 31.
    Srivastava, D., Dar, S., Jagadish, H.V., Levy, A.Y.: Answering queries with aggregation using views. In: Proceedings of VLDB, pp. 318–329 (1996)Google Scholar
  32. 32.
    Theodoratos, D., Sellis, T.: Data warehouse configuration. In: Proceedings of VLDB, pp. 126–135 (1997)Google Scholar
  33. 33.
    TPC-H:. TPC Benchmark H (Decision Support), Available from
  34. 34.
    Widom, J.: Research problems in data warehousing. In: Proc. CIKM (1995)Google Scholar
  35. 35.
    Yang, J., Karlapalem, K., Li, Q.: Algorithms for materialized view design in data warehousing environment. In: Proceedings of VLDB, pp. 136–145 (1997)Google Scholar

Copyright information

© Springer-Verlag Berlin Heidelberg 2005

Authors and Affiliations

  • Jingni Li
    • 1
  • Zohreh Asgharzadeh Talebi
    • 1
  • Rada Chirkova
    • 2
  • Yahya Fathi
    • 1
  1. 1.Operations Research ProgramNC State UniversityRaleighUSA
  2. 2.Computer Science DepartmentNC State UniversityRaleighUSA

Personalised recommendations