Abstract
We consider the problem of selecting views and indexes that minimize the evaluation costs of the important queries under an upper bound on the disk space available for storing the views/indexes selected to be materialized. We propose a novel end-to-end approach that focuses on systematic exploration of plans for evaluating the queries. Specifically, we propose a framework (architecture) and algorithms that enable selection of views/indexes that contribute to the most efficient plans for the input queries, subject to the space bound. We present strong optimality guarantees on our architecture. Our algorithms search for sets of competitive plans for queries expressed in the language of conjunctive queries with arithmetic comparisons. This language captures the full expressive power of SQL select-project-join queries, which are common in practical database systems. Our experimental results demonstrate the competitiveness and scalability of our approach.
This is a preview of subscription content, log in via an institution to check access.
Access this chapter
Tax calculation will be finalised at checkout
Purchases are for personal use only
Preview
Unable to display preview. Download preview PDF.
References
Agrawal, S., Chaudhuri, S., Kollár, L., Marathe, A.P., Narasayya, V.R., Syamala, M.: Database tuning advisor for Microsoft SQL Server 2005. In: VLDB (2004)
Agrawal, S., Chaudhuri, S., Narasayya, V.R.: Automated selection of materialized views and indexes in SQL databases. In: VLDB, pp. 496–505 (2000)
Bruno, N., Chaudhuri, S.: Automatic physical database tuning: A relaxation-based approach. In: SIGMOD, pp. 227–238 (2005)
Bruno, N., Chaudhuri, S.: Physical design refinement: The merge-reduce approach. ACM Transactions on Database Systems 32(4), 28–43 (2007)
Balmin, A., Özcan, F., Beyer, K.S., Cochrane, R., Pirahesh, H.: A framework for using materialized XPath views in XML query processing. In: VLDB (2004)
Valentin, G., Zuliani, M., Zilio, D.C., Lohman, G.M., Skelley, A.: DB2 advisor: An optimizer smart enough to recommend its own indexes. In: ICDE (2000)
Zilio, D.C., Zuzarte, C., Lightstone, S., Ma, W., Lohman, G.M., Cochrane, R., Pirahesh, H., Colby, L.S., Gryz, J., Alton, E., Liang, D., Valentin, G.: Recommending views and indexes with IBM DB2 design advisor. In: ICAC (2004)
Chirkova, R.: Automated Database Restructuring. PhD thesis, Stanford U. (2002)
Chaudhuri, S., Datar, M., Narasayya, V.R.: Index selection for databases: A hardness study and principled heuristic solution. IEEE TKDE 16, 1313–1323 (2004)
ILOG: CPLEX Homepage (2004), http://www.ilog.com/products/cplex/
Gupta, H., Harinarayan, V., Rajaraman, A., Ullman, J.D.: Index selection for OLAP. In: ICDE (1997)
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 (1999)
Harinarayan, V., Rajaraman, A., Ullman, J.D.: Implementing data cubes efficiently. In: SIGMOD (1996)
Karloff, H.J., Mihail, M.: On the complexity of the view-selection problem. In: PODS (1999)
Asgharzadeh Talebi, Z., Chirkova, R., Fathi, Y., Stallmann, M.: Exact and inexact methods for selecting views and indexes for OLAP performance improvement. In: EDBT (2008)
Chaudhuri, S., Narasayya, V.R.: An efficient cost-driven index selection tool for Microsoft SQL server. In: VLDB, pp. 146–155 (1997)
Kormilitsin, M., Chirkova, R., Fathi, Y., Stallmann, M.: View and index selection for query-performance improvement: Quality-centered algorithms and heuristics. In: CIKM (2008)
Gupta, A., Mumick, I.S., Rao, J., Ross, K.: Adapting materialized views after redefinitions: techniques and a performance study. Inf. Sys. 26(5), 323–362 (2001)
Mistry, H., Roy, P., Sudarshan, S., Ramamritham, K.: Materialized view selection and maintenance using multi-query optimization. In: SIGMOD, pp. 307–318 (2001)
Roy, P., Seshadri, S., Sudarshan, S., Bhobe, S.: Efficient and extensible algorithms for multi query optimization. In: SIGMOD, pp. 249–260 (2000)
Bruno, N., Chaudhuri, S.: Online approach to physical design tuning. In: ICDE 2007 (2007)
Bruno, N., Chaudhuri, S.: Constrained physical design tuning. PVLDBÂ 1 (2008)
Kormilitsin, M., Chirkova, R., Fathi, Y., Stallmann, M.: Systematic exploration of efficient query plans for automated database restructuring. Technical Report TR-2009-8, NCSU (2009), http://www.csc.ncsu.edu/research/tech/reports.php
Gou, G., Kormilitsin, M., Chirkova, R.: Query evaluation using overlapping views: Completeness and efficiency. In: SIGMOD, pp. 37–48 (2006)
Chaudhuri, S., Krishnamurthy, R., Potamianos, S., Shim, K.: Optimizing queries with materialized views. In: ICDE, pp. 190–200 (1995)
Klug, A.: On conjunctive queries containing inequalities. J. ACM 35, 146–160 (1988)
Ono, K., Lohman, G.: Measuring the complexity of join enumeration in query optimization. In: VLDB, pp. 314–325 (1990)
Ausiello, G., Crescenzi, P., Gambosi, G., Kann, V., Marchetti-Spaccamela, A., Protasi, M.: Complexity and Approximation. Springer, Heidelberg (1999)
Selinger, P.G., Astrahan, M.M., Chamberlin, D.D., Lorie, R.A., Price, T.G.: Access path selection in a relational database management system. In: SIGMOD (1979)
TPC-H:: TPC Benchmark H, http://www.tpc.org/tpch/spec/tpch2.1.0.pdf
Author information
Authors and Affiliations
Editor information
Editors and Affiliations
Rights and permissions
Copyright information
© 2009 Springer-Verlag Berlin Heidelberg
About this paper
Cite this paper
Kormilitsin, M., Chirkova, R., Fathi, Y., Stallmann, M. (2009). Systematic Exploration of Efficient Query Plans for Automated Database Restructuring. In: Grundspenkis, J., Morzy, T., Vossen, G. (eds) Advances in Databases and Information Systems. ADBIS 2009. Lecture Notes in Computer Science, vol 5739. Springer, Berlin, Heidelberg. https://doi.org/10.1007/978-3-642-03973-7_11
Download citation
DOI: https://doi.org/10.1007/978-3-642-03973-7_11
Publisher Name: Springer, Berlin, Heidelberg
Print ISBN: 978-3-642-03972-0
Online ISBN: 978-3-642-03973-7
eBook Packages: Computer ScienceComputer Science (R0)