Pragmatic estimation of join sizes and attribute correlations

Abstract

A new method is presented of modelling attribute value distributions in database relations for the purpose of obtaining accurate estimates of intermediate relation sizes during query evaluation. The basic idea is that instead of keeping a single (average) value to represent the number of occurrences of each attribute value, we keep m (typically 10) parameters, each representing the number of occurrences of attribute values in a piece, or partition, corresponding to a sub-range of l/m th of the original value range. The "uniformity assumption", taken here as an estimation technique rather than as an assumption, holds for each partitionhence the name "piecewise uniform".The piecewise uniform method is independent of the level of the query tree at which the estimation is done and gives better estimates than can be obtained by the uniformity assumption. We establish some of the conditions under which this improvement occurs by analysis and back this up by experimental results.The distribution method is extended to the modelling of important intra-relational attribute correlations. This and other enhancements to the technique such as for application to semi-join operation are suggested.The technique is being used on two multi-database management systems.