Physical Design Refinement: The “Merge-Reduce” Approach

  • Nicolas Bruno
  • Surajit Chaudhuri
Part of the Lecture Notes in Computer Science book series (LNCS, volume 3896)

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.

Keywords

Physical Design View Versus Merging Operation Storage Constraint Merging Operator 
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.
    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)Google Scholar
  2. 2.
    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)Google Scholar
  3. 3.
    Brassard, G., Bratley, P.: Fundamental of Algorithmics. Prentice Hall, Englewood Cliffs (1996)Google Scholar
  4. 4.
    Bruno, N., Chaudhuri, S.: Automatic physical database tuning: A relaxationbased approach. In: Proceedings of the ACM International Conference on Management of Data, SIGMOD (2005)Google Scholar
  5. 5.
    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)Google Scholar
  6. 6.
    Chaudhuri, S., Narasayya, V.: Index merging. In: Proceedings of the International Conference on Data Engineering, ICDE (1999)Google Scholar
  7. 7.
    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)Google Scholar
  8. 8.
    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)Google Scholar
  9. 9.
    TPC Benchmark H. Decision support, Available at http://www.tpc.org
  10. 10.
    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)Google Scholar
  11. 11.
    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)Google Scholar
  12. 12.
    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)Google Scholar

Copyright information

© Springer-Verlag Berlin Heidelberg 2006

Authors and Affiliations

  • Nicolas Bruno
    • 1
  • Surajit Chaudhuri
    • 1
  1. 1.Microsoft ResearchRedmondUSA

Personalised recommendations