Skip to main content
SpringerLink
Log in

View selection for real conjunctive queries

  • Original article
  • Published:
Acta Informatica Aims and scope Submit manuscript

Abstract

Given a query workload, a database and a set of constraints, the view-selection problem is to select views to materialize so that the constraints are satisfied and the views can be used to compute the queries in the workload efficiently. A typical constraint, which we consider in the present work, is to require that the views can be stored in a given amount of disk space. Depending on features of SQL queries (e.g., the DISTINCT keyword) and on whether the database relations on which the queries are applied are sets or bags, the queries may be computed under set semantics, bag-set semantics, or bag semantics. In this paper we study the complexity of the view-selection problem for conjunctive queries and views under these semantics. We show that bag semantics is the “easiest to handle” (we show that in this case the decision version of view selection is in NP), whereas under set and bag-set semantics we assume further restrictions on the query workload (we only allow queries without self-joins in the workload) to achieve the same complexity. Moreover, while under bag and bag-set semantics filtering views (i.e., subgoals that can be dropped from the rewriting without impacting equivalence to the query) are practically not needed, under set semantics filtering views can reduce significantly the query-evaluation costs. We show that under set semantics the decision version of the view-selection problem remains in NP only if filtering views are not allowed in the rewritings. Finally, we investigate whether the cgalg algorithm for view selection introduced in Chirkova and Genesereth (Linearly bounded reformulations of conjunctive databases, pp. 987–1001, 2000) is suitable in our setting. We prove that this algorithm is sound for all cases we examine here, and that it is complete under bag semantics for workloads of arbitrary conjunctive queries and under bag-set semantics for workloads of conjunctive queries without self-joins.

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

Similar content being viewed by others

References

  1. AutoAdmin: Self-tuning and self-administering databases. http://research.miosoft.com/dmx/autoadmin/default.asp

  2. Afrati, F., Chirkova, R.: Selecting and using views to computeaggregate queries. In: Database Theory - ICDT 2005 10th International Conference, Edinburgh, UK, January 5-7, 2005, Proceedings, Lecture Notes in Computer Science, vol. 3363, pp. 383–397 (2005)

  3. Afrati, F., Li, C., Ullman, J.: Generating efficient plans for queries using views. In: Proceedings of ACM SIGMOD, pp. 319–330 (2001)

  4. Agrawal, S., Chaudhuri, S., Narasayya, V.: Automated selection of materialized views and indexes in SQL databases. In: Proceedings of VLDB, pp. 496–505 (2000)

  5. Baralis, E., Paraboschi, S., Teniente, E.: Materialized views selection in a multidimensional database. In: Proceedings of VLDB, pp. 156–165 (1997)

  6. Chandra, A.K., Merlin, P.M.: Optimal implementation of conjunctive queries in relational databases. In: Proceedings of the 9th ACM symposium on theory of computing, pp. 77–90 (1977)

  7. Chaudhuri, S., Krishnamurthy, R., Potamianos, S., Shim, K.: Optimizing queries with materialized views. In: Proceedings of ICDE, pp. 190–200. Taipei, Taiwan (1995)

  8. Chaudhuri, S., Vardi, M.Y.: Optimization of real conjunctive queries. In: Proceedings of the 12th ACM SIGACT-SIGMOD-SIGART symposium on principles of database systems, pp. 59–70. ACM Press, New York (1993)

  9. Chirkova, R., Genesereth, M.: Linearly bounded reformulations of conjunctive databases. In: Proceedings of the first conference on computational logic, pp. 987–1001 (2000)

  10. Chirkova, R., Genesereth, M.: Database reformulation with integrity constraints. In: Proceedings of the logic and computational complexity workshop at the logic in computer science conference (LICS) (2005)

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

    Article  MATH  Google Scholar 

  12. Deutsch, A.: XML query reformulation over mixed and redundant storage. Ph.D. thesis, University of Pennsylvania (2002). Available at http://www.db.ucsd.edu/People/alin/thesis/thesis.pdf

  13. Garcia-Molina, H., Ullman, J., Widom, J.: Database systems: the complete book. Prentice Hall, Englewood Cliffs (2002)

  14. Grumbach S., Rafanelli M., Tininini L. (2004). On the equivalence and rewriting of aggregate queries. Acta Informatica 40(8): 529–584

    Article  MATH  Google Scholar 

  15. Gupta, H.: Selection of views to materialize in a data warehouse. In: Proceedings of ICDT, pp. 98–112 (1997)

  16. Gupta, H., Harinarayan, V., Rajaraman, A., Ullman, J.: Index selection for OLAP. In: Proceedings of ICDE, pp. 208–219 (1997)

  17. Gupta, H., Mumick, I.S.: Selection of views to materialize under a maintenance cost constraint. In: Proceedings of ICDT, pp. 453–470 (1999)

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

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

  20. Ioannidis Y., Ramakrishnan R. (1995). Containment of conjunctive queries: Beyond relations as sets. ACM Trans. Database Syst. 20(3): 288–324

    Article  Google Scholar 

  21. Karloff, H.J., Mihail, M.: On the complexity of the view-selection problem. In: Proceedings of PODS, pp. 167–173. Philadelphia, Pennsylvania (1999)

  22. Kossmann D. (2000). The state of the art in distributed query processing. ACM Comput. Surv. 32(4): 422–469

    Article  Google Scholar 

  23. Levy, A.Y., Mendelzon, A.O., Sagiv, Y., Srivastava, D.: Answering queries using views. In: Proceedings of the 14th ACM SIGACT-SIGMOD-SIGART symposium on principles of database systems, pp. 95–104. ACM Press, New York (1995)

  24. Li, C., Bawa, M., Ullman, J.D.: Minimizing view sets without losing query-answering power. In: Proceedings of ICDT, pp. 99–113 (2001)

  25. Pottinger R., Halevy A.Y. (2001). Minicon: a scalable algorithm for answering queries using views. VLDB J. 10(2-3): 182–198

    MATH  Google Scholar 

  26. Shasha, D., Bonnet, P.: Database tuning: principles, experiments, and troubleshooting techniques. Morgan Kaufmann (2002). http://www.distlab.dk/dbtune/

  27. Theodoratos, D., Sellis, T.: Data warehouse configuration. In: Proceedings of VLDB, pp. 126–135. Athens, Greece (1997)

  28. Yang, J., Karlapalem, K., Li, Q.: Algorithms for materialized view design in data warehousing environment. In: Proceedings of VLDB, pp. 136–145. Athens, Greece (1997)

Download references

Author information

Authors and Affiliations

  1. Department of Electrical and Computing Engineering, National Technical University of Athens (NTUA), 15773, Athens, Greece

    Foto Afrati & Vassia Pavlaki

  2. Computer Science Department, North Carolina State University, Campus Box 8206, Raleigh, NC, 27695-8206, USA

    Rada Chirkova

  3. Department of Archive and Library Sciences, Ionian University, Palea Anaktora, Plateia Eleftherias, 49100, Corfu, Greece

    Manolis Gergatsoulis

Authors
  1. Foto Afrati
    View author publications

    You can also search for this author in PubMed Google Scholar

  2. Rada Chirkova
    View author publications

    You can also search for this author in PubMed Google Scholar

  3. Manolis Gergatsoulis
    View author publications

    You can also search for this author in PubMed Google Scholar

  4. Vassia Pavlaki
    View author publications

    You can also search for this author in PubMed Google Scholar

Corresponding author

Correspondence to Manolis Gergatsoulis.

Additional information

Rada Chirkova’s work on this material has been supported by the National Science Foundation under Grant No. 0307072.

The project is co-funded by the European Social Fund (75%) and National Resources (25%)- Operational Program for Educational and Vocational Training II (EPEAEK II) and particularly the program PYTHAGORAS.

A preliminary version of this paper appears in F. Afrati, R. Chirkova, M. Gergatsoulis, V. Pavlaki. Designing Views to Efficiently Answer Real SQL Queries. In Proc. of SARA 2005, LNAI Vol. 3607, pages 332-346, Springer-Verlag, 2005.

Rights and permissions

Reprints and permissions

About this article

Cite this article

Afrati, F., Chirkova, R., Gergatsoulis, M. et al. View selection for real conjunctive queries. Acta Informatica 44, 289–321 (2007). https://doi.org/10.1007/s00236-007-0046-z

Download citation

  • Received:

  • Accepted:

  • Published:

  • Issue Date:

  • DOI: https://doi.org/10.1007/s00236-007-0046-z

Keywords

Search

Navigation