Group-By result size estimation
View Patent ↗A method and system for accurately estimating a result size of a Group-By operation in a relational database. The estimate utilizes the probability of union of the columns involved in the operation, as well as the relative cardinality of each column with respect to the other columns in the operation. In addition, the estimate incorporates the use of table filters when indicated such that table filters are applied prior to determining the size of the tables in the operation, as well as including equivalent columns into the list of columns that are a part of the Group-By operation. Accordingly, the estimate of the result size of the operation includes influencing factors that provide an accurate estimation of system memory requirements.
1. A computer system having a Group-By operation size estimator comprising:
a processor in communication with storage media;
said storage media having a database with data records maintained in tables;
a selectivity manager in communication with said database, said selectivity manager adapted to calculate a cumulative selectivity based upon an aggregation of selectivity of an individual column in a group of tables in a Group-By operation, wherein the step of calculating a cumulative selectivity is based upon the following mathematical relationship: S ab =S a +S b −(S a ×S b ), wherein S a is a selectivity of column “a”, S b is the selectivity of column “b”, and S ab is a cumulative selectivity of columns “a” and column “b”; and
a result size manager in communication with said database adapted to receive said calculated cumulative selectivity from said selectivity manager, to estimate a size of said Group-By operation as a product of said calculated cumulative selectivity and an input size of said Group-By operation, to compute a memory requirement for said Group-By operation based on said estimated size of said Group-By operation, and to allocate memory for said operation based upon said memory requirement.
2. The estimator of claim 1 , wherein said selectivity manager is adapted to normalize a selectivity for each column in said group.
3. The estimator of claim 2 , wherein normalization of said selectivity includes a weight factor adapted to be applied to said cumulative selectivity calculation.
4. The estimator of claim 3 , wherein said weight factor includes a relative size of a table in which said column resides.
5. The estimator of claim 1 , wherein said selectivity manager is adapted to iteratively apply said mathematical relationship for each additional column in said group.
6. The estimator of claim 1 , wherein said selectivity manager is adapted to include equivalent columns of said group based upon query predicates.
7. An article comprising:
a database stored on a computer-readable data storage medium with data records maintained in multiple tables;
means in the medium for calculating a cumulative selectivity of each column in a group of tables in a Group-By operation, wherein the step of calculating a cumulative selectivity is based upon the following mathematical relationship: S ab =S a +S b −(S a ×S b ), wherein S a is a selectivity of column “a”, S b is the selectivity of column “b”, and S ab is a cumulative selectivity of columns “a” and column “b”;
means in the medium for estimating a result size of said operation based upon said cumulative selectivity;
means in the medium for computing a memory requirement for said Group-By operation based on said estimated result size; and
means in the medium for allocating memory for said operation based upon said memory requirement.
8. The article of claim 7 , wherein said means for calculating said cumulative selectivity includes means for normalizing a selectivity for each column in said group.
9. The article of claim 8 , wherein said means for normalizing said selectivity includes a weight factor based upon of a relative size of a table of said column.
10. The article of claim 7 , wherein said means for calculating said cumulative selectivity is inclusive of equivalent columns.