skip to main content
10.1145/1007568.1007609acmconferencesArticle/Chapter ViewAbstractPublication PagesmodConference Proceedingsconference-collections
Article

Integrating vertical and horizontal partitioning into automated physical database design

Published: 13 June 2004 Publication History

Abstract

In addition to indexes and materialized views, horizontal and vertical partitioning are important aspects of physical design in a relational database system that significantly impact performance. Horizontal partitioning also provides manageability; database administrators often require indexes and their underlying tables partitioned identically so as to make common operations such as backup/restore easier. While partitioning is important, incorporating partitioning makes the problem of automating physical design much harder since: (a) The choices of partitioning can strongly interact with choices of indexes and materialized views. (b) A large new space of physical design alternatives must be considered. (c) Manageability requirements impose a new constraint on the problem. In this paper, we present novel techniques for designing a scalable solution to this integrated physical design problem that takes both performance and manageability into account. We have implemented our techniques and evaluated it on Microsoft SQL Server. Our experiments highlight: (a) the importance of taking an integrated approach to automated physical design and (b) the scalability of our techniques.

References

[1]
Agrawal, R., Ramakrishnan, S. Fast Algorithms for Mining Association Rules in Large Databases. Proc. of VLDB 1994.
[2]
Agrawal, S., Chaudhuri, S., and Narasayya. V. Automated Selection of Materialized Views and Indexes for SQL Databases. Proceedings of VLDB 2000.
[3]
Ailamaki A., Dewitt D. J., Hill M. D., and Skounakis M. Weaving Relations for Cache Performance. VLDB 2001.
[4]
Chaudhuri, S., Narasayya, V. An Efficient Cost-Driven Index Selection Tool for Microsoft SQL Server. VLDB 1997.
[5]
Chaudhuri, S., and Narasayya, V. AutoAdmin "What-If" Index Analysis Utility. Proc. of ACM SIGMOD 1998.
[6]
Chaudhuri, S., and Narasayya, V. Index Merging. Proceedings of ICDE 1999.
[7]
Cornell D. W., Yu P. S. An Effective Approach to Vertical Partitioning for Physical Design of Relational Databases. IEEE Transactions on Software Engg, Vol 16, No 2, 1990.
[8]
De P., Park J. S., and Pirkul H. An Integrated Model of Record Segmentation and Access Path Selection for Databases. Information Systems, Vol 13 No. 1, 1988.
[9]
Gupta H., Harinarayan V., Rajaramana A., and Ullman J. D. Index Selection for OLAP. Proc. ICDE 1997.
[10]
Navathe S., Ra M. Vertical Partitioning for Database Design: A Graphical Algorithm. Proc. of SIGMOD 1989.
[11]
http://otn.oracle.com/products/oracle9i/index.html.
[12]
http://research.microsoft.com/~gray/dbgen/.
[13]
http://www.olapcouncil.org/research/bmarkco.htm.
[14]
Papadomanolakis, E., and Ailamaki A. AutoPart: Automating Schema Design for Large Scientific Databases Using Data Partitioning. CMU Technical Report. CMU-CS-03-159, July 2003.
[15]
Program for TPC-D data generation with Skew. ftp://ftp.research.microsoft.com/users/viveknar/TPCDSkew/.
[16]
Ramamurthy R., Dewitt D. J., and Su Q. A Case for Fractured Mirrors. Proceedings of VLDB 2002.
[17]
Rao, J., Zhang, C., Lohman, G., and Megiddo, N. Automating Physical Database Design in a Parallel Database. Proceedings of the ACM SIGMOD 2002.
[18]
Sacca D., and Wiederhold G. Database Partitioning in a Cluster of Processors. ACM TODS, Vol 10, No 1, Mar 1985.
[19]
Stohr T., Martens H., and Rahm E., Multi-Dimensional Aware Database Allocation for Parallel Data Warehouses. Proceedings of VLDB 2000.
[20]
TPC Benchmark H. Decision Support. http://www.tpc.org
[21]
Valentin, G., Zuliani, M., Zilio, D., and Lohman, G. DB2 Advisor: An Optimizer That is Smart Enough to Recommend Its Own Indexes. Proceedings of ICDE 2000.
[22]
Zeller, B., and Kemper, A. Experience Report. Exploiting Advanced Database Optimization Features for Large-Scale SAP R/3 Installations. Proceedings of VLDB 2002.
[23]
Zilio, D. Physical Database Design Decision Algorithms and Concurrent Reoganization for Parallel Database Systems. PhD Thesis, Dept. of Comp. Sc., Univ. of Toronto, 1998.
[24]
Zilio, D., Jhingran, A., Padmanabhan, S. Partitioning Key Selection for Shared-Nothing Parallel Database System. IBM Research Report RC 19820. 1994.

Cited By

View all
  • (2024)Self-tuning Database Systems: A Systematic Literature Review of Automatic Database Schema Design and TuningACM Computing Surveys10.1145/3665323Online publication date: 17-May-2024
  • (2024)Limousine: Blending Learned and Classical Indexes to Self-Design Larger-than-Memory Cloud Storage EnginesProceedings of the ACM on Management of Data10.1145/36393022:1(1-28)Online publication date: 26-Mar-2024
  • (2024)Enhancing Storage Efficiency and Performance: A Survey of Data Partitioning TechniquesJournal of Computer Science and Technology10.1007/s11390-024-3538-139:2(346-368)Online publication date: 1-Mar-2024
  • Show More Cited By

Recommendations

Comments

Information & Contributors

Information

Published In

cover image ACM Conferences
SIGMOD '04: Proceedings of the 2004 ACM SIGMOD international conference on Management of data
June 2004
988 pages
ISBN:1581138598
DOI:10.1145/1007568
Permission to make digital or hard copies of all or part of this work for personal or classroom use is granted without fee provided that copies are not made or distributed for profit or commercial advantage and that copies bear this notice and the full citation on the first page. Copyrights for components of this work owned by others than ACM must be honored. Abstracting with credit is permitted. To copy otherwise, or republish, to post on servers or to redistribute to lists, requires prior specific permission and/or a fee. Request permissions from [email protected]

Sponsors

Publisher

Association for Computing Machinery

New York, NY, United States

Publication History

Published: 13 June 2004

Permissions

Request permissions for this article.

Check for updates

Qualifiers

  • Article

Conference

SIGMOD/PODS04
Sponsor:

Acceptance Rates

Overall Acceptance Rate 785 of 4,003 submissions, 20%

Contributors

Other Metrics

Bibliometrics & Citations

Bibliometrics

Article Metrics

  • Downloads (Last 12 months)69
  • Downloads (Last 6 weeks)8
Reflects downloads up to 07 Jan 2025

Other Metrics

Citations

Cited By

View all
  • (2024)Self-tuning Database Systems: A Systematic Literature Review of Automatic Database Schema Design and TuningACM Computing Surveys10.1145/3665323Online publication date: 17-May-2024
  • (2024)Limousine: Blending Learned and Classical Indexes to Self-Design Larger-than-Memory Cloud Storage EnginesProceedings of the ACM on Management of Data10.1145/36393022:1(1-28)Online publication date: 26-Mar-2024
  • (2024)Enhancing Storage Efficiency and Performance: A Survey of Data Partitioning TechniquesJournal of Computer Science and Technology10.1007/s11390-024-3538-139:2(346-368)Online publication date: 1-Mar-2024
  • (2023)Progressive Partitioning for Parallelized Query Execution in Google's NapaProceedings of the VLDB Endowment10.14778/3611540.361154116:12(3475-3487)Online publication date: 12-Sep-2023
  • (2023)Pando: Enhanced Data Skipping with Logical Data PartitioningProceedings of the VLDB Endowment10.14778/3598581.359860116:9(2316-2329)Online publication date: 1-May-2023
  • (2023)Semantic Slicing across the Distributed Intelligent 6G Wireless Networks2023 20th Annual IEEE International Conference on Sensing, Communication, and Networking (SECON)10.1109/SECON58729.2023.10287519(79-84)Online publication date: 11-Sep-2023
  • (2023)Physical Database Design for Manufacturing Business Analytics2023 IEEE International Conference on Big Data (BigData)10.1109/BigData59044.2023.10386475(1793-1802)Online publication date: 15-Dec-2023
  • (2023)RCBench: an RDMA-enabled transaction framework for analyzing concurrency control algorithmsThe VLDB Journal10.1007/s00778-023-00821-033:2(543-567)Online publication date: 14-Dec-2023
  • (2023)Towards a Workload Mapping Model for Tuning Backing Services in Cloud SystemsDatabase and Expert Systems Applications10.1007/978-3-031-39847-6_19(266-280)Online publication date: 18-Aug-2023
  • (2022)SageDBProceedings of the VLDB Endowment10.14778/3565838.356585715:13(4062-4078)Online publication date: 1-Sep-2022
  • Show More Cited By

View Options

Login options

View options

PDF

View or Download as a PDF file.

PDF

eReader

View online with eReader.

eReader

Media

Figures

Other

Tables

Share

Share

Share this Publication link

Share on social media