Some embodiments relate to executing and optimizing queries executed by database management systems. In particular, some embodiments concern methods and systems for optimizing the queries by providing cardinality estimations with guaranteed bounded error in a database management system in an efficient and effective manner. In some aspects, the data structures used to derive the estimates exploit discrete dense domains for faster histogram construction.
The following description is provided to enable any person in the art to make and use the described embodiments and sets forth the best mode contemplated for carrying out some embodiments. Various modifications, however, will remain readily apparent to those in the art.
The present disclosure describes methods and systems that, at least in part, use a novel space- and time-efficient algorithm for the construction of maximal q,θ-optimal histogram buckets.
In some aspects, histograms may be used for cardinality estimation problems in, for example, database systems. A q,θ-optimal histogram is a histogram over a data distribution whose buckets have been carefully chosen such that for any cardinality estimate made using the histogram, the estimate is guaranteed to obey an acceptability criteria parameterized by q and θ that bounds the ratio error between the estimate and the true answer.
Histograms are widely used for cardinality estimation problems in, for example, database systems.
A problem of constructing q,θ-optimal histograms may be reduced to the computationally simpler problem of finding a maximal q,θ-optimal histogram bucket from a fixed starting value. That is, a histogram bucket that guarantees for every possible query over the values within the bucket that the resulting estimate is q,θ-acceptable, and which cannot be extended by another value without violating this property.
As a preliminary discussion and introduction to the various methods and systems disclosed herein, a number of concepts will now be discussed. A data distribution D of size n over an ordered numeric value domain is a binary tuple (active, ƒ) where
In some embodiments herein, we consider only data distributions whose active value domain is composed of a set of contiguous integers starting at zero (i.e., having vi=i). This limited scope permits certain simplifications to both the presentation and to the logic of the algorithms disclosed herein. However, this is not a fundamental limitation of the present disclosure and it is possible to generalize the methods, systems, and algorithms herein to handle data distributions whose active domains are composed of arbitrary sets of numeric values.
Given a data distribution D of size n and any pair of values vl, vr, ε with vl<vr herein [vl,vr) is used to denote the left-closed right-open interval containing values v ε
satisfying vl≦v<vr.
The cardinality of a range query over interval [vl,vr) is given by cumulative frequency function ƒ+.
The result of a count distinct query over interval [vl,vr) is given by the count function ƒ1.
The average cardinality per unit in an interval is given by the density function ƒd, while the average cardinality per active domain value is given by the average function ƒavg.
Finally, the cardinality of an equality query for some value vεV is simply the frequency ƒ(v).
A histogram bucket B over data distribution D is a data structure that has an associated interval of starting and ending boundary values [vs,ve) and which permits exact evaluation of the following queries.
We define the size of bucket B as the width of its boundary interval.
|B|:=|[vs,ve)|=ve−vs
A histogram over D is a set of histogram buckets corresponding to pair-wise disjoint intervals of boundary values. Because a histogram and its buckets are always defined for a single data distribution D, for the remainder of the present disclosure reference to a bucket B assumes the data distribution D.
Given a histogram bucket B with boundary values [vs,ve) and a query interval [vl,vr) satisfying vs≦vl<vr≦ve, the following approximation functions {circumflex over (ƒ)}+, {circumflex over (ƒ)}1, {circumflex over (ƒ)}d and {circumflex over (ƒ)}avg embody well-known techniques for estimating the functions {circumflex over (ƒ)}+, {circumflex over (ƒ)}1, {circumflex over (ƒ)}d and {circumflex over (ƒ)}avg, respectively, using histogram bucket B.
Similarly, given any value vε satisfying vs≦v<ve, the following approximation function embodies a known technique for estimating the cardinality of an equality query using histogram bucket B.
Herein, we restrict our attention to queries that fall within a single histogram bucket. Known techniques exist for reducing queries that span multiple histogram buckets into collections of smaller queries that do not span buckets.
Within the limited scope we are considering of distributions over dense integer domains, the estimation functions {circumflex over (ƒ)}, {circumflex over (ƒ)}1, and {circumflex over (ƒ)}avg can all be re-written as follows.
Therefore, in order to support estimation functions {circumflex over (ƒ)}+, {circumflex over (ƒ)}1, {circumflex over (ƒ)}d and ƒavg and {circumflex over (ƒ)} it suffices for a histogram bucket to only directly support estimation function {circumflex over (ƒ)}+, which requires that each bucket B store enough information to be able to retrieve its value boundaries vs and ve and its cumulative frequency ƒ+(B).
Definition 1.1 (q-Acceptable and q,θ-Acceptable Estimates)
Given a histogram bucket B and a query interval [vl,vr) that falls within B:
1. The estimate {circumflex over (ƒ)}+(vl,vr) is q-acceptable if
2. The estimate {circumflex over (ƒ)}+(vl,vr) is q,θ-acceptable if either
Herein, we call the estimate {circumflex over (ƒ)}+(vl,vr) a q-violation if it is not q-acceptable, and a q,θ-violation if it is not q,θ-acceptable. When the bucket B is clear from context, we may refer to the interval [vl,vr) itself as the q- or q,θ-violation. Although not needed for the limited scope of distributions over dense integer domains, the concepts of q-acceptable and q,θ-acceptable estimates (and violations) extend directly to both count distinct queries and equality queries.
Definition 1.2 (q,θ-Acceptable Bucket)
Given histogram bucket B over boundary interval [vs,ve), herein we call B q,θ-acceptable if every query interval [vl,vr) that falls within B has a q,θ-acceptable estimate {circumflex over (ƒ)}(vl, vr).
Given a q,θ-acceptable histogram bucket B over interval [vs,ve), we state that B is locally maximal if no q,θ-acceptable bucket exists for the boundary interval [vs,ve+1), and we further state that B is globally maximal if no q,θ-acceptable bucket exists for any boundary interval [vs, ve=k) with k>0. As described in previous literature, the q-error produced by estimation functions over a histogram bucket increases almost monotonically with the size of the bucket, which implies that with rare exception a locally maximal q,θ-acceptable bucket is almost always globally maximal. This property suggests an iterative method for constructing q,θ-optimal histograms starting with the smallest value in the data distribution and sequentially construct locally maximal q,θ-acceptable histogram buckets, with the ending value boundary for the bucket constructed in one iteration forming the starting value boundary for the bucket constructed in the next iteration [2, 1].
In some embodiments, we do not propose any changes to this overarching algorithm for q,θ-optimal histogram construction. Rather, the present disclosure focuses on the sub-problem of how to efficiently construct a locally-maximal q,θ-acceptable histogram bucket.
Algorithm 1 disclosed below illustrates a method for constructing a q,θ-acceptable histogram bucket from a given starting value using a simple generate-and-test methodology.
Input: Ratio error q
Input: Threshold θ
Input: Data distribution D = (
active, ƒ)
Input: Starting value vs ε
active
active with vs < ve
, D, B)
Input: Ratio error q
Input: Threshold θ
Input: Data distribution D = (
active, ƒ)
Input: Histogram bucket B over [vs,ve)
Referring to algorithm 1, the outer method B
Although Algorithm 1 may appear conceptually simple, its overall structure and asymptotic performance corresponds to the best bucket construction algorithms proposed in previous literature [1], with the following two caveats.
As observed above, each call to I
As introduced and discussed hereinabove, decision procedure I
Given histogram bucket B over interval [vs,ve), let interval [vl,vr) be a q-violation proving that B is not q-acceptable. We refer to [vl,vr) herein as a minimal q-violation if there does not exist another q-violation [vl′,vr′) that is a strict sub-interval of [vl,vr). A minimal q,θ-violation may be analogously defined.
Theorem 3.1
Let [vl,vr) be a q,θ-violation, and let vm be any value satisfying vl<vm<vr. If
1. ƒ+(vl, vm)>θ or {circumflex over (ƒ)}+(vl, vm)>θ; and
2. ƒ+(vm,vr)>θ or {circumflex over (ƒ)}+(vm, vr)>θ
then q,θ-violation [vl,vr) is not minimal.
Corollary 3.2
If interval [vl,vr) is a minimal q,θ-violation, then its maximum size is bounded as follows.
Corollary 3.2 bounds the maximum size query interval that needs to be considered in order to conclude that no q,θ-violations exist. By integrating this bound into decision procedure I
Input: Ratio error q
Input: Threshold θ
Input: Data distribution D = (
active, f)
Input: Histogram bucket B over [vs,ve)
The new decision procedure has worst-case asymptotic runtime bound of
Note that it can be assumed that
or else me decision problem is trivial. Comparing
to the O(|B|2) bound of the previous decision procedure, it is seen that this is a substantial improvement because the new procedure scales linearly with the number of distinct values in B, since for moderately large B the value ƒd(B) is basically unchanging as B increases, and θ remains constant (albeit a potentially large θ).
As discussed above, the worst-case runtime of I
In some embodiments, a strategy for partially mitigating the effect of large e values is suggested by the following axiom and its corollary, which follow trivially from the definition of q,θ-acceptability.
Axiom 3.3
If bucket B contains a q,θ-violation, then B also contains a q,θ′-violation for all θ′<θ
Corollary 3.4
q,θ′-acceptability implies q,θ-acceptability for all θ′<θ
As an example, consider an invocation of B
The above example suggests that an iterative approach to testing q,θ-acceptability may yield performance improvements for large θ. However, rather than repeating entire invocations of the decision procedure, it may be more efficient to iteratively increase the value of θ within a single invocation of the decision procedure.
Definition 3.5 (Effective Theta)
For a given invocation of a q,θ-acceptability test of some bucket B, an effective theta, θeff, is defined as the smallest value such that the result of the q, θeff-acceptability test implies the result of the q,θ-acceptability test. If B is q,θ-acceptable then θeff,≦θ; otherwise θeff=θ.
Input: Ratio error q
Input: Threshold θ
Input: Data distribution D = (
active, f)
Input: Histogram bucket B over [vs,ve)
Decision procedure I
The asymptotic runtime of procedure I
The following theorem and corollaries enable further improvements to Algorithm 3 that exploit knowledge of recently-enumerated intervals to reduce the search space in certain cases.
Theorem 3.6
Let [vl,vr) be a minimal q,θ-violation, and let vm be any value satisfying vl<vm<vr.
1. If [vl,vm) is q-acceptable, then ƒ+(vm, vr)≦θ and {circumflex over (ƒ)}+(vm, vr)≦θ.
2. If [vm,vr) is q-acceptable, then ƒ+(vl, vm)≦θ and {circumflex over (ƒ)}+(vl, vm)≦θ.
Corollary 3.7
Let [vl,vr) be a minimal q,θ-violation, and let vm<vr be the largest value such that [vm, vr) is q-acceptable. Then the maximum size of [vl, vr) is bounded as follows.
Corollary 3.8
Let [vl,vr) be a minimal q,θ-violation. If [vr-1,vr) is q-acceptable, then [vl,vr-1) is a q-violation but not a q,θ-violation.
Corollary 3.7 permits the number of iterations of the loop on line 3 of Algorithm 3 to be reduced in certain cases. For each iteration of the outer loop on line 2, a boolean variable can be used to detect when the first q-acceptable interval [j,i) is enumerated by the loop starting at line 3. This interval corresponds to [vm,vr) in Corollary 3.7, and so the corollary permits computation of a new maximum size for the intervals with right endpoint vr=i that need to be enumerated by the remaining iterations of the loop on line 3.
Corollary 3.8 permits the loop on line 3 of Algorithm 3 to be completely skipped in certain cases. For each iteration of the loop starting on line 2, a boolean variable can be used to record whether any of the intervals enumerated by the loop starting on line 3 are q-violations. Upon the next iteration of the loop at line 2, if the singleton range query [i−1,i) is q-acceptable and the previous iteration did not find any q-violations with a right endpoint of i−1, then the evaluation of the loop on lines 3-7 can be skipped because Corollary 3.8 guarantees there cannot be a minimal q,θ-violation with right endpoint of i.
As discussed above, the bucket construction method in Algorithm 1 has a stratified generate-and-test structure in which procedure B
The present disclosure will now consider inefficiencies that may be present in Algorithm 1 in the stratification between the generation and test layers, and introduces an alternate algorithmic structure that tightly integrates the testing of q,θ-acceptability with an incremental construction of candidate buckets. For clarity of presentation, the disclosure here will ignore the results obtained using Algorithm 3 and use the original Algorithm 1 as the basis for the following illustrative discussion. However, the algorithmic improvements presented herein for eliminating the stratification between generation and testing are orthogonal to the algorithmic improvements related to Algorithms 2 and 3. It is a straightforward matter to combine the results herein with the various results hereinabove to yield a unified construction algorithm that utilizes all of the foregoing techniques.
Consider Algorithm 1, and note that there are three loops in a nested arrangement:
The following observation provides a key insight for eliminating one of these loops.
Observation 4.1
Deciding q,θ-acceptability per Definition 1.1 does not require knowing the interval [vs,ve) of candidate bucket B. Instead, it suffices to know only ƒd(B) in order to compute {circumflex over (ƒ)}+(vl, vr).
By applying Observation 4.1 to the q,θ-acceptability test on line 3 of I
Pseudo-code of the new procedure B
Input: Ratio error q
Input: Threshold θ
Input: Data distribution D = (
active, f)
Input: Starting value vs ∈
active
active | + 1
Variables LB and UB represent the (inclusive) lower and upper bounds that ƒd(B) must stay between to avoid a q,θ-violation by an already-enumerated query interval, and they are updated for each query interval [j,i). Variable LB bounds the lowest permitted value for ƒd(B) that yields a q,θ-acceptable estimate {circumflex over (ƒ)}+(j, i). Because LB is bounding under-estimates, such an estimate can only be a q,θ-violation (c.f. Definition 1.1) if the true cumulative frequency ƒ+(j, i) exceeds θ and ƒd (j, i) exceeds {circumflex over (ƒ)}d(j, i) by more than a factor q. Lines 6-7 increase LB if necessary to within a factor q of ƒd(j, i). Variable UB bounds the highest permitted value for ƒd(B) that yields a q,θ-acceptable estimate {circumflex over (ƒ)}+(j, i). Because UB is bounding over-estimates, such an estimate can only be a q,θ-violation if the estimated cumulative frequency {circumflex over (ƒ)}+(j, i)=(i−j)ƒd(B) exceeds θ and {circumflex over (ƒ)}d(j, i) exceeds ƒd (j, i) by more than a factor q. Line 8 decreases UB if necessary until one of those two conditions is no longer true. Line 9 tests the current bucket [vs,i) against the upper and lower bounds and returns the previous bucket if a q,θ-violation has been found.
The asymptotic complexity Of B
As mentioned above, previous construction algorithms for q,θ-optimal histograms have been extended so that a single construction call builds a fixed number equi-width “bucklets” at the same time, which are stored together as a single bucket within the overall histogram. For simplicity, assume that eight equi-width bucklets are constructed at one time. The eight bucklet frequencies can then be compressed together into a single 64-bit bucket payload using one of a variety of different compression schemes that allocate a certain number of bits (e.g., 16) to compressed storage of the cumulative frequency for the entire bucket, and a certain number of bits (e.g., 6) to compressed storage of the cumulative frequency of each bucklet. Such a bucklet-based storage scheme reduces the number of required histogram buckets by a constant factor, which reduces the number of values that need to be stored to identify value boundaries. (In some aspects, the savings in frequency storage is less pronounced since a relatively large payload such as 64-bits is required, whereas an atomic histogram bucket storing only a frequency count could be reasonably compressed down to 8 bits).
In some regards, a disadvantage of such a scheme is that the reliance on equi-width bucklets to avoid storing intermediate bucklet boundaries limits the maximum width of the eight bucklets to the width of the first bucket to violate q,θ-acceptability.
In some aspects, the incremental construction algorithm described above does not permit separating the generation and testing phases, which may make it less suited to building a group of equi-width bucklets. Whereas a generate-and-test algorithm can iteratively extend and test bucklets as a group, the incremental construction algorithm builds bucklets serially and so cannot know when to stop extending the first bucklet in cases where a q,θ-violation would occur in a later bucklet of a shorter length.
In some embodiments, the incremental construction algorithm herein may be used to construct individual bucklets within a bucket that uses bucklet-based compression. In order to permit the bucklets to be constructed serially the storage structure permits the bucklets to take on different widths rather than making an equi-width assumption. To accomplish this, some embodiments extend any of the schemes for storing bucklet frequencies with additional bits to encode the bucklet boundaries that are interior to the overall histogram bucket interval. For example, for a bucket composed of eight variable-width bucklets, seven interior boundary values may be stored. Assume that 64 bits are used to store a compressed encoding of the cumulative frequencies as described above. Adding another 64 bits to the bucket payload to store boundary information permits the seven interior boundaries to each be represented as a 9-bit integer encoding the bucklet width, which means that seven of the bucklets have a maximum width of 511 domain values, while one bucklet—referred to herein as the implicit bucklet—has unconstrained width that can be computed by first computing the width of the overall bucket from the bucket boundaries, and then subtracting the stored widths of the other seven bucklets. The remaining 64−7*9=1 bit may be used as a flag to indicate whether the implicit bucklet is the first or the last bucklet in the bucket. The construction algorithm for the overall bucket simply invokes B
In some aspects, embodiments herein may, at least, simplify the optimization of a query (in a plan generator) and/or provide a cardinality estimation guarantee. For example, in an instance a query optimizer generates a first plan with an accurate cardinality estimate and thus a histogram with a guaranteed maximum error of “x”, then the optimizer may efficiently determine that a second (or any other) plan that does not provide better guarantee that the factor “x” should not be chosen or recommended.
In some embodiments, data source 105 is implemented in Random Access Memory (e.g., cache memory for storing recently-used data) and one or more fixed disks (e.g., persistent memory for storing their respective portions of the full database). Alternatively, data source 105 may implement an “in-memory” database, in which volatile (e.g., non-disk-based) memory (e.g., Random Access Memory) is used both for cache memory and for storing its entire respective portion of the full database. In some embodiments, the data of data source 105 may comprise one or more of conventional tabular data, row-based data stored in row format, column-based data stored in columnar format, and object-based data. Data source 105 may also or alternatively support multi-tenancy by providing multiple logical database systems which are programmatically isolated from one another. Moreover, the data of data source 105 may be indexed and/or selectively replicated in an index to allow fast searching and retrieval thereof.
Metadata 120 may provide information regarding the structure, relationships and meaning of the data stored within data source 105. Metadata 120 may also include data defining views on the stored data, and queries that may be executed upon the stored data. Metadata 120 may be generated by a database administrator.
Query server 115 generally provides data of data source 110 to reporting clients, such as client 110, in response to instructions (e.g., SQL statements) received therefrom. In some embodiments, query server 115 receives an instruction from client 110. Query server 115 generates an execution plan based on the instruction and on metadata 140. The execution plan is forwarded to data source 105, which executes the plan and returns a dataset based on the SQL script. Query server 115 then returns the dataset to client 110. Embodiments are not limited thereto.
Client 110 may comprise one or more devices executing program code of a software application for presenting user interfaces to allow interaction with query server 115. Presentation of a user interface may comprise any degree or type of rendering, depending on the type of user interface code generated by query server 115. For example, client 110 may execute a Web Browser to receive a Web page (e.g., in HTML format) from query server 115, and may render and present the Web page according to known protocols. Client 110 may also or alternatively present user interfaces by executing a standalone executable file (e.g., an .exe file) or code (e.g., a JAVA applet) within a virtual machine.
Apparatus 300 includes processor 305 operatively coupled to communication device 310, data storage device 330, one or more input devices 320, one or more output devices 325 and memory 315. Communication device 310 may facilitate wired or wireless communication with external devices, such as a client device or a data storage device. Input device(s) 320 may comprise, for example, a keyboard, a keypad, a mouse or other pointing device, a microphone, knob or a switch, an infra-red (IR) port, a docking station, and/or a touch screen. Input device(s) 320 may be used, for example, to enter information into apparatus 300. Output device(s) 325 may comprise, for example, a display (e.g., a display screen) a speaker, and/or a printer.
Data storage device 330 may comprise any appropriate persistent storage device, including combinations of magnetic storage devices (e.g., magnetic tape, hard disk drives and flash memory), optical storage devices, Read Only Memory (ROM) devices, etc., while memory 315 may comprise Random Access Memory (RAM).
Query server 335 may comprise program code or instructions executed by processor 305 to cause apparatus 300 to perform any one or more processes based on the algorithms disclosed herein. Embodiments are not limited to execution of such processes by a single apparatus. Data source 340 may implement data source 105 as described above. As also described above, data source 340 may be implemented in volatile memory. Data storage device 330 may also store data and other program code for providing additional functionality and/or which are necessary for operation of apparatus 300, such as device drivers, operating system files, etc.
To empirically validate the techniques disclosed herein, applicant implemented multiple histogram bucket construction algorithms within SAP HANA. Experiments were then performed to test both the construction time and resulting histogram size for histograms built with the different bucket construction algorithms. The experiments were performed on a HANA system SPS7 running on a SLES11 SP1 system with Intel Xeon E5-2660 CPUs with 2.2 GHz regular clock speed and 256 GB main memory. All binaries were compiled using GCC 4.3.4 with O3. Because the histogram construction code used in the experiments was single-threaded, all experiments ran on a single core. However, the methods and processes disclosed herein are not limited to being performed, implemented, or embodied by such a system.
Two different data sets were used in the experiments. The first data set was taken from an internal development system for SAP ERP system used to analyze mixed OLAP and OLTP workloads. The schema consists of 133 tables with 757 columns in total. The second data set was derived from customer data of a large warehouse system. The schema consists of 229 tables with 2410 columns in total. The largest of these columns contains 168 million distinct values.
For many of these columns, it does not make sense to create a histogram since:
Applying these restrictions on the columns resulted in 688 columns of the ERP data set and 192 of the BW data set.
The histograms for each column were created five times, and we report the average construction time for these five runs. For all experiments, we test with a maximal q-error per bucket of 2.0. This choice of a rather small q-error guarantees high quality estimates, and is, in some embodiments, the default value for maximal q-error within the SAP HANA implementation. If not mentioned otherwise, we let the histogram construction component choose a value for parameter θ using a simple sublinear function of the cumulated frequency.
The construction time and space consumption of histograms generated over dictionary-encoded values were analyzed. In some embodiments, dictionary-encoded values are the default case for histograms in SAP HANA.
Histograms were constructed by five different construction algorithms:
Note that the difference between algorithms 1 Dinc and V8inc is primarily one of storage format. As such, we expect their construction times to be very similar, and this is confirmed by
The utility of the incremental bucket construction method disclosed herein and particularly described in paragraphs [0057]-[0065] is illustrated by comparing the construction times for algorithms F8Dgt and V8Dinc. These algorithms both build histogram buckets containing eight bucklets, they utilize similar heuristics, and neither algorithm includes the optimizations from paragraphs [0036]-[0056]; the primary difference is that F8Dgt has a generate-and-test structure, whereas V8Dinc uses the incremental construction introduced and disclosed herein.
In some aspects, a utility of the optimizations described in paragraphs [0036]-[0056] herein can be demonstrated by comparing algorithms V8Dinc and V8DincB, which are identical other than these additional optimizations.
In some aspects, a utility of the variable-width bucklet-based storage format described in paragraph [0068] herein can be demonstrated by comparing the memory consumption of algorithms V8Dinc and 1Dinc.
The foregoing diagrams represent logical architectures for describing processes according to some embodiments, and actual implementations may include more or different components arranged in other manners. Other topologies may be used in conjunction with other embodiments. Moreover, each system described herein may be implemented by any number of devices in communication via any number of other public and/or private networks. Two or more of such computing devices may be located remote from one another and may communicate with one another via any known manner of network(s) and/or a dedicated connection. Each device may comprise any number of hardware and/or software elements suitable to provide the functions described herein as well as any other functions. For example, any computing device used in an implementation of system 100 and/or system 300 may include a processor to execute program code such that the computing device operates as described herein.
All algorithms and processes disclosed herein may be embodied in processor-executable program code read from one or more of non-transitory computer-readable media, such as a removable disk, a CD-ROM, a DVD-ROM, a Flash drive, and a magnetic tape, and then stored in a compressed, uncompiled and/or encrypted format. In some embodiments, hard-wired circuitry may be used in place of, or in combination with, program code for implementation of processes according to some embodiments. Embodiments are therefore not limited to any specific combination of hardware and software.
Embodiments have been described herein solely for the purpose of illustration. Persons skilled in the art will recognize from this description that embodiments are not limited to those described, but may be practiced with modifications and alterations.
Number | Date | Country | |
---|---|---|---|
61909202 | Nov 2013 | US |