US 7124146 B2 Abstract A technique is provided for incrementally maintaining column cardinality estimates in database management systems. The system catalog table containing a cardinality estimate for a column is extended to include an appropriate data structure. A modified linear counting technique is used in a first embodiment of a method for column cardinality estimation. Moreover, a modified logarithmic counting technique is used in a second, preferred embodiment of a column cardinality estimation method to reduce storage requirements for the data structure. The cardinality estimate is produced by an initial scan of the data but is then further maintained without requiring a full scan of the data. Data changes are reflected incrementally in modifications to the initial cardinality estimate, keeping the cardinality statistics more current with respect to the database condition. The technique of the invention typically provides a capability for a database management system to produce more efficient search plans providing more effective responses to user queries through the use of improved cardinality statistics.
Claims(58) 1. A method performed on a computer system for estimating a cardinality value for a set of data values, the method comprising:
(a) initializing a data structure for representing counts;
(b) obtaining a data value from said set of data values;
(c) transforming said data value into a transformed string;
(d) modifying said data structure with said transformed string;
(e) obtaining a summary statistic value from said modified data structure; and
(f) generating said estimated cardinality value using said summary statistic value, wherein generating said estimated cardinality value step (f) is performed for each distinct occurrence of a hashing function within a plurality of hashing functions, further comprising:
(f1) generating a cardinality estimate value associated with each instance of said hashing function;
(f2) summing said cardinality estimate values;
(f3) averaging said cardinality estimate values to produce a raw cardinality estimate value, and;
(f4) adjusting said raw cardinality estimate value for data value collisions to produce a final cardinality estimate value.
2. The method of
3. The method of
(c1) hashing said data value into a hashed data value of said transformed string.
4. The method of
(c1) hashing said data value into a hashed data value, and;
(c2) retaining a leftmost “1” value, all other bit values in said hashed data value being set to zero.
5. The method of
(d1) vector addition of said transformed string to said array of counts when a data value is added to said set of data values; and
(d2) vector subtraction of said transformed string from said array of counts when a data value is deleted from said set of data values.
6. The method of
(e1) determining a relative position number of a leftmost “0” within said array of counts; and
(e2) setting said relative position number to said summary statistic.
7. The method of
(e1) determining a number of non-zero positions in said array of counts; and
(e2) setting said number to said summary statistic.
8. The method of
(f1a) computing a result of 2n/q, wherein n is equal to said summary statistic value and q is less than one.
9. The method of
10. The method of
11. The method of
12. The method of
13. The method of
14. The method of
15. A computer system for estimating a cardinality value for a set of data values comprising:
means for initializing a data structure for representing counts;
means for obtaining a data value from said set of data values;
means for transforming said data value into a transformed string;
means for modifying said data structure with said transformed string;
means for obtaining a summary statistic value from said modified data structure; and
means for generating said estimated cardinality value using said summary statistic value, wherein means for generating said estimated cardinality value step is performed for each distinct occurrence of a hashing function within a plurality of hashing functions, further comprising:
means for generating a cardinality estimate value associated with each instance of said hashing function;
means for summing said cardinality estimate values;
means for averaging said cardinality estimate values to produce a raw cardinality estimate value; and
means for adjusting said raw cardinality estimate value for data value collisions to produce a final cardinality estimate value.
16. The computer system of
17. The computer system of
a plurality of elements, each clement relating to a data value whose leftmost “1” occurs at a specified element position.
18. The computer system of
a plurality of elements, each element relating to a number of occurrences of a distinct data value.
19. The computer system of
means for hashing said data value into a hashed data value of said transformed string.
20. The computer system of
means for hashing said data value into a hashed data value, and;
means for retaining a leftmost “1” value, all other bit values in said hashed data value being set to zero.
21. The computer system of
means for vector addition of transformed bit string and said bit array when a data value is added to said set of data values; and
means for vector subtraction of said transformed bit string from said bit array when a data value is deleted from said set of data values.
22. The computer system of
means for determining a relative position number of a leftmost “0” within said array of counts; and
means for setting said relative position number to said summary statistic.
23. The computer system of
means for determining a number of non-zero positions in said array of counts; and
means for setting said number to said summary statistic.
24. The computer system of
25. The computer system of
26. The computer system of
27. The computer system of
28. The computer system of
29. An article comprising:
a computer readable signal bearing medium;
means stored in the medium for initializing a data structure for representing counts;
means stored in the medium for obtaining a data value from a set of data values;
means stored in the medium for transforming said data value into a transformed string;
means stored in the medium for modifying said data structure with said transformed string;
means stored in the medium for obtaining a summary statistic value from said modified data structure; and
means stored in the medium for generating an estimated cardinality value using said summary statistic value, wherein means stored in the medium for generating said estimated cardinality value step is performed for each distinct occurrence of a hashing function within a plurality of hashing functions, further comprising:
means stored in the medium for generating a cardinality estimate value associated with each instance of said hashing function;
means stored in the medium for summing said cardinality estimate values;
means stored in the medium for averaging said cardinality estimate values to produce a raw cardinality estimate value; and
means stored in the medium for adjusting said raw cardinality estimate value for data value collisions to produce a final cardinality estimate value.
30. The article of
31. The article of
means stored in the medium for hashing said data value into a hashed data value of said transformed string.
32. The article of
means stored in the medium for hashing said data value into a hashed data value, and;
means stored in the medium for retaining a leftmost “1” value, all other bit values in said hashed data value being set to zero.
33. The article of
means stored in the medium for vector addition of transformed bit string and said bit array when a data value is added to said set of data values; and
means stored in the medium for vector subtraction of said transformed bit string from said bit array when a data value is deleted from said set of data values.
34. The article of
means stored in the medium for determining a relative position number of a leftmost “0” within said array of counts; and
means stored in the medium for setting said relative position number to said summary statistic.
35. The article of
means stored in the medium for determining a number of non-zero positions in said array of counts; and
means stored in the medium for setting said number to said summary statistic.
36. The article of
means in the medium for selecting said set of data values from at least one of: a database and a flat file.
37. The article of
38. The article of
39. The article of
40. The article of
41. The article of
means for relating each element of a plurality of elements of said array to a data value whose leftmost “1” occurs at a specified element position.
42. The article of
means for relating each element of a plurality of elements of said array to a number of occurrences of a distinct data value.
43. The article of
magnetic, optical, biological and atomic storage media.
44. The article of
an Intranet, an Extranet and the Internet.
45. A computer program product comprising a computer readable medium carrying program means for estimating a cardinality value for a set of data values on a computer system, the computer program product comprising:
means for initializing a data structure for representing counts;
means for obtaining a data value from said set of data values;
means for transforming said data value into a transformed string;
means for modifying said data structure with said transformed string;
means for obtaining a summary statistic value from said modified data structure; and
means for generating said estimated cardinality value using said summary statistic value, wherein means for generating said estimated cardinality value step is performed for each distinct occurrence of a hashing function within a plurality of hashing functions, further comprising:
means for generating a cardinality estimate value associated with each instance of said hashing function;
means for summing said cardinality estimate values;
means for averaging said cardinality estimate values to produce a raw cardinality estimate value; and
means for adjusting said raw cardinality estimate value for data value collisions to produce a final cardinality estimate value.
46. The computer program product of
47. The computer program product of
means for hashing said data value into a hashed data value of said transformed string.
48. The computer program product of
means for hashing said data value into a hashed data value, and;
means for retaining a leftmost “1” value, all other bit values in said hashed data value being set to zero.
49. The computer program product of
means for vector addition of transformed bit string and said bit array when a data value is added to said set of data values; and
means for vector subtraction of said transformed bit string from said bit array when a data value is deleted from said set of data values.
50. The computer program product of
means for determining a relative position number of a leftmost “0” within said array of counts; and
means for setting said relative position number to said summary statistic.
51. The computer program product of
means for determining a number of non-zero positions in said array of counts; and
means for setting said number to said summary statistic.
52. The computer program product of
53. The computer program product of
54. The computer program product of
a plurality of elements, each element relating to a number of occurrences of a distinct data value.
55. The computer program product of
56. The computer program product of
a plurality of elements, each element relating to a data value whose leftmost “1” occurs at a specified element position.
57. The computer program product of
58. The computer program product of
Description This invention generally relates to determining frequent data values within a set of data values and more particularly to incrementally maintaining statistics such as, column cardinality, in relational database systems. To ensure generation of an efficient query plan, a database management system typically includes a query optimization software module. The query optimization software module generates search plans for query requests based on optimization rules that consider, among many variables, the size of the response set (amount of data expected to be returned) and the number of unique values within the data being queried. Current, accurate database statistics are highly desired by performance sensitive query software modules (for example, a query optimizer) of database management systems. When database statistics are inaccurate or no longer current, the query optimizer is most likely to generate inefficient query plans. Inefficient query plans perform poorly at run time degrading overall performance of the database management system. In the following simple examples the actual cardinality value and the estimated cardinality value are equivalent, which may not be the typical case. Column cardinality is an example of an important database statistic. Column cardinality is a value representing the number of distinct values found in a database column. For example, the column cardinality of the NAME column in Table Typical database systems estimate column cardinality because exact computation is too expensive in terms of time and resource and due to the dynamic nature of the value. In many commercial database systems, statistics collection is a batch operation, allowing the statistics processing workload to be scheduled based on database performance needs. Typically, database statistics represent a snapshot of the data only for the instant the statistics are generated. As a database table changes, previously computed statistics, associated with that table, may no longer be representative of the current state of the table and its indexes. Statistics for a table should be recalculated to reflect the current state of the table. It is typically a simple task to incrementally maintain basic statistics such as the total number of rows in a table. However, column cardinality estimates are usually more difficult to maintain. Current techniques for maintaining estimates of column cardinality require rescanning the entire column as the data in that column changes. For example, assume a column “C” initially contains twenty distinct values and subsequently thirty additional distinct values are inserted. Computing a new estimate of column cardinality for column “C” by simply adding the cardinality of the two data sets may lead to an incorrect result due to duplicate entries. An allowance has to be made for duplicate values within the combined set of old and new values. For example, consider Table The column NAME has the following four values: “smith”, “jones”, “doole”, and “smith”. Observe that the value “smith” is repeated twice, therefore, the estimated column cardinality value of column NAME is equal to “3”. This is because there are only three distinct values namely, “smith”, “jones”, and “doole”. On the other hand the estimated column cardinality value of the column SALARY is equal to “4” because all values in this column are distinct. A typical database system has at least one method of computing column cardinality estimates. Typical examples of such methods are Linear counting and Logarithmic counting, both of which are well known to persons of ordinary skill in the art. When a database system computes an estimate of column cardinality, the database system stores the estimated value in what is known as a system catalog table for future use by the database system. For example, in a typical database system, the system catalog table that stores the column cardinality estimates for data in Table Table In a typical database system, a query optimizer (a component of the database system) then obtains the values of the column cardinality estimates from a database system catalog table. The query optimizer then uses the column cardinality values along with other statistics when computing the resource consumption estimates for a given set of database queries. Consider once again the table Employee shown in Table Computing a new column cardinality estimate value for the column NAME for example, according to current methods applied by database systems, requires reading all the NAME values; smith, jones, doole, smith, and harris. After computing the cardinality estimates for both columns of the recently updated table in Table The content of the database system catalog table in Table In a “linear counting” technique used to estimate column cardinality, a hashing function is used to transform each data value to a bit string that represents a position in a bit map. Initially all positions in the bit map contain zeros. Hashing a data value to a position in the bit map causes a value at the position to be set to one. A value of zero in a position of the bit map indicates there were no data values that hashed to that position. A value of one in a position in the bit map indicates that at least one data value hashed to that position. All data values in the column are hashed, and then the number of distinct data values is estimated by counting the number of bits in the bit map that are set to one (non-zero). Not all data values that hash to the same address are necessarily duplicates. Distinct values that hash to the same location are collisions. Collisions require the count to be adjusted using an adjustment formula, derived using probabilistic techniques (known to those of ordinary skill in the art). Another well known estimation technique is “logarithmic counting.” Although more complicated than linear counting, the bit map used by logarithmic counting is much smaller than that used by linear counting. In logarithmic counting, a hashing function is used to transform each data value to a bit string, which is then further transformed by retaining the leftmost “1” position and converting all remaining positions to “0”. All data values in the column are doubly transformed to bit strings in this manner, and a final bit map is computed as the bit-wise “OR” of all the bit strings for the column. The leftmost “0” position in the final bit map is then computed, and this relative position number n is used to calculate the estimated cardinality value using the formula 2n/q, where “q” is set to a value “0.7735”. This latter expression is a well known probabilistic estimation formula that ensures statistically reasonable results. As can be seen from the foregoing discussion, current cardinality estimation techniques typically require all data values in a column to be obtained each time a new estimate of that column's cardinality is requested. The detailed example shown previously illustrates a case where a new value was added, but the same requirement also holds for cases where an existing value is deleted or updated. The presence of duplicate entries in columns of data make it difficult to maintain the column cardinality estimates each time data values change. It is apparent, from the examples described, that there is a need for improved efficiency, or expediency or both regarding generation of database management systems statistics. Embodiments of the current invention are described by way of example, in the accompanying drawings in which: The present invention provides a technique for incrementally updating or maintaining column cardinality estimate values in database management systems. The invention extends the system catalog table storing a column cardinality estimate to also include an appropriate data structure associated with the column for which cardinality is being maintained. The data structure consists of an array of counts, where the interpretation of the counts depends on the particular embodiment. The invention uses the information in the extended data structure in conjunction with a modified counting technique and is suitable for use in database management systems requiring current column statistics. In a first aspect of the invention there is provided a method performed on a computer system for estimating a cardinality value for a set of data values comprising, initializing a data structure for representing counts, obtaining a data value from the set of data values and transforming the data value into a transformed string. Next modifying the data structure with the transformed string and obtaining a summary statistic value from said modified data structure. Finally generating an estimated cardinality value using the summary statistic value. In a another aspect of the invention there is provided a computer system for estimating a cardinality value for a set of data values comprising, means for initializing a data structure for representing counts, means for obtaining a data value from the set of data values and means for transforming the data value into a transformed string. In addition, providing means for modifying data structure with the transformed string, and means for obtaining a summary statistic value from the modified data structure. Additionally, providing means for generating an estimated cardinality value using the summary statistic value. In a further aspect of the invention there is provided an article comprising, a computer readable signal bearing medium with means stored in the medium for initializing a data structure for representing counts, means stored in the medium for obtaining a data value from a set of data values, and means stored in the medium for transforming the data value into a transformed string. The article further provides means stored in the medium for modifying the data structure with the transformed string, and means stored in the medium for obtaining a summary statistic value from the modified data structure. In addition, the article provides means stored in the medium for generating an estimated cardinality value using the summary statistic value. Other features and advantages of the current invention should be apparent from the following description of embodiments, which illustrates, by way of example, the principles of the invention. In the performance of database query processing, knowledge of column cardinalities (the number of distinct values in a column) is important for the generation of efficient query plans. The query plans used in query operations directly affects the performance of the relational database management system. The current invention typically enables a database management system to more efficiently compute, when required, a column cardinality estimate without requiring use of all of the column data values. Computation may occur whenever a new data value is inserted into a column or an existing data value is deleted from, or updated in, a column. The invention determines the new estimated column cardinality value using the new data value inserted, deleted or updated. This method of estimation is referred to as “incremental column cardinality maintenance”. The invention incorporates, in a first embodiment, a modified linear counting technique into computing a column cardinality estimate. The invention incorporates, in a second, preferred embodiment, a modified logarithmic counting technique. This technique may be used when computing a column cardinality estimate, to reduce storage requirements of the system catalog table data structure. Referring to Data processing system Relational database management system Data items After performing any one of the operations Referring to At an instant in time, the mth entry of the array of counts records the number of data values in a column that have hashed to position m. Column cardinality is then estimated as the number of non-zero entries in the array of counts adjusted for collisions using the same adjustment formula as the simple linear counting mechanism. The number of non-zero entries may be referred to as a summary statistic derived from the array of counts. The unadjusted count may be referred to as the “raw” column cardinality estimate while the adjusted count is referred to as the “final” column cardinality estimate. Handling of INSERT, DELETE, and UPDATE database operations using modified linear counting is described below. In a database INSERT operation, a new value In a database DELETE operation, a data value to be deleted from the set of data values A database UPDATE operation can be thought of as deleting the old value followed by inserting the new value. The operations in the delete and insert steps can then be applied sequentially as explained above. For example, consider Table As in the standard linear counting technique, the column cardinality estimate of column NAME, in Table Now assume a previous employee (for example, an entry in column An UPDATE operation can be thought of as a DELETE of the old value followed by an INSERT of the new value. This embodiment of the invention computes a new column cardinality estimate value after an update operation, by sequentially performing the logic for a delete of the old value followed by an insert of the new value. An example of an update is not shown as this is easily understood from the previous insert and delete examples. Referring to For example, assume “C” (data value Operation Operation It should be reemphasized that although not shown in the diagram one need only compute a new estimate for the column cardinality if the position n of the leftmost “0” in the array of counts has changed after the database operation of INSERT, DELETE or UPDATE. If the value for n has not changed, the previously computed estimate is retained and used as the estimated cardinality value, saving time and resource. As with the extended linear counting technique previously described, the preceding embodiment of the invention extends the logarithmic counting method by replacing the bit map with an array of counts. The array of counts for extended logarithmic counting requires fewer elements than the array required for the extended linear counting method. Because the modified logarithmic counting method reduces the catalog storage requirements, it may be a preferred embodiment. The technique may be better understood using the following examples. First, the array of counts and consequent column-cardinality estimate are computed for the NAME column in the original EMPLOYEE table in Table In Table Using the array of counts (3,1,0) obtained, the column cardinality is then estimated using 2n/q, (“2” raised to power “n”) where n is the position of the leftmost zero in the array of counts and “q” is a value of 0.7735. In this example, n=2, because counting from the leftmost end, or zero position of the array, it is the second position that has the value of zero (0 position=3, 1st position=1, 2nd position=0). The estimated column cardinality is then 22/0.7735=5.2 The process described above may be repeated with “y” different hash functions (typically, y=64) to obtain “y” preliminary estimates, and a final estimate may then be computed by averaging the preliminary estimates. There may then be “y” arrays of counts maintained in the system catalog. It is well known that this technique may be implemented without actually needing y completely different hash functions. For purposes of this example, assume for simplicity that only one hash function is used. Equivalently, this example can be viewed as showing the computations necessary to compute and update a specified preliminary estimate. Continuing the example the last row from the original Table By further example, again using the original table in Table Similar computations are used when adding a new row to the EMPLOYEE table of Table It is conceivable that one may implement this invention as described in a database management system so that the statistics may be more current with respect to the database activities (that is INSERT, UPDATE and DELETE operations). To reduce the overhead associated with incrementally updating the statistics, a preferred approach may include buffering of INSERT, UPDATE and DELETE operations and then at a later stage generating the incremental statistics based on the buffered values. The invention may be then advantageously used because it does not need to implement a special buffering mechanism to allow incremental statistics update to proceed at a later stage. Buffering may be provided by the logged activity and almost every database system implements logging in some form to allow for recovery in the case where a database has been damaged. Database systems usually store transaction activities into a log file. The technique of this invention can be used in conjunction with the log file information to update the database cardinality statistics in an incremental fashion. When implemented in an automatic manner, the invention may provide current cardinality estimate values at a much lower cost than automating batch-like alternatives since batch versions currently used typically require re-scanning the entire table (or a sample of the table). The invention provides incremental statistics updates while not having to rescan the entire table. The statistics may be incrementally updated during database activities if desired or may be updated at a later stage using the log file approach as described earlier. In both cases re-accessing the base table is not required. Further, a capability of calculating cardinality statistics only when needed may reduce processing requirements. The concepts of the current invention can be further extended to a variety of applications that are clearly within the scope of this invention. Having thus described the current invention with respect to a preferred embodiment as implemented, it will be apparent to those skilled in the art that many modifications and enhancements to the current invention are possible without departing from the basic concepts as described in the preferred embodiment of the current invention. Therefore, what is intended to be protected by way of letters patent should be limited only by the scope of the following claims. Patent Citations
Non-Patent Citations
Referenced by
Classifications
Legal Events
Rotate |