Advertisement

Acta Informatica

, Volume 44, Issue 5, pp 289–321 | Cite as

View selection for real conjunctive queries

  • Foto Afrati
  • Rada Chirkova
  • Manolis Gergatsoulis
  • Vassia Pavlaki
Original article

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.

Keywords

Query Evaluation Decision Version Conjunctive Query Query Plan Storage Limit 
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.

Preview

Unable to display preview. Download preview PDF.

Unable to display preview. Download preview PDF.

References

  1. 1.
    AutoAdmin: Self-tuning and self-administering databases. http://research.miosoft.com/dmx/autoadmin/default.asp
  2. 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)Google Scholar
  3. 3.
    Afrati, F., Li, C., Ullman, J.: Generating efficient plans for queries using views. In: Proceedings of ACM SIGMOD, pp. 319–330 (2001)Google Scholar
  4. 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)Google Scholar
  5. 5.
    Baralis, E., Paraboschi, S., Teniente, E.: Materialized views selection in a multidimensional database. In: Proceedings of VLDB, pp. 156–165 (1997)Google Scholar
  6. 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)Google Scholar
  7. 7.
    Chaudhuri, S., Krishnamurthy, R., Potamianos, S., Shim, K.: Optimizing queries with materialized views. In: Proceedings of ICDE, pp. 190–200. Taipei, Taiwan (1995)Google Scholar
  8. 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)Google Scholar
  9. 9.
    Chirkova, R., Genesereth, M.: Linearly bounded reformulations of conjunctive databases. In: Proceedings of the first conference on computational logic, pp. 987–1001 (2000)Google Scholar
  10. 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)Google Scholar
  11. 11.
    Chirkova R., Halevy A.Y., Suciu D. (2002). A formal perspective on the view selection problem. VLDB J. 11(3): 216–237 zbMATHCrossRefGoogle Scholar
  12. 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. 13.
    Garcia-Molina, H., Ullman, J., Widom, J.: Database systems: the complete book. Prentice Hall, Englewood Cliffs (2002)Google Scholar
  14. 14.
    Grumbach S., Rafanelli M., Tininini L. (2004). On the equivalence and rewriting of aggregate queries. Acta Informatica 40(8): 529–584 zbMATHCrossRefGoogle Scholar
  15. 15.
    Gupta, H.: Selection of views to materialize in a data warehouse. In: Proceedings of ICDT, pp. 98–112 (1997)Google Scholar
  16. 16.
    Gupta, H., Harinarayan, V., Rajaraman, A., Ullman, J.: Index selection for OLAP. In: Proceedings of ICDE, pp. 208–219 (1997)Google Scholar
  17. 17.
    Gupta, H., Mumick, I.S.: Selection of views to materialize under a maintenance cost constraint. In: Proceedings of ICDT, pp. 453–470 (1999)Google Scholar
  18. 18.
    Harinarayan, V., Rajaraman, A., Ullman, J.: Implementing data cubes efficiently. In: Proceedings of ACM SIGMOD, pp. 205–216 (1996)Google Scholar
  19. 19.
    IBM: Autonomic Computing. http://www.research.ibm.com/autonomic/
  20. 20.
    Ioannidis Y., Ramakrishnan R. (1995). Containment of conjunctive queries: Beyond relations as sets. ACM Trans. Database Syst. 20(3): 288–324 CrossRefGoogle Scholar
  21. 21.
    Karloff, H.J., Mihail, M.: On the complexity of the view-selection problem. In: Proceedings of PODS, pp. 167–173. Philadelphia, Pennsylvania (1999)Google Scholar
  22. 22.
    Kossmann D. (2000). The state of the art in distributed query processing. ACM Comput. Surv. 32(4): 422–469 CrossRefGoogle Scholar
  23. 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)Google Scholar
  24. 24.
    Li, C., Bawa, M., Ullman, J.D.: Minimizing view sets without losing query-answering power. In: Proceedings of ICDT, pp. 99–113 (2001)Google Scholar
  25. 25.
    Pottinger R., Halevy A.Y. (2001). Minicon: a scalable algorithm for answering queries using views. VLDB J. 10(2-3): 182–198 zbMATHGoogle Scholar
  26. 26.
    Shasha, D., Bonnet, P.: Database tuning: principles, experiments, and troubleshooting techniques. Morgan Kaufmann (2002). http://www.distlab.dk/dbtune/
  27. 27.
    Theodoratos, D., Sellis, T.: Data warehouse configuration. In: Proceedings of VLDB, pp. 126–135. Athens, Greece (1997)Google Scholar
  28. 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)Google Scholar

Copyright information

© Springer-Verlag 2007

Authors and Affiliations

  • Foto Afrati
    • 1
  • Rada Chirkova
    • 2
  • Manolis Gergatsoulis
    • 3
  • Vassia Pavlaki
    • 1
  1. 1.Department of Electrical and Computing EngineeringNational Technical University of Athens (NTUA)AthensGreece
  2. 2.Computer Science DepartmentNorth Carolina State UniversityRaleighUSA
  3. 3.Department of Archive and Library SciencesIonian UniversityCorfuGreece

Personalised recommendations