CONSISTENT AND UNBIASED CARDINALITY ESTIMATION FOR COMPLEX QUERIES WITH CONJUNCTS OF PREDICATES

Abstract

A method of selectivity estimation is disclosed in which preprocessing steps improve the feasibility and efficiency of the estimation. The preprocessing steps are: partitioning (to make iterative scaling estimation terminate in a reasonable time for even large sets of predicates); forced partitioning (to enable partitioning in case there are no “natural” partitions, by finding the subsets of predicates to create partitions that least impact the overall solution); inconsistency resolution (in order to ensure that there always is a correct and feasible solution); and implied zero elimination (to ensure convergence of the iterative scaling computation under all circumstances). All of these preprocessing steps make a maximum entropy method of selectivity estimation produce a correct cardinality model, for any kind of query with conjuncts of predicates. In addition, the preprocessing steps can also be used in conjunction with prior art methods for building a cardinality model.

Description

BRIEF DESCRIPTION OF THE DRAWINGS

FIG. 1 illustrates exemplary QEPs (a), (b) and (c) and associated prior art selectivity estimates;

FIG. 2 is a block diagram for a method of preprocessing and estimating selectivities according to an embodiment of the present invention;

FIG. 3 is a Venn diagram illustrating an exemplary probability space given some known conjunctive predicate estimates according to an embodiment of the present invention;

FIG. 4 is a Venn diagram illustrating a maximum entropy solution for the exemplary probability space shown in FIG. 3; and

FIG. 5 is a pseudo-code algorithmic specification for forced partitioning for computation of selectivities according to an embodiment of the present invention.

DETAILED DESCRIPTION OF THE INVENTION

The following detailed description is of the best currently contemplated modes of carrying out the invention. The description is not to be taken in a limiting sense, but is made merely for the purpose of illustrating the general principles of the invention, since the scope of the invention is best defined by the appended claims.

Broadly, embodiments of the present invention provide a novel maximum entropy (ME) method for estimating the selectivity of conjunctive predicates, utilizing an approach that is information-theoretically sound, i.e., valid from the point of view of information theory, invented by Claude Shannon of MIT circa 1947, as known in the art, and that takes into account available statistics on both single columns and groups of columns. Embodiments of the invention find application, for example, in commercial query optimizers that use statistical information on the number of rows in a relational database management system table and the number of distinct values in a column to compute the selectivities of simple predicates.

Unlike prior art ad hoc query optimizers—such as the example given above—that select from available knowledge and thus remain subject to inconsistencies and to introducing biases, embodiments avoid arbitrary biases, inconsistencies, and the flight from knowledge to ignorance by deriving missing knowledge using the ME principle. Embodiments exploit any and all available multi-attribute information, in contrast to prior art selectivity models that typically ignore at least some available information.

Embodiments also differ from the prior art by introducing methods for resolving inconsistencies in the available multivariate statistics (MVS) that would otherwise prevent computation of a ME solution; such inconsistencies can arise when the single-column statistics in the catalogue have been computed only approximately, or when the various statistics used by the optimizer have been computed at different points in time.

Other embodiments differ from the prior art by including novel partitioning methods that permit application of the inventive method of selectivity estimation (and also prior art methods of selectivity estimation) to complex queries with many predicates. For example, in various embodiments, the efficiency of the ME computation can be improved—often by orders of magnitude—by partitioning the predicates into disjoint sets and computing an ME distribution for each of the resulting sub-problems. Thus, estimating selectivities for a complex query, e.g., one having more than 10 predicates, may be feasible with the present invention where not practical using prior art methods.

Further embodiments differ from the prior art by providing methods for detecting and eliminating atoms of the predicates with implied selectivity of zero (also referred to as “zero atoms” or “zeroes”) prior to the computation of the selectivities of the predicates. Implied zero elimination may ensure convergence of an iterative scaling algorithm used in some embodiments to compute selectivies using the ME model. As with the novel methods for resolving inconsistencies and for partitioning, the novel method for implied zero elimination may also be applied to prior art methods for estimating selectivities.

The problem of selectivity estimation for conjunctive predicates, given partial MVS, may be formalized using the following terminology. A set of boolean factors (BFs) may be denoted as P={p₁, . . . , p_n}. For any X⊂N={1, . . . , n}, p_X is used to denote the conjunctive predicate _iεXp_i. The symbol s denotes a probability measure over 2^N, the powerset of N, with the interpretation that s_X is the selectivity of the predicate p_X. (The quantity s_X can also be interpreted as the probability that a randomly selected row satisfies p_X.) Usually, for |X|=1, the histograms and column statistics from the system catalog determine s_X and are all known. For |X|>1, the MVS may be stored in the database system catalog either as multidimensional histograms, index statistics, or some other form of column-group statistics or statistics on intermediate tables. In practice, s_X is not known for all possible predicate combinations due to the exponential number of combinations of columns that can be used to define MVS. Suppose that s_X is known for every X in some collection T⊂2^N. The collection T may be referred to as the “knowledge set” for the predicates P. It may be defined that the empty set Ø is always part of T, because s_Ø=1 when applying no predicates. Given that s_X is known for every X in the collection T, the selectivity estimation problem is to compute s_X for Xε2^N\T, i.e., to compute .s_X for all the remaining sets X not in the collection T.

Information theory defines for a probability distribution q=(q₁, q₂, . . . ) a measure of uncertainty called entropy:

H(q)=−Σ_iq_i log q_i.

The ME principle prescribes selection of the unique probability distribution that maximizes the entropy function H(q) and is consistent with respect to the known information. Entropy maximization without any additional information uses the single constraint that the sum of all probabilities equals 1. The ME probability distribution is then the uniform distribution. When constraints only involve marginals of a multivariate distribution, the ME solution coincides with the independence assumption. Thus, query optimizers that do not use MVS comprise a special case of estimating their selectivities for conjunctive queries according to the ME principle: they assume uniformity when no information about column distributions is available, and they assume independence because they do not know about any correlations. In contrast, integrating the more general ME principle into the inventive optimizer's cardinality model, thereby generalizes these concepts of uniformity and independence. The ME principle enables the inventive optimizer to take advantage of all available information in a consistent way, avoiding inappropriate bias towards any given set of selectivity estimates. The ME principle applied to selectivity estimation means that, given several selectivities of simple predicates and conjuncts, the inventive optimizer chooses the most uniform and independent selectivity model consistent with this information.

Referring now to FIG. 2, method 200 for estimating selectivities for a query may begin with an input 202 of a query with predicates indexed by the set N, for example, P={p₁, . . . , p_n} and N={1, . . . , n}. N may be called the index set of P. A brief description of method 200 is given here, with more detailed descriptions of partitioning (206), resolving inconsistencies (210), and implied zero detection and elimination (212) steps of method 200 given further below.

At step 204, method 200 may retrieve single, e.g., individual selectivities s₁, s₂, . . . s_n, and multivariate, e.g., joint selectivities, such as s_1,2, s_3,5, and s_2,3,4, statistics T from the database, where T is a collection T⊂2^N, as formalized above.

At step 206, if there is a need to reduce computational complexity of estimating selectivities, the overall problem of estimating selectivities may be divided into several sub-problems 207 as indicated by the branching of flow arrows at 208. To accomplish a division of the overall problem into subproblems, method 200 may partition N with respect to T. The overall problem may then be solved by combining solutions for the sub-problems 207 as indicated by the rejoining of flow arrows at 209. If T satisfies a certain condition (described below), method 200 may accordingly partition N with respect to T (“naturally”) at step 206 and, if not, method 200 may use forced partitioning to partition N with respect to T at step 206. Both types of partitioning, which may occur at step 206, are described in more detail below. Each of the sub-problems 207 corresponds to a component N_k of the partition of N. (If N is not partitioned, there is only one component N₁=N and one sub-problem 207.)

At step 210, method 200 may resolve inconsistencies for each of the sub-problems 207 as described in more detail below.

At step 212, method 200 may detect and eliminate zero atoms as described in more detail below.

At step 214, method 200 may compute an ME solution of selectivity estimates for each component of the partition of N with respect to T.

At step 216, the solutions for the partitions may be combined, as described in more detail below in connection with partitioning.

At output 218, an overall solution for the problem of estimating selectivities may be given in accordance with the ME selectivity model.

In order to describe in more detail the partitioning (206), resolving inconsistencies (210), and implied zero detection and elimination (212) steps of method 200, the following begins with a description of the constrained optimization problem that may be solved at step 214 in order for method 200 to provide selectivity estimation at step 214. The constrained optimization problem is described first in order to provide a consistent terminology for describing the partitioning (206), resolving inconsistencies (210), and implied zero detection and elimination (212) steps.

Rather than applying the steps of resolving inconsistencies (210) and detecting zero atoms (212) directly to a selectivity estimation problem it may be more computationally efficient to first break up the selectivity estimation problem into smaller problems before applying the steps of resolving inconsistencies (210) and detecting zero atoms (212) individually to each smaller problem. Thus, method 200 may first perform the partitioning (206) process which accomplishes this breaking up of a problem into smaller sub-problems.

Likewise, because a newly consistent set of constraints may have new zero atoms, method 200 may first obtain a consistent starting set of constraints by applying the process of resolving inconsistencies (210) to the (sub)problem before detecting and removing zero atoms (212). Also, because the steps of resolving inconsistencies (210) and detecting zero atoms (212) may facilitate a more efficient computation of solutions to the constrained optimization (sub)problems for selectivity estimation, method 200 may first perform all the preprocessing steps of partitioning (206), resolving inconsistencies (210), and detecting zero atoms (212) before computing an ME solution for the constrained optimization problem. Thus, while the sections below are presented in a certain order to facilitate a logical and consistent exposition, that order of presentation of the processing steps of method 200 may be the reverse of the exemplary order of execution in method 200. Thus, each heading below is cross-referenced to a corresponding step of method 200.

The Constrained Optimization Problem (Step 214)

Given a set of predicates P={p₁, p₂, . . . , p_n}, each of the corresponding atoms—terms in disjunctive normal form (DNF), i.e., a term of the form _iεN p_i^b_i for b_iε{0,1}—may be denoted in abbreviated form by a binary string of length n. For example, when n=3, the string b=100 denotes the atom p₁p₂p₃, and so forth. As above, N={1,2, . . . ,n} and 2^N is used to denote the set of all subsets of N. For each predicate p^X, Xε2^N, C(X) is used to denote the set of components of X, i.e., the set of all atoms contributing to p_X. Formally,

C(X)={bε={0,1}ⁿ|b_i=1 for all iεX} and C(Ø)={0,1} ⁿ.

For example, for predicates p₁ and p_1,2, the components are:

C({1})={100, 110, 101, 111} and C({1,2})={110, 111}.

Additionally, for each possible knowledge set T⊂2^N, P(b, T) denotes the set of all XεT such that p_X has b as an atom in its DNF representation, i.e.,

P(b,T)={XεT|b_i=1 for all iε}∪{Ø}.

Thus, for the atom 011 and T=2^{1,2,3}, for example, P(b,T)={{2}, {3}, {2,3}, 0}.

Given s_X for XεT, s_X for X∉T may be computed according to the ME principle. To this end, method 200, for example, at step 214, may solve the following constrained optimization problem:

$\begin{array}{cc}\underset{x_b|b\in {\left\{0,1\right\}}^n}{\mathrm{minimize}}\sum _{b\in {\left\{0,1\right\}}^n}x_b\log x_b& \left(\mathrm{Equation}1\right)\end{array}$

subject to the |T| constraints

Σ_bεC(X)x_b=s_X,XεT,  (Equation 2)

where x_bε[0,1] denotes the selectivity of atom b. The |T| constraints correspond to the known selectivities. One of the included constraints is s_Ø=Σ_bε{0,1}nx_b=1, which asserts that the combined selectivity of all atoms is 1. The solution is a probability distribution with the maximum value of uncertainty (entropy), subject to the constraints. Given this solution, method 200 can compute any arbitrary selectivity s_X as s_X=Σ_bεC(X)x_b. The above problem can be solved analytically only in simple cases with a small number of unknowns. In general, a numerical solution method is required to solve the constrained optimization problem—such as an iterative scaling computation or a Newton-Raphson method.

EXAMPLE

FIG. 3 shows the probability space for the case N={1,2,3}, T={{1}, {2}, {3}, {1,2}, {1,3}, Ø}, and selectivities s₁=0.1, s₂=0.2, s₃=0.25, s₁₂=0.05, s₁₃=0.03, and s_Ø=1.

The example of FIG. 3 implies the following six constraints:

s ₁ =x ₁₀₀ +x ₁₁₀ +x ₁₀₁ +x ₁₁₁=0.1  (I)

s ₂ =x ₀₁₀ +x ₀₁₁ +x ₁₁₀ +x ₁₁₁=0.2  (II)

s ₃ =x ₀₀₁ +x ₀₁₁ +x ₁₀₁ +x ₁₁₁=0.25  (III)

s _1,2 =x ₁₁₀ +x ₁₁₁=0.05  (IV)

s _1,3 =x ₁₀₁ +x ₁₁₁=0.03  (V)

s_Ø=Σ_bε{0,1}3x_b=1  (VI)

The task of selectivity estimation is to now compute a solution for all atoms x_b, bε{0,1}³ that maximizes the entropy function −Σ_bε{0,1}3x_b log x_b and satisfies the above six constraints. Any desired selectivity s_X can then be computed from the x_b values as indicated previously.

FIG. 4 gives the results obtained when solving this constrained optimization problem. For instance, in this ME solution, method 200 may obtain the selectivity estimate s_1,2,3=x₁₁₁=0.015 and s_2,3=x₁₁₁+x₀₁₁=0.05167.

Detection of Implied Zero Atoms (Step 212)

It is often the case that the constraints in Equation 2 imply that the selectivity of certain atoms must be zero in any feasible solution. For instance, if pp₂, so that s₁=s_1,2, then x₁₀₀=x₁₀₁=0 in any solution x. These zero atoms, i.e., atoms for which the selectivity=0, can destabilize numerical solution algorithms so that they do not converge to a solution of the ME optimization problem. Consequently, all zero atoms must be identified and explicitly removed from the constraints in the ME optimization problem prior to execution of numerical solution algorithms.

Identifying zero atoms is nontrivial in general, because the reasoning involved can be arbitrarily complex. In the following sections, an iterative sub-method 212a and an approximation sub-method 212b are described for automatically detecting zero atoms, either of which may be used by method 200, for example, at step 212. The iterative sub-method 212a provides an exact solution but is computationally expensive while the approximation sub-method 212b is relatively quick (computationally inexpensive) but approximate.

Iterative Detection of Zero Atoms (Sub-Method 212a)

The iterative sub-method 212a for detecting zero atoms is based on the following. If, for a given atom b, there exists a feasible solution x that satisfies all constraints in Equation 2 and in which x_b>0, then x_b is also positive in the ME solution.

The iterative sub-method 212a for zero detection may begin with an initial set A₀ of candidate zero atoms that contains all of the atoms: A₀={0,1}ⁿ. In the first iteration the iterative sub-method 212a may set i=0 and solve the linear program (LP):

$\begin{array}{cc}\underset{x_b|b\in {\left\{0,1\right\}}^n}{\mathrm{maximize}}\sum _{b\in A_i}x_b\mathrm{subject}\mathrm{to}\mathrm{Equation}2\mathrm{and}\mathrm{to}x_b\in [0,1],b\in {\left\{0,1\right\}}^n& \left(\mathrm{Equation}3\right)\end{array}$

using, e.g., the simplex method (as known in the art). The idea is that a solution to the above problem can make each x_b as large as possible while satisfying the feasibility conditions. Any x_b that is equal to 0 in the LP solution is therefore likely to be a zero atom. However, an x_b that is equal to 0 in the LP solution is not guaranteed to be a zero atom; it could be the case that x_b=0 in the computed optimal LP solution but not in other possible optimal LP solutions. The iterative sub-method 212a may therefore refine the set of candidates as A₁=A₀\{b|x_b≠0}, set i=1, and solve the resulting LP in Equation 3. The iterative sub-method 212a may proceed in this manner, iterating until either (i) A_i=Ø or (ii) x_b=0 for every bεA_i. In the former case, each candidate atom can have been shown to have positive probability in at least one feasible solution, and hence, as discussed previously, should be positive in the ME solution, and, therefore the iterative sub-method 212a may conclude that there are no zero atoms. In the latter case, the objective-function value for the solution to the LP in Equation 3 is 0, and therefore each atom bεA_i should have x_b=0 in any feasible solution—otherwise, the optimal objective-function value would have been positive—and hence each atom bεA_i should have had x_b=0 also in the ME solution. Therefore, the iterative sub-method 212a may conclude that A_i is precisely the set of zero atoms.

Example: Iterative Zero Detection (Sub-Method 212a)

Suppose that N={1,2,3}, T={{1}, {2}, {3}, {1,2}, {1,3}, {2,3}, Ø}, s₁=0.23, s₂=0.01, s₃=0.015, s_1,2=0.01, s_1,3=0.01, s_2,3=0.01, and s_Ø=1. The constraints in the ME optimization problem are:

s ₁ =x ₁₀₀ +x ₁₁₀ +x ₁₀₁ +x ₁₁₁=0.23

s ₂ =x ₀₁₀ +x ₀₁₁ +x ₁₁₀ +x ₁₁₁=0.01

s ₃ =x ₀₀₁ +x ₀₁₁ +x ₁₀₁ +x ₁₁₁=0.015

s _1,2 =x ₁₁₀ +x ₁₁₁=0.01

s _1,3 =x ₁₀₁ +x ₁₁₁=0.01

s _2,3 =x ₀₁₁ +x ₁₁₁=0.01

s_Ø=Σ_bε{0,1}3x_b=1

Setting A₀={0,1}³ and solving the LP in Equation 3, the iterative sub-method 212a obtains a solution in which x₀₀₀, x₁₀₀, x₀₀₁, and x₁₁₁, are nonzero. For the next iteration the iterative sub-method 212a therefore sets A₁={010, 110, 101, 011}. Solving the resulting LP, the iterative sub-method 212a finds that x₀₁₀=x₁₁₀=x₁₁₀, =x₀₁₁=0, so that A₂ is the set of zero atoms.

The foregoing iterative sub-method 212a can be shown to discover every zero atom and not misclassify any nonzero atoms as zero atoms. Unfortunately, due to its iterative nature, the sub-method 212a may be so computationally expensive as to be impractical in real-world applications, even with a highly sophisticated LP solver.

Zero Detection Via Approximation (Sub-Method 212B)

A more practical implementation of method 200 may employ an approximation detection sub-method 212b, for example, at step 212 of method 200, that may offer a reasonable trade-off between accuracy and execution time. The approximation detection sub-method 212b may first rewrite the selectivity of each atom as the sum of two new variables: x_b=v_b+w_b. The approximation detection sub-method 212b may now solve the following LP:

$\underset{v_b,w_b}{\mathrm{maximize}}\sum _{b\in {\left\{0,1\right\}}^n}v_b\mathrm{subject}\mathrm{to}$ $\sum _{b\in C\left(X\right)}v_b+w_b=s_X,X\in T$ $0\le w_b\le 1\mathrm{and}0\le v_b\le \varepsilon ,b\in {\left\{0,1\right\}}^n$

where ε is a small value. For example, ε=0.0001 may be selected, and the value chosen may depend on various factors such as execution time for solving the LP and the level of precision desired. After solving this LP, an atom b may be considered to be a zero atom if and only if w_b=v_b=0. The idea is that setting x_b=0 requires setting v_b=0, which can significantly decrease the objective-function value because of the ε upper bound on all of the selectivities. Thus only “true” zero atoms are likely to be identified by the solution to the LP. The approximation detection sub-method 212b may include the w_b variables because they provide the “padding” needed to ensure that the original constraints in Equation 2 are satisfied. The approximation detection sub-method 212b can find all of the zero atoms, but may be considered approximate in that it can mislabel some nonzero atoms as zero atoms. In practice, such mislabelings tend to be infrequent, so that the quality of the ultimate ME solution (e.g., given by method 200) remains good.

Example: Zero Detection Via Approximation (Sub-Method 212B)

As in the previous example for sub-method 212a, again suppose that N={1,2,3}, T={{1}, {2}, {3}, {1,2}, {1,3}, {2,3}, Ø}, s₁=0.23, s₂=0.01, s₃=0.015, s_1,2=0.01, s_1,3=0.01, S_2,3=0.01, and s_Ø=1. The constraints in the resulting LP are:

s ₁ =v ₁₀₀ +w ₁₀₀ +v ₁₁₀ +w ₁₁₀ +v ₁₀₁ +w ₁₀₁ +v ₁₁₁ +w ₁₁₁=0.23

s ₂ =v ₀₁₀ +w ₀₁₀ +v ₀₁₁ +w ₀₁₁ +v ₁₁₀ +w ₁₁₀ +v ₁₁₁ +w ₁₁₁=0.01

s ₃ =v ₀₀₁ +w ₀₀₁ +v ₀₁₁ +w ₀₁₁ +v ₁₀₁ +w ₁₀₁ +v ₁₁₁ +w ₁₁₁=0.015

s _1,2 =v ₁₁₀ +w ₁₁₀ +v ₁₁₁ +w ₁₁₁=0.01

s _1,3 =v ₁₀₁ +w ₁₀₁ +v ₁₁₁ +w ₁₁₁=0.01

s _2,3 =v ₀₁₁ +w ₀₁₁ +v ₁₁₁ +w ₁₁₁=0.01

s _Ø=Σ_bε{0,1}3v_b+w_b=1

In the solution returned by the simplex algorithm, the following variables are equal to zero: v₀₁₀, w₀₁₀, v₁₁₀, w₁₁₀, v₁₀₁, w₁₀₁, v₀₁₁, w₀₁₁. The approximation detection sub-method 212b therefore can take the set of zero atoms as {010, 110, 101, 011}. Observe that this solution for this example coincides with the solution for the previous example returned by the iterative detection sub-method 212a

Resolving Inconsistencies (Step 210)

A significant problem encountered when applying ME selectivity estimation method 200 in a real-world database management system is that the given selectivities {s_X: XεT} might not be mutually consistent. For example, the selectivities s₁=0.1 and s_1,2=0.15 are inconsistent, because they violate the obvious requirement that s_X≧s_Y whenever X⊂Y. In the presence of inconsistent statistics, there can not exist any solutions to the constrained optimization problem in Equations 1 and 2, much less an ME solution, and the iterative scaling algorithm, for example, if applied blindly, will fail to converge. Therefore, method 200 provides at step 210 a method for resolving inconsistencies that may adjust the input selectivities (obtained, e.g., at step 204) to obtain a set of satisfiable constraints, prior to solution of the constrained optimization problem, (e.g., at step 214) by execution, for example, of the iterative-scaling method.

The method for resolving inconsistencies first may associate two “slack” variables a_X⁺; a_X⁻ with each of the original constraints in Equation 2, except for the constraint corresponding to s_Ø. This latter constraint ensures that the atom selectivities sum to 1, and therefore may not be modified. The method for resolving inconsistencies (step 210) then may solve the following LP:

$\begin{array}{cc}\underset{{a_X}^{+},{a_X}^{-},x_b}{\mathrm{minimize}}\sum _{X\in T\backslash \varnothing }{a_X}^{+}+{a_X}^{-}\mathrm{subject}\mathrm{to}\sum _{b\in C\left(X\right)}x_b+{a_X}^{+}-{a_X}^{-}=s_X,X\in T\backslash \left\{Ø\right\},\sum _{b\in {\left\{0,1\right\}}^n}x_b=1{a_X}^{+}\ge 0,{a_X}^{-}\ge 0\mathrm{and}0\le s_X-{a_X}^{+}+{a_X}^{-}\le 1& \left(\mathrm{Equation}4\right)\end{array}$

The slack variables may represent either positive or negative adjustments to the selectivities needed to ensure the existence of a feasible solution. In this connection, it may be observed that, in the optimal solution to the LP, at most one of the two slack variables for a constraint can be nonzero; indeed, for a specified value a_X⁺-a_X⁻ of the total adjustment, any solution that has a nonzero value for both slack variables yields a higher value of the objective function than a solution with only a single nonzero slack variable. The presence of a nonzero slack variable can both signal the presence of an inconsistency and indicate how to obtain consistency, namely, by setting s_X*:=s_X−a_X⁺+a_X⁻. The constraint in Equation 4 ensures that the adjusted selectivities lie in the range [0,1] By taking the objective function as the sum of the slack variables, the method for resolving inconsistencies (step 210) can ensure that the adjustments to the constraints are as small as possible.

In an alternative embodiment of step 210, the terms in the objective function may be weighted to reflect the fact that, typically, some statistics are more reliable than others. A large weighting coefficient for the slack variables a_X⁺ and a_X⁻ could be used for a corresponding selectivity s_X that is relatively more reliable; thus, this selectivity is relatively unlikely to be adjusted because an adjustment would incur a relatively large penalty in the objective function. By means of this weighting method, unreliable statistics are more likely to be subject to adjustments than reliable ones.

A newly consistent set of constraints may have zero atoms. Method 200, thus, first may obtain a consistent starting set of constraints at step 210 using the methods described here and then detect and remove zero atoms at step 212 as described above under the headings relating to “ZERO DETECTION”.

Example: Inconsistency Detection and Removal (Step 210)

Suppose that N={1,2}, T={{1}, {2}, {1,2}, 0}, s₁=0.99, s₂=0.99, s_1,2=0.90, and s_Ø=1. The constraints for the LP are given by:

s ₁ =x ₁₀ +x ₁₁ +a ₁ ⁺ −a ₁ ⁻=0.99

s ₂ =x ₀₁ +x ₁₁ +a ₂ ⁺ −a ₂ ⁻=0.99

s _1,2 =x ₁₁ +a _1,2 ⁺ −a _1,2 ⁻=0.90

s _Ø =x ₀₀ +x ₁₀ +x ₀₁ +x ₁₁=1

Minimizing the sum over all slack variables yields the following solution:

a₁⁺=a₁⁻=0

a₂⁺=a₂⁻=0

a_1,2⁺=0

a_1,2⁻=0.08

Although it may not be readily apparent, the constraint set T contains an inconsistency, because a_1,2⁻=0.08. Applying the resulting adjustment, the method for resolving inconsistencies (step 210) may obtain the set of consistent selectivities s₁*=0.99, s₂*=0.99, and s_1,2*=0.98.

Partitioning (Step 206)

Because solving the constrained optimization problem (e.g., at step 214), for example, using iterative scaling, has computational complexity that is exponential on the size of the predicate set P, e.g., complexity of O(|T|2^|P|), it may be desirable to avoid executing the iterative scaling algorithm on the full predicate set P. Instead, method 200 may compute the ME solution by partitioning P into several disjoint subsets, executing the iterative scaling computation on each subset independently, and using the independence assumption to combine selectivity estimates for predicates in different partitions. Partitioning can reduce the computational complexity from O(|T|2^|P|) to O(|T₁|2^|P₁^|+|T₂|2^|P₂^|+ . . . +|T_k|2^|P_k^|), where T₁, T₂, . . . , T_k and P₁, P₂, . . . , P_k form a partition of the predicate set P and can make the iterative scaling algorithm feasible even for extremely complex queries with large sets of predicates.

Method 200 can naturally partition the predicate set P under certain conditions, e.g. the partitioning condition described below; otherwise, method 200 may force a partition of the predicate set P or may force a further partition of the natural partition if there is a need to make the partition sizes smaller.

If, at step 206, method 200 can split N={1,2, . . . ,n} into nonempty disjoint subsets N₁, N₂, . . . , N_k, such that for each XεT it is the case that x⊂N_i for some iε{1,2, . . . ,k}, then the index set N of P is said to satisfy a partitioning condition. In that case, method 200 may partition P and T accordingly (or naturally partition P and T) by setting

P_i={p_j|jεN_i} and T_i={XεT|X⊂N_i}

for 1≦i≦k. (It may be observed that the T_i sets are not completely disjoint, because they all contain Ø.) In method 200, the ME solution for (P,T) can be obtained by first (e.g., using iterative scaling) computing the ME solutions for (P₁,T₁), (P₂,T₂), . . . , (P_k,T_k) and then using the independence assumption to combine selectivity estimates for predicates in different partitions; i.e., method 200 may compute

s_x=Π_{iε{1,2, . . . ,k}}s_x∩Nⁱ

for Xε2^N.

For example, when N={1,2,3,4} and T={{1}, {1,2}, {3}, {3,4}, Ø}, method 200 can take N₁={1,2}, N₂={3,4}, T₁={{1}, {1,2}, Ø}, and T₂={{3}, {3,4}, Ø} Method 200 can then, for example, compute s₂ by obtaining the ME solution for (P₁,T₁), compute s₄ by obtaining the ME solution for (P₂,T₂), and then compute s_2,4 using the independence assumption as s_2,4=s₂*s₄.

Partitioning can reduce the iterative scaling computation complexity from O(|T|2^|N|) to O(T₁2^|N1|+ . . . +T_k2^|Nk|), making iterative scaling feasible even for large sets of predicates N. For example, when N={1,2, . . . ,12}, |T|=25, k=4, |N_i|=3 and |T_i|==7, the use of partitioning can reduce the complexity by two orders of magnitude.

In practice, partitioning can reduce the computational complexity even more than is indicated above, by avoiding execution of the iterative scaling computation altogether for specified partitions (P_i,T_i). In the above example, for instance, method 200 can compute the selectivity s_1,2,4 as s_1,2,4=s_1,2*s₄; because s_1,2 is known a priori, method 200 need only run iterative scaling on (P₂,T₂) and not on (P₁,T₁). Similarly, if a partition contains only a few predicates, it may be possible to compute the desired ME selectivity analytically, without requiring iterative scaling. For example, suppose that P_i={p₁, p₂, p₃}, T_i={{1}, {2}, {3}, {1,2}, {2,3}, Ø}, and method 200 needs to compute s_1,2,3. It can be shown, for this example, that s_1,2,3=s_1,2*s_2,3/s₂, so that no iterative scaling computation is needed. In general, it may be possible to maintain a “library” of such identities, and method 200 may perform a quick syntactic analysis at selectivity-estimation time to see if any of the identities apply.

Forced partitioning (step 206)

In order to provide adequate real-time performance for computation of the constrained optimization problem (e.g., at step 214) using iterative scaling, for example, it may be desirable to ensure that each partition N_i of N, as described above, has a cardinality smaller than μ, where μ is a constant natural number greater than or equal to one that depends on the computer hardware and system load. For example, the constant μ may depend primarily on the CPU speed of the computer. On a single-user laptop using an Intel Pentium® III Mobile CPU 1133 MHz with 512 MB RAM, for example, a value for μ=8 has been found to provide computation time typically less than one second for iterative scaling computations.

It may not always be possible to partition N into subsets of cardinality less than μ for a given set T. For instance, if N={1,2,3,4,5,6} and T={{1}, {2}, {3}, {4}, {5}, {6}, {1,2}, {2,3}, {3,4}, {4,5}, {5,6}}, then N cannot be partitioned for μ=3 because for any partitioning of N into N₁ and N₂, there exists at least one XεT such that X∩N₁≠Ø and X∩N₂≠Ø. Thus, the independence assumption applied between any two partitions N₁ and N₂ in this example cannot result in the correct ME solution for N and T.

In such cases, method 200 may remove elements from T in order to force a partitioning in which |N_i|≦μ. However, this forced partitioning can impact the quality of the ME solution, since method 200 is discarding information. Ideally, a forced-partitioning method should remove those elements that have the least impact on the ME solution. Such a method, however, would be very computationally expensive, because it would have to consider the interaction of every constraint with every other constraint. Method 200 may use a pragmatic computational method (e.g., at step 206) for forced partitioning with complexity O(|T|log|T|) that ignores interactions and greedily removes elements from T with the goal of minimizing the impact on the ME solution, described as follows.

When generating partitions, method 200 may keep track of the cardinality c_i of each partition N_i. At the beginning, method 200 may start with |N| partitions, N_i={i} and c_i=1 for every partition. Method 200 then may iteratively merge partitions based on each element XεT, i. e,

$N_i=\{\begin{array}{cc}{\bigcup }_{j|X\subseteq N_j}N_j& \mathrm{if}i=\min \left(X\right);\\ Ø& \mathrm{otherwise},\end{array}$

which reduces the number of partitions, but increases c_i. Method 200 may only add an element XεT to the knowledge set T_i of partition N_i if afterwards c_i≦μ is still satisfied. If c_i≦μ is violated, method 200 may ignore X.

In order to have as little impact as possible on the overall ME solution, method 200 may avoid discarding those elements XεT that correspond to knowledge about the largest deviations from independence. Method 200 can achieve this goal by processing elements in descending order of Δ_X, where Δ_X=s_x/Π_iεXs_i. The quantity Δ_X measures the degree to which the corresponding s_X constraint in Equation 2 forces the ME solution away from independence. FIG. 5 summarizes the computation for forced partitioning. An efficient implementation could store T as a list sorted according to Δ_X and thus have a complexity of O(|T|log|T|).

Example: Forced Partitioning (Step 206)

Let N={1,2,3,4,5,6} and T={{1}, {2}, {3}, {4}, {5}, {6}, {1,2}, {2,3}, {3,4}, {4,5}, {5,6}}. Also suppose that

s₁=0.1, s₂=0.2, s₃=0.3, s₄=0.4, s₅=0.5, s₆=0.6ands_1,2=0.1, s_2,3=0.2, s_3,4=0.3, s_4,5=0.4, s_5,6=0.5.

It follows that Δ_1,2=5, Δ_2,3=3.33, Δ_3,4=2.5, Δ_4,5=2, and Δ_5,6=1.67. With μ=3, the forced partitioning method (step 206) therefore obtains k=2, N₁={1,2,3}, T₁={{1}, {2}, {3}, {1,2}, {2,3}}, N₂={4,5,6}, and T₂={{4}, {5}, {6}, {4,5}, {5,6}}. The element {3,4} has been dropped, to enable the partitioning of N into N₁ and N₂. Of all the elements that prevent partitions of size 3 from being constructed, the element {3,4} may have the smallest impact on the ME solution.

It should be understood, of course, that the foregoing relates to exemplary embodiments of the invention and that modifications may be made without departing from the spirit and scope of the invention as set forth in the following claims.

Claims

1. A computer-implemented method for a database, given a set of input selectivities {sX: XεT}, the method comprising: adjusting said input selectivities to obtain a mutually consistent set of selectivities; andminimizing said adjusting.

2. The method of claim 1, wherein: said given set of input selectivities is not mutually consistent; andsaid adjusted selectivities are mutually consistent and yield a set of satisfiable constraints for a constrained optimization problem.

3. The method of claim 2, wherein said constrained optimization problem is a maximum entropy (ME) constrained optimization problem for estimating selectivities of conjunctive predicates.

4. The method of claim 1, wherein: said adjusting step comprises associating two slack variables with each constraint of said constrained optimization problem, except for the constraint corresponding to an sØ; andsaid minimizing step comprises minimizing the total sum of all said slack variables.

5. The method of claim 1, further comprising: weighting a first pair of slack variables with a first weighting coefficient, said first pair of slack variables associated with a constraint for a selectivity sX;weighting a second pair of slack variables with a second weighting coefficient, said second pair of slack variables associated with a constraint for a selectivity sY, wherein: selectivity sX is more reliable than selectivity sY, andsaid first weighting coefficient is larger than said second weighting coefficient; andperforming said adjusting and minimizing steps with said weighting coefficients.

6. The method of claim 1, wherein: said minimizing step comprises solving a linear program (LP) having slack variables aX+; aX−, said LP including:

7. A database query optimizer, the optimizer receiving input of a knowledge set T for a predicate index set N and selectivities {sY: YεT} for predicate set P, the optimizer executing steps for: selecting, in response to N not satisfying a partitioning condition, XεT such that X has the smallest impact on a maximum entropy (ME) solution to a constrained optimization problem; andremoving the element X from T in order to force a partitioning of P and T.

8. The optimizer of claim 7, further comprising executing a step for: partitioning P and T accordingly in response to N satisfying a partitioning condition.

9. The optimizer of claim 7, further comprising executing steps for: choosing a constant μ:; andremoving elements from T in order to force a partitioning of P and T with partitions N1, N2, . . . , Nk in which |Ni|≦μ for each iε{1,2, . . . ,k}.

10. The optimizer of claim 7, wherein said selecting XεT further comprises selecting X in descending order of ΔX, where ΔX=sX/ΠiεXsi.

11. The optimizer of claim 7, wherein said forcing a partition further comprises: starting with |N|partitions, wherein Ni={i} and ci=1 for every partition;iteratively merging partitions based on each element XεT, so that

12. The optimizer of claim 8, wherein: said satisfied partitioning condition is that N={1,2, . . . ,n} can be split into nonempty disjoint subsets N1, N2, . . . , Nk, such that for each XεT it is the case that x⊂Ni for some iε{1,2, . . . ,k}; andsaid partitioning P and T accordingly comprises setting Pi={pj|jεNi} and Ti={XεT|X⊂Ni} for 1≦i≦k.

13. A computer program product comprising a computer useable medium including a computer readable program, wherein the computer readable program when executed on a computer causes the computer to: input a set of input selectivities {sX: XεT};form the |T| constraints ΣbεC(X)xb=sX, XεT; anddetect a zero atom b for which xb=0 in said constraints.

14. The computer program product of claim 13, wherein said step of detecting further comprises: detecting said zero atom using an iterative process.

15. The computer program product of claim 13, wherein said step of detecting further comprises: detecting said zero atom using an approximation process.

16. The computer program product of claim 13, wherein said step of detecting further comprises: setting an initial set A0 of candidate zero atoms to contain all of the atoms so that A0={0,1}n;in a first iteration, setting i=0 and solving the linear program (LP):

17. The computer program product of claim 13, wherein said step of detecting further comprises: rewriting the selectivity xb of each atom as the sum of two new variables: xb=vb+wb;solving the following LP: