Coradd

Abstract

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.

AMNES: Accelerating the Computation of Data Correlation Using FPGAs

“…The first is the semantic relationship (e.g., a functional dependency) between columns. Hermit [77], Correlation Maps [38], CORDS [30], BHUNT [10], CORADD [39] use attributes' semantic correlation to improve indexing, query execution, and query optimization [51] performance. The second concept uses the two attributes covariance to model selectivity for query optimizers [14].…”

“…Later in the paper, we show how to compute the correlation for 64 streams and 2 million elements per variable in about 10 ms, i.e., an order of magnitude less than these established libraries. In databases, due to the cost of computing correlation, it is often approximated, especially when used to optimize the creation of indexes, as data correlations can significantly affect their performance, particularly for clustered indexes [17,39]. One common approximation is to compare the number of distinct values across attributes [39].…”

“…In databases, due to the cost of computing correlation, it is often approximated, especially when used to optimize the creation of indexes, as data correlations can significantly affect their performance, particularly for clustered indexes [17,39]. One common approximation is to compare the number of distinct values across attributes [39]. However, this measure is far less precise than statistical correlation measures.…”

Tsunami

“…BHUNT [5], CORDS [17], and Pyro [24] automatically discover algebraic constraints, soft functional dependencies, and approximate dependencies between columns, respectively. CORADD [21] recommends materialized views and indexes based on correlations. Correlation Map [20] aims to reduce the size of B+Tree secondary indexes by creating a mapping between correlated dimensions.…”