Optimal Clustering of Relations to Improve Sorting and Partitioning for Joins

The sorting or partitioning of relations is very common in relational database systems. Implementations of the join operation include the sort–merge join algorithm, which sorts both relations, and the hash join algorithm, which usually partitions both relations. We describe how clustering records using an optimal multi-attribute hash (MAH) file, taking the query pattern and distribution into account, reduces the average cost of sorting or partitioning. We demonstrate that maintaining multiple copies of a data file, each with a different clustering organization, further reduces the average cost of sorting or partitioning. We describe an inexpensive method for determining a good partitioning index (MAH file organization). Our analysis and experiments show that the partitioning indexes we find are usually optimal and can often partition a relation more than ten times faster than by not using any clustering. We also show that a significant change in the query pattern or distribution is required before a reorganization of a data file is necessary, and that such a reorganization is, in general, an inexpensive operation.