System for generating predicate-weighted histograms
Embodiments of the present invention provide a method, computer program-product, and system for generating predicate-weighted histograms in a database management system. Further, the methods, computer program-products and systems in accordance with the present invention generate histograms that are biased towards the predicate literals of the queries that are submitted to the database management system. The resulting histograms will improve query performance by generating histograms with greater resolution near predicate literals that represent the queries submitted to the database management system.
1. A method for generating a histogram, the method comprising the steps of:
(a) initialize a working histogram for a data column, the data column comprising a plurality of data values, wherein the working histogram comprises a plurality of rows, each row corresponding to a unique data value from the plurality of data values;
(b) capture one or more queries, wherein the one or more queries comprise one or more predicate literals;
(c) generate a weight vector based on the predicate literals, wherein the weight vector comprises a plurality of weight values, each weight value corresponding to one of the plurality of rows of the working histogram;
(d) calculate a cost value for each row of the working histogram, wherein each cost value is determined at least in part on an information loss and the weight value corresponding to one of the rows of the working histogram;
(e) identify a first row in the working histogram having a lowest cost value of the plurality of rows in the working histogram; and
(f) merge the first row of the working histogram with a second row of the working histogram,
wherein the information loss is calculated as an entropy value of a merged row in the working histogram less the sum of the entropy of a first row in the working histogram and a second row in the working histogram, wherein the merged row comprises the first row and the second row.
2. The method of claim 1 , further comprising the steps of:
calculate a total number of rows in the working histogram;
determine that the number of rows in the working histogram is greater than a predetermined threshold number of rows; and
repeat steps (c) through (f) until the total number of rows in the working histogram is less than the predetermined threshold number of rows.
3. The method of claim 1 further comprising:
compare a distribution of weight values to a distribution of the rows in the working histogram; and
determine that distribution of weight values is sufficiently different to the distribution of the rows in the working histogram.
4. The method of claim 3 , wherein comparing the distribution of weight values to the distribution of the rows in the working histogram further comprises:
performing statistical non-parametric test to generate a comparison score; and
determining that the comparison score is less than a predetermined threshold comparison score.
5. The method of claim 1 , wherein capturing one or more queries comprises capturing one or more queries that meet or exceed a predetermined threshold.
6. The method of claim 5 , wherein the predetermined threshold is a runtime for the one or more queries.
7. The method of claim 5 , wherein the predetermined threshold is a CPU time of a query.
8. The method of claim 1 , wherein generating a weight vector further comprises:
generating a kernel density function for the one or more predicate literals; and
evaluating the kernel density function at each row in the working histogram.
9. The method of claim 1 , where entropy is calculated using Shannon's entropy formula.
10. The method of claim 1 , wherein the method further comprises:
calculating one or more of a range penalty, an information loss, a legacy information loss, and a scaling parameter.
11. The method of claim 10 , wherein the cost function is further based on the one or more of a size penalty, an information loss, a legacy information loss, and a scaling parameter.
12. The method of claim 1 , wherein the first row of the working histogram and the second row of the working histogram are adjacent rows.
13. The method of claim 1 , wherein the data column is indexed.
14. The method of claim 13 , wherein the indexed data column is filtered.
15. A method for generating a histogram, the method comprising the steps of:
(a) initialize a working histogram for a data column, the data column comprising a plurality of data values, wherein the working histogram comprises a plurality of rows, each row corresponding to a unique data value from the plurality of data values;
(b) capture one or more queries, wherein the one or more queries comprise one or more predicate literals;
(c) generate a weight vector based on the predicate literals, wherein the weight vector comprises a plurality of weight values, each weight value corresponding to one of the plurality of rows of the working histogram;
(d) calculate a cost value for each row of the working histogram, wherein each cost value is determined at least in part on an information loss and the weight value corresponding to one of the rows of the working histogram;
(e) identify a first row in the working histogram having a lowest cost value of the plurality of rows in the working histogram; and
(f) merge the first row of the working histogram with a second row of the working histogram,
wherein generating the weight vector further comprises:
generating a kernel density function for the one or more predicate literals; and
evaluating the kernel density function at each row in the working histogram, and
wherein the bandwidth for the kernel density function is proportional to the variance in the one or more predicate literals.
16. A computer-program product for generating a histogram, wherein the computer program product is embodied on at least one non-transitory computer-readable medium having computer-readable program code portions embodied therein, the computer-readable program code portions comprising executable portions configured to:
(a) initialize a working histogram for a data column, the data column comprising a plurality of data values, wherein the working histogram comprises a plurality of rows, each row corresponding to a unique data value from the plurality of data values;
(b) capture one or more queries, wherein the one or more queries comprise one or more predicate literals;
(c) generate a weight vector based on the predicate literals, wherein the weight vector comprises a plurality of weight values, each weight value corresponding to one of the plurality of rows of the working histogram;
(d) calculate a cost value for each row of the working histogram, wherein each cost value is determined at least in part on an information loss and the weight value corresponding to one of the rows of the working histogram;
(e) identify a first row in the working histogram having a lowest cost value of the plurality of rows in the working histogram; and
(f) merge the first row of the working histogram with a second row of the working histogram,
wherein the information loss is calculated as an entropy value of a merged row in the working histogram less the sum of the entropy of a first row in the working histogram and a second row in the working histogram, wherein the merged row comprises the first row and the second row.
17. The computer program product of claim 16 , further comprising executable portions configured to:
calculate a total number of rows in the working histogram;
determine that the number of rows in the working histogram is greater than a predetermined threshold number of rows; and
repeat steps (c) through (f) until the total number of rows in the working histogram is less than the predetermined threshold number of rows.
18. A system for generating histograms, the system comprising:
a memory device with computer-readable program code stored thereon;
a communication device;
a processing device operatively coupled to the memory device and the communication device, wherein the processing device is configured to execute the computer-readable program code to:
(a) initialize a working histogram for a data column, the data column comprising a plurality of data values, wherein the working histogram comprises a plurality of rows, each row corresponding to a unique data value from the plurality of data values;
(b) capture one or more queries, wherein the one or more queries comprise one or more predicate literals;
(c) generate a weight vector based on the predicate literals, wherein the weight vector comprises a plurality of weight values, each weight value corresponding to one of the plurality of rows of the working histogram;
(d) calculate a cost value for each row of the working histogram, wherein each cost value is determined at least in part on an information loss and the weight value corresponding to one of the rows of the working histogram;
(e) identify a first row in the working histogram having a lowest cost value of the plurality of rows in the working histogram; and
merge the first row of the working histogram with a second row of the working histogram,
wherein the information loss is calculated as an entropy value of a merged row in the working histogram less the sum of the entropy of a first row in the working histogram and a second row in the working histogram, wherein the merged row comprises the first row and the second row.
19. The system of claim 18 where in the processing device is further configured to execute the computer-readable program code to
calculate a total number of rows in the working histogram;
determine that the number of rows in the working histogram is greater than a predetermined threshold number of rows; and
repeat steps (c) through (f) until the total number of rows in the working histogram is less than the predetermined threshold number of rows.