CORADD: Correlation Aware Database Designer for Materialized Views and Indexes
We describe an automatic database design tool that exploits correlations between attributes when recommending materialized views (MVs) and indexes. Although there is a substantial body of related work exploring how to select an appropriate set of MVs and indexes for a given workload, none of this wo...
| Main Authors: | , , , , |
|---|---|
| Other Authors: | |
| Format: | Article |
| Language: | en_US |
| Published: |
Association for Computing Machinery (ACM)
2012
|
| Online Access: | http://hdl.handle.net/1721.1/73500 https://orcid.org/0000-0002-7470-3265 |
| _version_ | 1811072566757949440 |
|---|---|
| author | Kimura, Hideaki Huo, George Rasin, Alexander Madden, Samuel R. Zdonik, Stanley B. |
| author2 | Massachusetts Institute of Technology. Department of Electrical Engineering and Computer Science |
| author_facet | Massachusetts Institute of Technology. Department of Electrical Engineering and Computer Science Kimura, Hideaki Huo, George Rasin, Alexander Madden, Samuel R. Zdonik, Stanley B. |
| author_sort | Kimura, Hideaki |
| collection | MIT |
| description | We describe an automatic database design tool that exploits correlations between attributes when recommending materialized views (MVs) and indexes. Although there is a substantial body of related work exploring how to select an appropriate set of MVs and indexes for a given workload, none of this work has explored the effect of correlated attributes (e.g., attributes encoding related geographic information) on designs. Our tool identifies a set of MVs and secondary indexes such that correlations between the clustered attributes of the MVs and the secondary indexes are enhanced, which can dramatically improve query performance. It uses a form of Integer Linear Programming (ILP) called ILP Feedback to pick the best set of MVs and indexes for given database size constraints. We compare our tool with a state-of-the-art commercial database designer on two workloads, APB-1 and SSB (Star Schema Benchmark---similar to TPC-H). Our results show that a correlation-aware database designer can improve query performance up to 6 times within the same space budget when compared to a commercial database designer. |
| first_indexed | 2024-09-23T09:08:05Z |
| format | Article |
| id | mit-1721.1/73500 |
| institution | Massachusetts Institute of Technology |
| language | en_US |
| last_indexed | 2024-09-23T09:08:05Z |
| publishDate | 2012 |
| publisher | Association for Computing Machinery (ACM) |
| record_format | dspace |
| spelling | mit-1721.1/735002022-09-30T13:38:04Z CORADD: Correlation Aware Database Designer for Materialized Views and Indexes Kimura, Hideaki Huo, George Rasin, Alexander Madden, Samuel R. Zdonik, Stanley B. Massachusetts Institute of Technology. Department of Electrical Engineering and Computer Science Madden, Samuel R. We describe an automatic database design tool that exploits correlations between attributes when recommending materialized views (MVs) and indexes. Although there is a substantial body of related work exploring how to select an appropriate set of MVs and indexes for a given workload, none of this work has explored the effect of correlated attributes (e.g., attributes encoding related geographic information) on designs. Our tool identifies a set of MVs and secondary indexes such that correlations between the clustered attributes of the MVs and the secondary indexes are enhanced, which can dramatically improve query performance. It uses a form of Integer Linear Programming (ILP) called ILP Feedback to pick the best set of MVs and indexes for given database size constraints. We compare our tool with a state-of-the-art commercial database designer on two workloads, APB-1 and SSB (Star Schema Benchmark---similar to TPC-H). Our results show that a correlation-aware database designer can improve query performance up to 6 times within the same space budget when compared to a commercial database designer. National Science Foundation (U.S.) (Grant IIS-0704424) SAP Corporation (Grant) 2012-10-01T15:16:09Z 2012-10-01T15:16:09Z 2010-09 Article http://purl.org/eprint/type/ConferencePaper 2150-8097 http://hdl.handle.net/1721.1/73500 Hideaki Kimura, George Huo, Alexander Rasin, Samuel Madden, and Stanley B. Zdonik. 2010. CORADD: correlation aware database designer for materialized views and indexes. Proc. VLDB Endow. 3, 1-2 (September 2010), 1103-1113. https://orcid.org/0000-0002-7470-3265 en_US http://dl.acm.org/citation.cfm?id=1920979 Proceedings of the VLDB Endowment Creative Commons Attribution-Noncommercial-Share Alike 3.0 http://creativecommons.org/licenses/by-nc-sa/3.0/ application/pdf Association for Computing Machinery (ACM) MIT web domain |
| spellingShingle | Kimura, Hideaki Huo, George Rasin, Alexander Madden, Samuel R. Zdonik, Stanley B. CORADD: Correlation Aware Database Designer for Materialized Views and Indexes |
| title | CORADD: Correlation Aware Database Designer for Materialized Views and Indexes |
| title_full | CORADD: Correlation Aware Database Designer for Materialized Views and Indexes |
| title_fullStr | CORADD: Correlation Aware Database Designer for Materialized Views and Indexes |
| title_full_unstemmed | CORADD: Correlation Aware Database Designer for Materialized Views and Indexes |
| title_short | CORADD: Correlation Aware Database Designer for Materialized Views and Indexes |
| title_sort | coradd correlation aware database designer for materialized views and indexes |
| url | http://hdl.handle.net/1721.1/73500 https://orcid.org/0000-0002-7470-3265 |
| work_keys_str_mv | AT kimurahideaki coraddcorrelationawaredatabasedesignerformaterializedviewsandindexes AT huogeorge coraddcorrelationawaredatabasedesignerformaterializedviewsandindexes AT rasinalexander coraddcorrelationawaredatabasedesignerformaterializedviewsandindexes AT maddensamuelr coraddcorrelationawaredatabasedesignerformaterializedviewsandindexes AT zdonikstanleyb coraddcorrelationawaredatabasedesignerformaterializedviewsandindexes |