Abstract
Physical database design tools rely on a DBA-provided workload to pick an “optimal” set of indexes and materialized views. Such an approach fails to capture scenarios where DBAs are unable to produce a succinct workload for an automated tool but still able to suggest an ideal physical design based on their broad knowledge of the database usage. Unfortunately, in many cases such an ideal design violates important constraints (e.g., space) and needs to be refined. In this paper, we focus on the important problem of physical design refinement, which addresses the above and other related scenarios. We propose to solve the physical refinement problem by using a transformational architecture that is based upon two novel primitive operations, called merging and reduction. These operators help refine a configuration, treating indexes and materialized views in a unified way, as well as succinctly explain the refinement process to DBAs.
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., Kollar, L., Marathe, A., Narasayya, V., Syamala, M.: Database Tuning Advisor for Microsoft SQL Server 2005. In: Proceedings of the 30th International Conference on Very Large Databases, VLDB (2004)
Agrawal, S., Chaudhuri, S., Narasayya, V.: Automated selection of materialized views and indexes in SQL databases. In: Proceedings of the International Conference on Very Large Databases, VLDB (2000)
Brassard, G., Bratley, P.: Fundamental of Algorithmics. Prentice Hall, Englewood Cliffs (1996)
Bruno, N., Chaudhuri, S.: Automatic physical database tuning: A relaxationbased approach. In: Proceedings of the ACM International Conference on Management of Data, SIGMOD (2005)
Chaudhuri, S., Narasayya, V.: An efficient cost-driven index selection tool for Microsoft SQL Server. In: Proceedings of the 23rd International Conference on Very Large Databases, VLDB (1997)
Chaudhuri, S., Narasayya, V.: Index merging. In: Proceedings of the International Conference on Data Engineering, ICDE (1999)
Dageville, B., Das, D., Dias, K., Yagoub, K., Zait, M., Ziauddin, M.: Automatic SQL Tuning in Oracle 10g. In: Proceedings of the 30th International Conference on Very Large Databases, VLDB (2004)
Goldstein, J., Larson, P.-A.: Optimizing queries using materialized views: A practical, scalable solution. In: Proceedings of the ACM International Conference on Management of Data, SIGMOD (2001)
TPC Benchmark H. Decision support, Available at http://www.tpc.org
Valentin, G., Zuliani, M., Zilio, D., Lohman, G., Skelley, A.: DB2 advisor: An optimizer smart enough to recommend its own indexes. In: Proceedings of the International Conference on Data Engineering, ICDE (2000)
Zilio, D., et al.: DB2 design advisor: Integrated automatic physical database design. In: Proceedings of the 30th International Conference on Very Large Databases, VLDB (2004)
Zilio, D., Zuzarte, C., Lightstone, S., Ma, W., Lohman, G., Cochrane, R., Pirahesh, H., Colby, L., Gryz, J., Alton, E., Liang, D., Valentin, G.: Recommending materialized views and indexes with IBM DB2 design advisor. In: International Conference on Autonomic Computing (2004)
Author information
Authors and Affiliations
Editor information
Editors and Affiliations
Rights and permissions
Copyright information
© 2006 Springer-Verlag Berlin Heidelberg
About this paper
Cite this paper
Bruno, N., Chaudhuri, S. (2006). Physical Design Refinement: The “Merge-Reduce” Approach. In: Ioannidis, Y., et al. Advances in Database Technology - EDBT 2006. EDBT 2006. Lecture Notes in Computer Science, vol 3896. Springer, Berlin, Heidelberg. https://doi.org/10.1007/11687238_25
Download citation
DOI: https://doi.org/10.1007/11687238_25
Publisher Name: Springer, Berlin, Heidelberg
Print ISBN: 978-3-540-32960-2
Online ISBN: 978-3-540-32961-9
eBook Packages: Computer ScienceComputer Science (R0)