Compression aware physical database design
Authors:
Open Access
- 1 July 2011
- journal article
- Published by Association for Computing Machinery (ACM) in Proceedings of the VLDB Endowment
- Vol. 4 (10) , 657-668
Abstract
Modern RDBMSs support the ability to compress data using methods such as null suppression and dictionary encoding. Data compression offers the promise of significantly reducing storage requirements and improving I/O performance for decision support queries. However, compression can also slow down update and query performance due to the CPU costs of compression and decompression. In this paper, we study how data compression affects choice of appropriate physical database design, such as indexes, for a given workload. We observe that approaches that decouple the decision of whether or not to choose an index from whether or not to compress the index can result in poor solutions. Thus, we focus on the novel problem of integrating compression into physical database design in a scalable manner. We have implemented our techniques by modifying Microsoft SQL Server and the Database Engine Tuning Advisor (DTA) physical design tool. Our techniques are general and are potentially applicable to DBMSs that support other compression methods. Our experimental results on real world as well as TPC-H benchmark workloads demonstrate the effectiveness of our techniques.Keywords
All Related Versions
This publication has 8 references indexed in Scilit:
- Estimating the compression fraction of an index using sampling2010
- Efficient index compression in DB2 LUWProceedings of the VLDB Endowment, 2009
- Integrating compression and execution in column-oriented database systems2006
- Database tuning advisor for microsoft SQL server 20052005
- DB2 Design Advisor2004
- Data Compression in Oracle2003
- Towards estimation error guarantees for distinct values2000
- Join synopses for approximate query answering1999