Group-by size result 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 method for estimating a result size of a Group-By operation comprising:
organizing a database stored on a computer readable data storage medium with data records maintained in multiple tables and defining a selectivity for each column as a number of distinct values in a column divided by the number of rows in a table;
calculating a cumulative selectivity for a Group-By operation based upon aggregation of normalized selectivity of each column in a group of tables in the Group-By operation, including adding to a Group-By list columns equivalent to columns specified in the Group-By operation, said equivalent columns unspecified in the Group-By operation and accounting for dependency of columns originating from a same table in said Group-By operation by subtracting a product of normalized selectivities for said columns originating from the same table from a sum of normalized selectivities for said columns;
computing a memory requirement for said Group-By operation based on said calculated cumulative selectivity; and
allocating memory for said Group-By operation based upon said memory requirement.
2. The method of claim 1 , further comprising normalizing said selectivity for each column.
3. The method of claim 2 , wherein the step of normalizing said selectivity for each column includes applying a weight factor to said selectivity based upon a relative size of the table in which said column resides.
4. The method of claim 1 , further comprising multiplying said calculated cumulative selectivity by an input size of said Group-By operation.
5. The method of claim 1 , wherein the step of calculating the cumulative selectivity includes determining equivalent columns in the Group-By list based upon an equal join predicate.
6. A computer system 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 calculating a cumulative selectivity for a Group-By operation based upon an aggregation of individual normalized selectivity of each column in a group of tables in the Group-By operation, an addition to a Group-By list columns equivalent to columns specified in the Group-By operation, said equivalent columns unspecified in the Group-By operation and accounting for dependency of columns originating from a same table in said Group-By operation by subtracting a product of individual normalized selectivities for said columns originating from the same table from a sum of normalized selectivities for said columns; and
a result size manager in communication with said selectivity manager and said database to compute a memory requirement for said Group-By operation based on said calculated cumulative selectivity, and to allocate memory for said Group-By operation based upon said memory requirement.
7. The system of claim 6 , wherein said selectivity manager normalizes a selectivity for each column.
8. The system of claim 7 , wherein normalization of said selectivity for each column includes application of a weight factor to said selectivity based upon a relative size of a table in which said column resides.
9. The system of claim 6 , wherein said result manager multiplies said calculated cumulative selectivity by an input size of said Group-By operation.
10. The system of claim 6 , wherein the selectivity manager includes determining equivalent columns in the Group-By list based upon an equal join predicate.
11. An article comprising:
a computer-readable non-transitory medium including computer program instructions configured to estimate a result size of a Group-By operation in a database, said instructions comprising:
instructions to organize a database stored on a computer readable medium with data records maintained in multiple tables;
instructions to calculate a cumulative selectivity for a Group-By operation based upon aggregation of individual normalized selectivity of each column in a group of tables in the Group-By operation, including adding to a Group-By list columns equivalent to columns specified in the Group-By operation, said equivalent columns unspecified in the Group-By operation and accounting for dependency of columns originating from a same table in said Group-By operation by subtracting a product of individual normalized selectivities for said columns originating from the same table from a sum of normalized selectivities for said columns; and
instructions to compute a memory requirement for said Group-By operation based on said calculated cumulative selectivity; and instructions to allocate memory for said Group-By operation based upon said memory requirement.
12. The article of claim 11 , further comprising instructions to normalize a selectivity for each column.
13. The article of claim 12 , wherein the instructions to normalize said selectivity for each column includes applying a weight factor to said selectivity based upon a relative size of a table in which said column resides.
14. The article of claim 11 , further comprising instructions to multiply said calculated cumulative selectivity by an input size of said Group-By operation.
15. The article of claim 11 , wherein the instructions to calculate the cumulative selectivity includes determining equivalent columns in the Group-By list based upon an equal join predicate.