Patent Grant
7392242
The present invention relates to the field of computer databases. More specifically, the present invention relates to the estimating a cost of a query in a multidimensional database.
Database applications are commonly used to store large amounts of data. One branch of database applications that is growing in popularity is Online Analytical Processing (OLAP) applications. OLAP involves the use of computers to extract useful trends and correlations from large databases of raw data. It may involve consolidating and summarizing huge databases containing millions of items (e.g., sales figures from all branches of a supermarket chain) and making this data viewable along multidimensional axes, while allowing the variables of interest to be changed at will in an interactive fashion. As such, the processing and memory load on OLAP servers is very high.
Typically, a multidimensional database stores and organizes data in a way that better reflects how a user would want to view the data than is possible in a two-dimensional spreadsheet or relational database file. Multidimensional databases are generally better suited to handle applications with large volumes of numeric data and that require calculations on numeric data, such as business analysis and forecasting, although they are not limited to such applications.
A dimension within multidimensional data is typically a basic categorical definition of data. Other dimensions in the database allow a user to analyze a large volume of data from many different perspectives. Each dimension may have a hierarchy associated with it. For example, a product group dimension may have a sublevel in the hierarchy that includes entries such as drinks and cookies. The drinks entry may then have its own sublevel of individual product identifiers for each type of drink sold. Each hierarchy may have any number of levels.
For each event, measures may be recorded. In a sales example, this may include sales amount, product identifier, location of purchase, etc. This raw information is known as input level data. This data may be stored in a multidimensional cube. This cube may be extremely large given the number of dimensions and variables typical to businesses, but it may also be extremely sparse, in that there are large gaps where no information is stored. This is because only a small percentage of the possible combinations of variables will actually be used (e.g., no customer is going to purchase every single item in stock over their lifetime, let alone in a single day).
Users typically will issue queries to the database in order to get information they want or need. These queries will typically ask for a summary of data across certain dimensions. In many applications, querying a single cell in the database is rarely needed, as the user typically would not be interested in that fine a detail. For example, in a supermarket chain database, a user may be interested in overall sales for various stores in the month of January, or sales of a certain soft drink in the Southwest over the last year, but they would probably not be interested in how much of a particular product a single customer bought on a single day.
In a relational database, these queries are executed dynamically at runtime, at which point the appropriate data is aggregated. While this method requires the least amount of dedicated storage space, it can be slow, especially as the size of the cube increases. Users typically aren't willing to allow a significant amount of time to run a query.
One solution is to pre-run every single possible query, and materialize the results on disk. While this certainly reduces runtime delays, for very large cubes it can take up a significant amount of memory and processing power. In fact, for cubes typical in many businesses, such a solution would require years of processing time, which is obviously impractical.
Therefore, it is beneficial to materialize only those queries where the benefit of materialization outweighs its costs. As the number of candidate queries for materialization is exorbitant, the queries are typically organized into groups called views. A view is the set of all 1-cell queries such that for each dimension, each query requests information at the same level in the hierarchy. A decision must therefore be made as to which views should be materialized. A key to this decision is knowing how long a particular query will take to execute.
One abstract solution proposed to estimate how long a particular query will take to execute is to look solely at the number of “affected records”, i.e., the number of existing and missing tuples in the materialized view that correspond to the tuples in the query view. While in the abstract this would appear to make sense, in actual practice it results in wildly incorrect estimations. This is due to many factors. One factor is that in real operation, only a small percentage of “affected records” would need to be examined when running a query, due to the sparsity of the data. Another factor is that the order in which the records are stored can have a dramatic effect on the speed at which a query is run. If for example, all of the required records are very close to each other, it may only require one page fetch in order to grab all of them. What is needed is a solution that provides for a better estimate of the time needed to run a particular query in a multidimensional database.
The cost of running a query (having a query range) on a multidimensional database may be estimated using a process factors criteria beyond merely the number of affected records. First, a materialized view of the database may be represented as a container of tuples, sorted by key. Then keys may be stepped through, each key representing a mapping of a combination of tuples from the container. At each step, the process may request the next smallest key in the query range greater than or equal to the key of the current step, which results in the tuple in the database whose key is the smallest, greater than or equal to the requested key, and determine if the resulting tuple is in the query range. The cost of the query may then be estimated as the number of tuples upon which the range check was performed.
The accompanying drawings, which are incorporated into and constitute a part of this specification, illustrate one or more embodiments of the present invention and, together with the detailed description, serve to explain the principles and implementations of the invention.
In the drawings:
Embodiments of the present invention are described herein in the context of a system of computers, servers, and software. Those of ordinary skill in the art will realize that the following detailed description of the present invention is illustrative only and is not intended to be in any way limiting. Other embodiments of the present invention will readily suggest themselves to such skilled persons having the benefit of this disclosure. Reference will now be made in detail to implementations of the present invention as illustrated in the accompanying drawings. The same reference indicators will be used throughout the drawings and the following detailed description to refer to the same or like parts.
In the interest of clarity, not all of the routine features of the implementations described herein are shown and described. It will, of course, be appreciated that in the development of any such actual implementation, numerous implementation-specific decisions must be made in order to achieve the developer's specific goals, such as compliance with application- and business-related constraints, and that these specific goals will vary from one implementation to another and from one developer to another. Moreover, it will be appreciated that such a development effort might be complex and time-consuming, but would nevertheless be a routine undertaking of engineering for those of ordinary skill in the art having the benefit of this disclosure.
In accordance with the present invention, the components, process steps, and/or data structures may be implemented using various types of operating systems, computing platforms, computer programs, and/or general purpose machines. In addition, those of ordinary skill in the art will recognize that devices of a less general purpose nature, such as hardwired devices, field programmable gate arrays (FPGAs), application specific integrated circuits (ASICs), or the like, may also be used without departing from the scope and spirit of the inventive concepts disclosed herein.
In an embodiment of the present invention, each materialized view is stored as a B-tree of tuples. Every member for each dimension may have a unique member number. The keys of the tuples may be binary integers constructed from the bits on the member numbers. These member numbers may be combined into a single key representing the tuples. This is illustrated in
It should be noted that while an embodiment of the present invention utilizes a B-tree to store and/or represent the tuples, one of ordinary skill in the art will recognize that other structures may be used to accomplish the same result, and the claims should not be interpreted to be limited to B-tree implementations.
Each query constrains the set of tuples that need to be looked up. Whenever a query is executed, the B-tree may then be traversed using an algorithm similar to the following.
In an embodiment of the present invention, the estimated cost assigned to a specific query is proportional to the number of calls to processTuple( ).
In this discussion, it may be assumed that the number of children of every member is either 0 or a power of two, and the hierarchy of every dimension is perfectly balanced. It may further be assumed that the distribution of keys is random.
For every query that requests a tuple from a view that is not materialized, the system may potentially have to aggregate the results from the partial results located in some materialized view. For example, suppose only the level 0 view is materialized for the hierarchy illustrated in
Suppose further the two bits (aa) of the 2003 dimension are more significant in the resulting key than the two bits (bb) of the Region dimension. Then the key map may be represented as “aabb”. To answer the query, the values of the tuple with the following keys must be added together: 0001, 0101, 1001, and 1101. As can be seen, to determine if the tuple retrieved by findTuple( ) is in the query box, one can simply mask the tuple's key with 0011. If and only if the result is 0001, then this value must be added to the result.
Note that the view of the resulting tuple determined which bits of the source view's keys must be masked to perform the range check. In other words, the view of the resulting tuple determined which bits of the source view's key are fixed, and which bits are allowed to vary. Thus, a 1-tuple query can always be represented as a set of fixed (f) and variable (v) bits. The above query may be represented as “vvff”.
There may be several different variations of the estimation process. Some of these variations will be discussed here. The first may be known as the Least Significant Bit (LSB) Query Cost method. This method moves from the least significant bit to the most significant bit. This is best illustrated by an example, which will be described in the text as well as in
Assume a query fffffv. Furthermore, assume the fixed bits are fixed at 0, so the query is 00000v. Reference a 300 in
The query may be complicated a bit by adding another variable bit, for example: fffvfv. Again, one may assume all fixed bits equal 0. Reference b 302 in
Finally, looking at the query fvfvfv, depicted in reference c 304 in
The results may be generalized as follows. First, the initial cost of a single search must be incurred no matter what. All the additional searches are “extra” searches. The process moves from the least significant bit to the most significant bit. Each time another variable bit is encountered, the expected number of extra searches may be multiplied by 2 and added to the probability that a tuple occurs in the range from the maximum key searched so far to the key 2n, where n is the current bit. In code, this may be written as:
The calculation of P(0) here assumes a random distribution. A common formula known as Cardenas' formula may be used to approximate the expected number of ranges that are filled when the distribution is random and the size of a range is large.
In a second variation of the process, a weight for each bit in the query may be used, and thus this may be known as the Bit Weight Query Cost method.
For example, if the query is vfffff, then the keys will fall into two possible buckets: keys beginning with a 0 and keys beginning with a 1. Cardenas' formula may be used to determine the expected number of buckets that are filled, since this value is likely to be very close to 1 or very close to 2, the query cost may be approximated as simply cardenas(2, cellCount).
The query may be further complicated by adding another variable bit to get: vvffff. This time, there are four possible buckets. Using Cardenas' formula, the expected number of buckets that are filled can be arrived at by using cardenas(4, cellcount). Again, the number of processed tuples can be approximated as the number of filled buckets.
If the query is vvvfff, the approximate number of filled buckets may be cardenas(8, cellcount). This is the query cost.
This example may be made even more interesting by inserting a fixed bit to achieve: vfvfff. Of the four possible buckets created by the most significant two bits, cardenas (4, cellCount) of them are likely to be filled. Since cardenas (8, cellCount) 3-bit buckets are likely to be filled, there are likely cardenas(8, cellcount)/cardenas(4, cellcount) 3-bit buckets likely to be filled for each 2-bit bucket. However, because of the most significant variable bit, the cardenas (2, cellcount) two-bit buckets are all that is cared about. Therefore, the expected query cost is cardenas(2, cellcount)*cardenas(8, cellcount)/cardenas(4, cellcount).
In general, a weight may be associated with each bit of the key. The weight is cardenas (2n+1, cellcount)/cardenas (2n, cellcount), where n=0 for the most significant bit, n=1 for the next most significant bit, and so on. Then, to get the query cost, the weight of each variable bit may be multiplied.
The Bit Weight Query Cost method does have an advantage over the LSB method in that it is easier to refine the method to account for the possibility that some of the assumptions made earlier in this document are incorrect. The Bit Weight Query Cost method may be refined to account for the possibility of the inaccuracy of the assumption that the number of children of every member is either 0 or a power of two, and the hierarchy of every dimension is perfectly balanced. One refinement is called the grouping capacity refinement, and the other is called the bit count refinement. Both approaches employ an “effective bucket count” strategy. Normally, as the process moves from the most significant bit to the least significant bit in the Bit Weight Query Cost algorithm, the bucket count is multiplied by two for each new bit. Using the refinements here, the effective bucket count is actually multiplied by a number between 1 and 2, depending upon the various characteristics of the hierarchy.
In the grouping capacity refinement, it is known that each dimension may have one or more groupings, each of which is given n bits within the key. But the actual number of members at that grouping of the dimension is not necessarily reflected by the total number of possible combinations of the corresponding bits within the key. The process may calculate m, the average number of members in the grouping for every combinations of the other groupings in the dimension. The effective bucket count multiplier in this approach is equal to the 2 divided by the nth root of m.
The bit count refinement employs an effective bucket count strategy that attempts to account for members with children not a power of 2, as well as drastic imbalances in the hierarchy. In this approach, when the outline is initialized, a count c is maintained for every bit of every member number and is incremented by one for each level 0 member for which that bit was set. In a perfectly balanced hierarchy, each bit would have c equal to half the number of level 0 members m. Due to imbalances, c could be more or less than half m. It is assumed that the number of effective buckets increases by 2 each time the bit is set or unset, whichever occurs least frequently. For the remaining cases, the number of effective buckets is not increased. On average then, the number of effective buckets increases by m/max (c, m−c). This is the effective bucket count multiplier.
Cardenas' formula gives the number of distinct values for n values in v possible distinct values as v−v*(1−1/v)n. In the context of query costing, the inputs v and n are often so large that double precision floating point arithmetic cannot accurately compute the correct answer.
The approximation (1+e)n˜1+n*e for small e and small n*e may be used. Furthermore, if v is much greater than n or n is much greater than v, the result can be approximated by the smaller of n and v. Thus, the Cardenas' formula may be implemented as follows.
At 514, the processing cost of the query may be estimated at one plus the result value produced by the loop.
Once the loop is complete, a query processing cost result estimator 814 coupled to the counter value incrementer 812 may estimate the processing cost of the query at one plus the result value produced by the loop.
While embodiments and applications of this invention have been shown and described, it would be apparent to those skilled in the art having the benefit of this disclosure that many more modifications than mentioned above are possible without departing from the inventive concepts herein. The invention, therefore, is not to be restricted except in the spirit of the appended claims.
The present invention claims priority based on provisional patent application Ser. No. 60/548,198, entitled “QUERY COSTING IN A MULTIDIMENSIONAL DATABASE”, by Jonathan Baccash, Igor Nazarenko, Uri Rodny and Ambuj Shatdal, filed on Feb. 27, 2004.
| Number | Name | Date | Kind |
|---|---|---|---|
| 6003022 | Eberhard et al. | Dec 1999 | A |
| 6330552 | Farrar et al. | Dec 2001 | B1 |
| 6353826 | Seputis | Mar 2002 | B1 |
| 20030126143 | Roussopoulos et al. | Jul 2003 | A1 |
| 20040167874 | Chong et al. | Aug 2004 | A1 |
| Number | Date | Country | |
|---|---|---|---|
| 60548198 | Feb 2004 | US |
