Sanjay Agrawal
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.
- Agrawal, R., Ramakrishnan, S. Fast Algorithms for Mining Association Rules in Large Databases. Proc. of VLDB 1994. Google Scholar
Digital Library
- Agrawal, S., Chaudhuri, S., and Narasayya. V. Automated Selection of Materialized Views and Indexes for SQL Databases. Proceedings of VLDB 2000. Google ScholarDigital Library
- Ailamaki A., Dewitt D. J., Hill M. D., and Skounakis M. Weaving Relations for Cache Performance. VLDB 2001. Google ScholarDigital Library
- Chaudhuri, S., Narasayya, V. An Efficient Cost-Driven Index Selection Tool for Microsoft SQL Server. VLDB 1997. Google ScholarDigital Library
- Chaudhuri, S., and Narasayya, V. AutoAdmin "What-If" Index Analysis Utility. Proc. of ACM SIGMOD 1998. Google ScholarDigital Library
- Chaudhuri, S., and Narasayya, V. Index Merging. Proceedings of ICDE 1999. Google ScholarDigital Library
- 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. Google ScholarDigital Library
- 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. Google ScholarDigital Library
- Gupta H., Harinarayan V., Rajaramana A., and Ullman J. D. Index Selection for OLAP. Proc. ICDE 1997. Google ScholarDigital Library
- Navathe S., Ra M. Vertical Partitioning for Database Design: A Graphical Algorithm. Proc. of SIGMOD 1989. Google ScholarDigital Library
- http://otn.oracle.com/products/oracle9i/index.html.Google Scholar
- http://research.microsoft.com/~gray/dbgen/.Google Scholar
- http://www.olapcouncil.org/research/bmarkco.htm.Google Scholar
- 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.Google Scholar
- Program for TPC-D data generation with Skew. ftp://ftp.research.microsoft.com/users/viveknar/TPCDSkew/.Google Scholar
- Ramamurthy R., Dewitt D. J., and Su Q. A Case for Fractured Mirrors. Proceedings of VLDB 2002. Google ScholarDigital Library
- Rao, J., Zhang, C., Lohman, G., and Megiddo, N. Automating Physical Database Design in a Parallel Database. Proceedings of the ACM SIGMOD 2002. Google ScholarDigital Library
- Sacca D., and Wiederhold G. Database Partitioning in a Cluster of Processors. ACM TODS, Vol 10, No 1, Mar 1985. Google ScholarDigital Library
- Stohr T., Martens H., and Rahm E., Multi-Dimensional Aware Database Allocation for Parallel Data Warehouses. Proceedings of VLDB 2000. Google ScholarDigital Library
- TPC Benchmark H. Decision Support. http://www.tpc.orgGoogle Scholar
- 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. Google ScholarDigital Library
- Zeller, B., and Kemper, A. Experience Report. Exploiting Advanced Database Optimization Features for Large-Scale SAP R/3 Installations. Proceedings of VLDB 2002. Google ScholarDigital Library
- Zilio, D. Physical Database Design Decision Algorithms and Concurrent Reoganization for Parallel Database Systems. PhD Thesis, Dept. of Comp. Sc., Univ. of Toronto, 1998. Google ScholarDigital Library
- Zilio, D., Jhingran, A., Padmanabhan, S. Partitioning Key Selection for Shared-Nothing Parallel Database System. IBM Research Report RC 19820. 1994.Google Scholar
- Integrating vertical and horizontal partitioning into automated physical database design
Recommendations
Vertical partitioning algorithms for database design
This paper addresses the vertical partitioning of a set of logical records or a relation into fragments. The rationale behind vertical partitioning is to produce fragments, groups of attribute columns, that “closely match” the requirements of ...
Options in physical database design
A cornerstone of modern database systems is physical data independence, i.e., the separation of a type and its associated operations from its physical representation in memory and on storage media. Users manipulate and query data at the logical level; ...
Comments