Skip to main content
SpringerLink
Log in
Book cover

East European Conference on Advances in Databases and Information Systems

ADBIS 2005: Advances in Databases and Information Systems pp 125–138Cite as

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

  • Conference paper

Part of the book series: Lecture Notes in Computer Science ((LNISA,volume 3631))

Abstract

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.

This is a preview of subscription content, log in via an institution.

Chapter
USD   29.95
Price excludes VAT (USA)
  • Available as PDF
  • Read on any device
  • Instant download
  • Own it forever
eBook
USD   39.99
Price excludes VAT (USA)
  • Available as PDF
  • Read on any device
  • Instant download
  • Own it forever
Softcover Book
USD   54.99
Price excludes VAT (USA)
  • Compact, lightweight edition
  • Dispatched in 3 to 5 business days
  • Free shipping worldwide - see info

Tax calculation will be finalised at checkout

Purchases are for personal use only

Learn about institutional subscriptions

Preview

Unable to display preview. Download preview PDF.

Unable to display preview. Download preview PDF.

References

  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)

    Chapter  Google Scholar 

  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. 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. 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. Baralis, E., Paraboschi, S., Teniente, E.: Materialized view selection in a multidimensional database. In: Proc. VLDB, pp. 156–165 (1997)

    Google Scholar 

  6. Barcelo, J., Casanovas, J.: A heuristic lagrangean algorithm for the capacitated plant location problem. European J. Operations Research 15, 212–226 (1984)

    Article  MATH  Google Scholar 

  7. Chaudhuri, S., Dayal, U.: An overview of data warehousing and OLAP technology. SIGMOD Record 26(1), 65–74 (1997)

    Article  Google Scholar 

  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. 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. Chaudhuri, S., Narasayya, V.R.: AutoAdmin ’What-if’ index analysis utility. In: Proceedings of ACM SIGMOD, pp. 367–378 (1998)

    Google Scholar 

  11. Chirkova, R., Halevy, A.Y., Suciu, D.: A formal perspective on the view selection problem. VLDB Journal 11(3), 216–237 (2002)

    Article  MATH  Google Scholar 

  12. Cohen, S., Nutt, W., Serebrenik, A.: Rewriting aggregate queries using views. In: Proceedings of PODS, pp. 155–166 (1999)

    Google Scholar 

  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. Fourer, R., Gay, D.M., Kernighan, B.W.: AMPL: A Modeling Language for Mathematical Programming. Boyd and Fraser, Danvers (2002)

    Google Scholar 

  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)

    Article  Google Scholar 

  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. Gupta, H., Harinarayan, V., Rajaraman, A., Ullman, J.D.: Index selection for OLAP. In: Proceedings of ICDE, pp. 208–219 (1997)

    Google Scholar 

  18. Halevy, A.Y.: Answering queries using views: A survey. VLDB Journal 10(4), 270–294 (2001)

    Article  MATH  Google Scholar 

  19. Harinarayan, V., Rajaraman, A., Ullman, J.D.: Implementing data cubes efficiently. In: Proceedings of ACM SIGMOD, pp. 205–216 (1996)

    Google Scholar 

  20. IBM. Autonomic Computing, http://www.research.ibm.com/autonomic/

  21. Kimball, R., Ross, M.: The Data Warehouse Toolkit, 2nd edn. Wiley Computer Publishing, Chichester (2002)

    Google Scholar 

  22. Krarup, J., Pruzan, P.M.: The simple plant location problem: Survey and synthesis. European Journal of Operations Research 12, 36–81 (1983)

    Article  MATH  MathSciNet  Google Scholar 

  23. Li, J., Chirkova, R., Fathi, V.: An IP Model for the View Selection Problem. Technical report, NC State University (2005)

    Google Scholar 

  24. Mulvey, J.M., Crowder, H.P.: Cluster analysis: An application of lagrangian relaxation. Management Science 25, 329–340 (1979)

    Article  MATH  Google Scholar 

  25. Parker, R.G., Rardin, R.L.: Discrete Optimization. Academic Press, London (1988)

    MATH  Google Scholar 

  26. Microsoft Research AutoAdmin Project. Self-Tuning and Self-Administering Databases, http://research.microsoft.com/dmx/autoadmin/default.asp

  27. ILOG S.A. CPLEX 7.0 software package (2000), http://www.ilog.com

  28. Shasha, D., Bonnet, P.: Database Tuning: Principles, Experiments, and Troubleshooting Techniques. Morgan Kaufmann, San Francisco (2002)

    Google Scholar 

  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. Spielberg, K.: Algorithms for the simple plant location problem with some side constraints. Operations Research 17, 85–111 (1969)

    Article  MATH  Google Scholar 

  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. Theodoratos, D., Sellis, T.: Data warehouse configuration. In: Proceedings of VLDB, pp. 126–135 (1997)

    Google Scholar 

  33. TPC-H:. TPC Benchmark H (Decision Support), Available from http://www.tpc.org/tpch/spec/tpch2.1.0.pdf

  34. Widom, J.: Research problems in data warehousing. In: Proc. CIKM (1995)

    Google Scholar 

  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 

Download references

Author information

Authors and Affiliations

  1. Operations Research Program, NC State University, Raleigh, NC, 27695, USA

    Jingni Li, Zohreh Asgharzadeh Talebi & Yahya Fathi

  2. Computer Science Department, NC State University, Raleigh, NC, 27695, USA

    Rada Chirkova

Authors
  1. Jingni Li
    View author publications

    You can also search for this author in PubMed Google Scholar

  2. Zohreh Asgharzadeh Talebi
    View author publications

    You can also search for this author in PubMed Google Scholar

  3. Rada Chirkova
    View author publications

    You can also search for this author in PubMed Google Scholar

  4. Yahya Fathi
    View author publications

    You can also search for this author in PubMed Google Scholar

Editor information

Editors and Affiliations

  1. Dept. of Knowledge and Business Engineering, University of Vienna, Rathausstrasse 19/9, A-1010, Vienna, Austria

    Johann Eder

  2. Institute of Cybernetics, Tallinn University of Technology, Akadeemia 21, 12618, Tallinn, Estonia

    Hele-Mai Haav , Ahto Kalja  & Jaan Penjam ,  & 

Rights and permissions

Reprints and permissions

Copyright information

© 2005 Springer-Verlag Berlin Heidelberg

About this paper

Cite this paper

Li, J., Talebi, Z.A., Chirkova, R., Fathi, Y. (2005). A Formal Model for the Problem of View Selection for Aggregate Queries. In: Eder, J., Haav, HM., Kalja, A., Penjam, J. (eds) Advances in Databases and Information Systems. ADBIS 2005. Lecture Notes in Computer Science, vol 3631. Springer, Berlin, Heidelberg. https://doi.org/10.1007/11547686_10

Download citation

  • DOI: https://doi.org/10.1007/11547686_10

  • Publisher Name: Springer, Berlin, Heidelberg

  • Print ISBN: 978-3-540-28585-4

  • Online ISBN: 978-3-540-31895-8

  • eBook Packages: Computer ScienceComputer Science (R0)

Publish with us

Policies and ethics

Search

Navigation