Efficient hypothetical query evaluation in a database system

Abstract

A hypothetical query in a database system is transformed using algebraic equivalences involving explicit substitutions so as to produce one or more equivalent queries which can be evaluated more efficiently than the original hypothetical query. The hypothetical query may be of the form Q when {U} where Q is a relational algebra query and U is an update expression. The value assigned to the hypothetical query in a database state DB is the value that Q would return in the database state reached from DB by executing update U. One or more explicit substitutions are used to represent hypothetical database state changes, and algebraic equivalences involving the explicit substitutions are applied to the hypothetical query in order to generate at least one additional query which is equivalent to the hypothetical query. Several equivalent queries may be generated, and their estimated computation times compared, in order to select a particular equivalent query for direct evaluation. The use of explicit substitutions to represent hypothetical database state changes allows when constructs or other similar hypothetical programming constructs to be eliminated from the equivalent query. The evaluation process may be facilitated by configuring the original hypothetical query into an evaluable normal form, such that one or more hypothetical state expressions of the query correspond to explicit substitutions, and the hypothetical query does not utilize a composition operator. The selected equivalent query can be evaluated using a purely lazy evaluation strategy, or a hybrid lazy-eager evaluation strategy.

Description

FIELD OF THE INVENTION

The present invention relates generally to database management techniques, and more particularly to techniques for implementing, evaluating and otherwise processing hypothetical information queries in a database system.

BACKGROUND OF THE INVENTION

Many database systems are configured to support specialized types of hypothetical queries which are useful in application areas such as, for example, version management, decision support, active databases and integrity maintenance. Most existing approaches provide support for hypothetical queries only in specialized forms. Frameworks for expressing hypothetical queries for general-purpose usage have been discussed in S. Ghandeharizadeh, R. Hull and D. Jacobs, "Elevating Deltas to be First-Class Citizens in a Database Programming Language," ACM Trans. on Database Systems, 21(3): 370-426, 1996, and J. Woodfill and M. Stonebraker, "An Implementation of Hypothetical Relations," Proc. of Intl. Conf. on Very Large Databases, pp. 157-165, September 1983, both of which are incorporated by reference herein. For example, a simple hypothetical query as described in the above-cited S. Ghandeharizadeh et al. reference may be of the form Q when .eta., where Q is a conventional database query and .eta. denotes a hypothetical database state expression that may be a hypothetical update expression {U}, an explicit substitution of the form {Q.sub.1 /R.sub.1, . . . , Q.sub.n /R.sub.n }, or any other suitable hypothetical-state expression. Consider a department store database which maintains the following two relations in the form of stored tables: Depts[dept.sub.-- name, mgr.sub.-- name], and Products[ prod.sub.-- name, price, color, dept.sub.-- name]. The relation Depts stores identifiers of departments (dept.sub.-- name) and their managers (mgr.sub.-- name), while the relation Products stores the name (prod.sub.-- name), price, color and selling department (dept.sub.-- name) of each product. An example of a simple hypothetical query to this database may be expressed informally as follows: [What managers sell at least one product with price.gtoreq.100] when [Move all green products to Garden department]. More complex hypothetical queries may include multiple when constructs or multiple nested when constructs.

Well-known direct evaluation techniques are often used to process the non-hypothetical conventional query Q. A direct evaluation of Q is an evaluation in which the actual operators mentioned in Q are performed directly. It is known that given a query Q, there may be some other query Q' which is equivalent to Q in that Q' will give the same answer as Q, but where the direct evaluation of Q' is much faster than the direct evaluation of Q. Using this fact, conventional direct evaluation of a query Q may be more efficiently implemented using an optimization step and an evaluation step. The optimization step includes generating a set of queries Q' that are equivalent to Q using "rewriting rules" based on algebraic equivalences, estimating the amount of time it would take to directly evaluate the various queries Q', and selecting a particular query Q' that appears to be fastest to directly evaluate. The evaluation step involves directly evaluating the selected equivalent query Q'. These techniques for evaluation of non-hypothetical queries are generally referred to as "lazy" evaluation techniques.

Although the efficient evaluation of non-hypothetical queries by the above-described lazy evaluation and other techniques is well developed, little work has been done in the area of efficient evaluation of hypothetical queries. More particularly, lazy evaluation techniques have generally not been applied to evaluation of hypothetical queries. Instead, existing techniques for evaluating hypothetical queries are generally "eager" with regard to the evaluation of the hypothetical states. An eager evaluation approach, when given a query of the form Q when .eta., generally computes and materializes in the database a representation of the hypothetical database state .eta., or a representation of the net change, or delta, called for by .eta.. The conventional query Q is then evaluated using a standard technique such as the above-described direct evaluation, and the result is "filtered" against the materialized representation of .eta.. Exemplary eager evaluation techniques are used in the Heraclitus language described in the above-cited S. Ghandeharizadeh et al. reference, and M. Doherty, R. Hull and M. Rupawalla, "Structures for Manipulating Proposed Updates in Object-Oriented Databases," Proc. ACM SIGMOD Symp. on the Management of Data, pp. 306-317, 1996, which is also incorporated by reference herein. Eager evaluation techniques are also used in conventional version management systems, and in implementations of hypothetical relations as described in the above-cited J. Woodfill and M. Stonebraker reference.

A conventional optimizer may be used in conjunction with eager evaluation to optimize a hypothetical query Q when .eta. in the manner previously described, that is, to find a Q' equivalent to Q such that direct evaluation of Q' filtered by .eta. is faster than direct evaluation of Q filtered by .eta.. Other techniques have been developed for computing and representing a hypothetical state .eta. quickly, and in manner which attempts to ensure that the filtering operation is relatively fast. Existing eager evaluation techniques thus attempt separate optimizations of the conventional query Q and the hypothetical database state .eta., but generally assume that the when construct will be evaluated directly through the above-noted filtering mechanism. The existing eager techniques thereby fail to provide sufficiently fast evaluation of hypothetical queries in many important applications.

As noted above, lazy evaluation strategies, although utilized in other database management applications such as in evaluating weakest preconditions, have generally not been applied to evaluation of hypothetical queries. Evaluation of weakest preconditions is described in greater detail in, for example, E. W. Dijkstra, "Guarded Commands, Nondeterminacy and Formal Derivations of Programs," Comm. ACM, 18:453-457, 1975, E. W. Dijkstra, "A Discipline of Programming," Prentice-Hall, 1976, D. Gries, "The Science of Programming," Springer-Verlag, 1981, X. Qian, "An Axiom System of Database Transactions," Information Processing Letters, 36:183-189, 1990 and X. Qian, "The Expressive Power of the Bounded-Iteration Construct," Acta Informatica, 28(7):631-656, October 1991, all of which are incorporated by reference herein. Other lazy evaluation techniques have been developed in logic programming and datalog applications, as described in, for example, D. M. Gabbay and U. Reyle, "N-Prolog: An Extension of Prolog with Hypothetical Implications," Journal of Logic Programming, 1(4):319-355, 1984 and 2(4):251-283, 1985, A. J. Bonner, "Hypothetical Datalog: Complexity and Expressibility," Theoretical Computer Science, 76:3-51, 1990, A. J. Bonner, "The Logical Semantics of Hypothetical Rulebases with Deletion," Journal of Logic Programming, 1995, all of which are incorporated by reference herein. However, these and other lazy or hybrid lazy-eager evaluation techniques, although not requiring materialization of hypothetical states, fail to provide a sufficiently general framework for optimizing hypothetical query evaluation in a variety of applications.

It is therefore apparent that a need exists for improved techniques for evaluating hypothetical database queries, such that the hypothetical queries can be processed more efficiently than is possible using the existing techniques described above.

SUMMARY OF THE INVENTION

In accordance with the principles of the invention, a hypothetical query Q when .eta. in a database system is transformed using explicit substitutions to generate one or more equivalent queries which can be evaluated more efficiently than the original hypothetical query. The equivalent queries may be developed by applying to the original hypothetical query algebraic equivalences involving explicit substitutions where each of the substitutions represents a hypothetical database state change. The use of algebraic equivalences involving explicit substitutions permits when constructs to be moved within hypothetical queries, or removed altogether, while equivalence is preserved. Thus, some of the resulting equivalent queries may be hypothetical, including one or more when constructs, while others may include no when constructs and are not hypothetical. If the when construct is present in the selected equivalent query, then filtering may be used in the direct evaluation process. Advantageously, in contrast to conventional hypothetical query evaluation techniques, the present invention provides an approach in which the when construct generally does not have to be evaluated directly nor eagerly.

The notation .eta. denotes a hypothetical database state expression that may be an hypothetical update expression {U}, an explicit substitution of the form {Q.sub.1 /R.sub.1, . . . , Q.sub.n /R.sub.n }, or any other suitable hypothetical-state expression. An exemplary embodiment of the invention applies one or more algebraic equivalences involving explicit substitutions to a given hypothetical query of the form Q when {U} where Q is a relational algebra query and U is an update expression. The value assigned to the hypothetical query in a database state DB is the value that Q would return in the state resulting from executing U on DB. The hypothetical query may include multiple nested occurrences of the when construct, and the update U may involve a sequence of several atomic updates which may include multiple nested occurrences of the when construct. Each of the explicit substitutions represents a hypothetical database state change, and is applied to hypothetical query Q when {U} in order to generate at least one additional query which is equivalent to the hypothetical query. Several equivalent queries may be generated, and their estimated computation times compared, such that a particular equivalent query can be selected for direct evaluation. The use of explicit substitutions to represent hypothetical database states allows when constructs or other hypothetical programming constructs to be effectively eliminated from the equivalent query. The evaluation process may be facilitated by configuring the original hypothetical query in a normal form, such that each hypothetical state expression of the query is specified by an explicit substitution. The selected equivalent query can then be evaluated using a purely lazy evaluation strategy, or a hybrid lazy-eager evaluation strategy, as appropriate for a given application. The present invention thus provides substantially greater flexibility than conventional hypothetical query evaluation techniques which apply only eager evaluation strategies.

These and other features and advantages of the present invention will become more apparent from the accompanying drawings and the following detailed description.

BRIEF DESCRIPTION OF THE DRAWINGS

FIGS. 1A and 1B show exemplary database systems in which the hypothetical query evaluation techniques of the present invention may be implemented;

FIGS. 2A through 2G show a family of equivalences in an exemplary set of hypothetical query language (HQL) expressions in accordance with the invention;

FIGS. 3A and 3B show exemplary HQL functions free(Q) and dom(Q), respectively, in accordance with an illustrative embodiment of the invention;

FIG. 4 shows a syntax tree formed in accordance with the invention for a query in evaluable normal form (ENF); and

FIGS. 5, 6 and 7 show exemplary HQL functions filter.sub.1, filter.sub.2 and filter.sub.3, respectively, used in exemplary query evaluation algorithms in accordance with the invention.

DETAILED DESCRIPTION OF THE INVENTION

The present invention will be illustrated below in conjunction with exemplary hypothetical database queries in the context of a relational database model. It should be understood, however, that the invention is not limited to use with any particular type of database, database configuration or database model, but is instead more generally applicable to any database application in which it is desirable to provide efficient processing of hypothetical queries. For example, the techniques of the invention could be readily adapted for use with an object-oriented database model. The term "data item" as used herein refers to a stored data element, such as an entry in a record, table or other relation. The term "database system" should be understood to include any type of system in which data items are stored or processed in one or more sites. The term "when construct" as used herein should be understood to include not only the illustrative when construct described herein, but also other types of programming language constructs which are used to support hypothetical query capability, such as, for example, similar constructs used in "what if" queries. The term "hypothetical database state change" as used herein refers to an expression that specifies a possible change or modification to a database state, to a part of a database state, or to the values of one or more objects in a database state. This expression might arise in the context of a hypothetical query, or in the context of decision support involving hypothetical states of a database or values of objects in the database, or in the context of the creation of a version of a full or partial database state or collection of objects in a database state, or in the context of accessing a previously occurring state of the database or values of a collection of objects in the database state, or in the execution of rules or analogous processing as in an active database system. An "eager" strategy for evaluating a hypothetical query refers to a strategy in which all hypothetical state representations are either partially or completely materialized, and query evaluation is filtered through those representations. A "lazy" strategy for evaluating a hypothetical query refers generally to a strategy in which the hypothetical query is first reformulated into an equivalent non-hypothetical query, and then the equivalent query is evaluated in place of the original hypothetical query. A "hybrid lazy-eager" strategy is one in which the hypothetical query is reformulated into a different but equivalent hypothetical query involving at least one when construct, and then the equivalent hypothetical query is evaluated using the eager strategy.

FIG. 1A shows an exemplary database system 10 in which the hypothetical query evaluation of the present invention may be implemented. The system 10 includes a processor 12 connected to communicate with a primary memory 14 and a secondary memory 16. The processor 12 can be implemented as a personal, micro or mainframe computer, a workstation, a microprocessor, a central processing unit, an application-specific integrated circuit (ASIC) or other digital data processor, as well as various portions or combinations thereof. The primary memory 14 is typically an electronic memory associated with the processor 12. The secondary memory 16 may be an external magnetic or optical disk memory, a magnetic tape memory, or other suitable data storage device. The primary and secondary memory of the system 10 may represent storage locations of data warehouses, data marts or other large databases having storage capacities of up to a few terabytes, smaller localized database memories, or even memories associated with desktop or portable computers. As will be described in detail below, the hypothetical query evaluation techniques of the present invention may be implemented as a software program stored in portions of one or both of the memories 14 and 16, and executed by processor 12. The software program may be configured to implement, evaluate and otherwise process hypothetical queries to a database or databases associated with one or both of the memories 14 and 16. The system 10 may also utilize processor 12 and memories 14, 16 to provide conventional query processing capabilities such as those described in, for example, H. F. Korth and A. Silberschatz, "Database System Concepts," Second Edition, McGraw-Hill, New York, 1991, and R. Elmasri and S. B. Navathe, "Fundamentals of Database Systems," Ch. 18, Benjamin/Cummings, Redwood City, Calif., 1989, both of which are incorporated by reference herein.

FIG. 1B shows an exemplary distributed database system 20 in which the hypothetical query evaluation of the present invention may be implemented. The system 20 includes N physical sites 22-i, i=1, 2, . . . N connected by a network 24. The network 24 may be a local area network, a metropolitan area network, a wide area network, a global data communications network such as the Internet, a private "intranet" network or any other suitable data communication medium. The sites 22-i may be centralized database systems such as data warehouses or data marts, remote customer sites such as automatic teller machines or desktop personal computers, portable computers or other mobile computing devices, or any other type of data processors. Each of the N sites 22-i includes a processor 26-i and a memory 28-i. The memory 28-i may be, for example, a large centralized database system, or a memory of a desktop or portable personal, depending upon the nature of the corresponding physical site 22-i. The processors 26-i may each run a conventional local database management system. Like the processor 12 of FIG. 1A, the processors 26-i can be implemented as personal, micro or mainframe computers, workstations, microprocessors, central processing units, ASICs or other digital data processors, as well as various portions or combinations thereof. The memories 28-i may utilize electronic, magnetic or optical storage media, or various combinations thereof, and as previously noted may represent storage locations of data warehouses or data marts having storage capacities of up to a few terabytes, or memories of desktop or portable computers. The storage and retrieval of data items from a memory 28-i of a given site 22-i is controlled by the corresponding processor 26-i using the local database management system. The sites 22-i of system 20 may each run multiple transactions, such as read and write transactions, during which data items stored at other sites are accessed and processed. One or more of the sites 22-i of FIG. 1B may execute a software program which implements, evaluates and otherwise processes hypothetical queries to a database or databases associated with one or more of the memories 28-i.

Hypothetical query evaluation in accordance with the invention provides additional optimization beyond that provided by conventional techniques. More particularly, the invention provides a framework that, given an exemplary hypothetical query Q when .eta., can be used to find many queries that are equivalent to Q when .eta.. The notation .eta. represents a hypothetical database state expression, such as an update expression {U}, an explicit substitution of the form {Q.sub.1 /R.sub.1, . . . , Q.sub.n /R.sub.n } which specifies that relation R.sub.i is to be replaced by the query Q.sub.i, an expression constructed by an operation that composes substitutions, or other suitable hypothetical state expression. Some of the resulting equivalent queries may also be hypothetical, including one or more when constructs, while others may include no when constructs. The framework to be described below includes mechanisms for moving when constructs within hypothetical queries, while preserving equivalence. In contrast to conventional hypothetical query techniques, the evaluation framework of the present invention provides an approach in which the when construct generally does not have to be evaluated directly.

The present invention is based in part on the discovery that hypothetical database state expressions can be specified using explicit substitutions. As noted above, a hypothetical database state expression .eta. may be {U} where U is an update expression. An exemplary hypothetical state expression .eta. corresponding to the update `[Move all green products to Garden dept]` might be: ##EQU1## In the above hypothetical state, a certain set of tuples has been inserted into Products and another set of tuples has been deleted from Products. The specific inserted set is essentially the same set of tuples of green products that are already in Products, but using the value Garden in the dept.sub.-- name column. The deleted set is the set of tuples of green products that do not have Garden in their dept.sub.-- name column. An explicit substitution .epsilon. that is equivalent to the hypothetical state .eta. given above is: ##EQU2## If X is defined as the query: ##EQU3## the hypothetical state expression .eta. may be written as {X/Products}. The hypothetical state expression .eta. thus represents a hypothetical state in which the relation Products is replaced by the value that query X has in the current database state. The explicit substitution .epsilon. indicates that the value of one relation is to be changed. In general, an explicit substitution in accordance with the invention may indicate that the values of several relations are to be changed.

The present invention provides an algorithm for transforming hypothetical state expressions, such as {U} where U is an update expression, into equivalent explicit substitutions. The update expression U may be expressed in a language that mimics the update expressions of SQL, the standard query language for the well-known relational model. Assume that Q when {U} is a hypothetical query, where Q is essentially a relational algebra query which may include nested when constructs. The value assigned to this hypothetical query in a given database state DB is the value that Q would return in the state that is reached from DB by executing the update U. Such queries are fundamental to many applications that involve hypothetical database states. The present invention recognizes that if .epsilon. is an explicit substitution equivalent to {U}, then a new query Q when .epsilon. is equivalent to the original hypothetical query Q when {U}. The new query can be further processed using the techniques of the present invention to generate equivalent queries in which the when construct is possibly eliminated, or is "moved" to other parts of the query. The illustration of elimination of an explicit substitution will be described in conjunction with the exemplary hypothetical query: ##EQU4## This hypothetical query may be written formally as: ##EQU5## where is the join operator. The hypothetical state expression can then be transformed into an explicit substitution, in the manner previously described, to yield: ##EQU6## It is now possible to "push the explicit substitution" into query Q through the join operation. In this simple example, the "pushing" is achieved by replacing the relation name Products by the query X, thus obtaining: ##EQU7## The above query is equivalent to the hypothetical query Q when {U}. It includes no when constructs, and is a conventional non-hypothetical query. Furthermore, it should be noted that a conventional "pushing selection through join" transformation can be applied to the join inside the query X. More generally, rules for rewriting queries involving when in accordance with the invention can be used in conjunction with known rules for rewriting conventional queries.

The preceding example is illustrative of a process of efficient evaluation of hypothetical queries in accordance with the present invention. Like the prior art direct evaluation techniques described above, an evaluation process in accordance with the present invention may include an optimization step and an evaluation step. The process begins with a hypothetical query, that may have one or more when constructs, properly nested. The optimization step in the inventive process involves generating a set of queries that are equivalent to the hypothetical query. The set of queries are generated by manipulating subexpressions of the original hypothetical query using a set of equivalences, to be described in conjunction with FIGS. 2A-2G below, either to move the when constructs to other parts of a query, or to remove the when constructs altogether, or to replace hypothetical state change expressions by explicit substitutions, or to transform, combine or eliminate one or more explicit substitutions. The equivalences of the present invention may be used in conjunction with well-known equivalences which have been used for conventional non-hypothetical queries. The resulting equivalent queries may be in an Evaluable Normal Form (ENF), to be described in greater detail below, such that no query utilizes a composition operator and each hypothetical state expression is an explicit substitution. The optimization step also involves estimating the amount of time it would take to evaluate directly each of the queries in the set of equivalent queries, and selecting a particular equivalent query that appears to be the fastest to evaluate directly. The evaluation step involves directly evaluating the selected equivalent query in a manner to be described in greater detail below. The present invention provides improved performance through the use of explicit substitutions to represent hypothetical database states, based on a number of equivalences that exploit the explicit substitutions for the purpose of query optimization.

As noted above, an important aspect of the present invention is the ability to represent hypothetical database states and hypothetical state change expressions as explicit substitutions. An illustrative language of hypothetical queries will be described below. It will be shown that, in accordance with the invention, substitutions can be treated as updates, updates can be treated as substitutions, and substantially all instances of the when construct can be eliminated or otherwise removed from direct evaluation by application of explicit substitutions. Explicit substitutions are described in greater detail in M. Abadi, L. Cardelli, P. Curien and J. Levy, "Explicit Substitutions," Journal of Functional Programming, 1(4):375-416, 1991, G. Dowek, T. Hardin and C. Kirchner, "Higher-Order Unification via Explicit Substitutions," IEEE Symposium on Logic in Computer Science, pp. 366-374, 1995, and P. Lescanne, "From .lambda..sigma. to .lambda..nu.--A Journey Through Calculi of Explicit Substitutions," ACM SIGPLAN-SIGACT Symposium on Principles of Programming Languages (POPL), pp. 60-69, 1994, all of which are incorporated by reference herein. In these references, explicit substitutions are described in conjunction with programming and the lambda base calculus. It is believed that explicit substitutions, although generally well-known per se, have not heretofore been applied to optimizing the evaluation of hypothetical queries.

For purposes of illustrating the invention in detail, a specific formalism will now be introduced. This formalism includes an illustrative language called "HQL" for hypothetical queries. HQL includes a specific conventional query language, a specific update language, and a specific language for hypothetical queries. A relational database schema is defined herein as a collection of relation names Z={Z.sub.1, . . . , Z.sub.Z }, each of which includes one or more attributes. An instance of a relation name contains zero or more records or tuples, where each tuple includes one or more stored data items or entries for each attribute. In addition, the relation names each have a fixed "arity," where arity refers generally to the number of attributes associated with a given relation. For example, in a database in which each relation includes a number of table entries, the arity of the relation name may be the number of column headings in the table. It will be assumed without limitation that a particular database schema Z is fixed. The variables R, R.sub.j, S and S.sub.j will be used to range over the relation names of Z. The language to be defined will include queries Q, updates U and hypothetical queries H. Initially, a subset of the language HQL will be defined in order to introduce various aspects of the invention. The full language HQL will then be presented at a point noted below. Hypothetical queries H .di-elect cons. H are defined by the grammar:

H ::=Q when {U} In any given database state, this construct represents the value that Q would have in the state reached by executing update U. Relational algebra queries Q .di-elect cons. RA are generated by the grammar: ##EQU8## For clarity of illustration, details such as condition expressions in selection and join, and column expressions in projections, will not be shown. The usual typing rules will be assumed with regard to arities of query expressions. Updates U .di-elect cons. U are generated by the grammar: ##EQU9## The set of all relations of arbitrary arity is denoted by R. A database state is a function DB mapping every relation name S .di-elect cons. Z to a relation DB(S) .di-elect cons. R of the appropriate arity. The set of all database states is denoted by DB. Each syntactic category has an associated semantic function, ##EQU10## The semantic function Q is defined in a conventional manner. The function U is defined recursively as: ##EQU11## The notation DB[R.rarw.] denotes the database state DB' such that DB'(R)=V and DB'(S)=DB(S) when R.noteq.S. In practice, if a database is currently in state DB and update expression U is executed, then the database will move to state U (DB). The semantics of hypothetical queries are defined as follows: ##EQU12##

As noted previously, the query Q when {U} can be simplified using substitutions. A partial function .mu.: Z.rarw.RA is called a substitution if for every S .di-elect cons. dom(.mu.) the arity of S is equal to the arity of .mu.(S). The set of all substitutions over RA is denoted by .SIGMA.(RA). The notation .mu.={Q.sub.1 /S.sub.1, . . . , Q.sub.j /S.sub.j } is used to denote the substitution .mu. where dom(.mu.)={S.sub.1, . . . S.sub.f } and .mu.(S.sub.i)=Q.sub.i. Given a substitution .mu. and a query Q, the function sub(Q, .mu.) denotes the query that results from replacing every occurrence of a name S in Q with the query .mu.(S), for each S .di-elect cons. dom(.mu.). For any substitution .mu., the query sub(Q, .mu.) is called a substitution instance of Q. For example, if .mu.={(S-R)/R, .sigma..sub.q (R)/S} and Q=(.pi..sub.A (R.times.S)).orgate.V, then sub(Q, .mu.)=(.pi..sub.A ((S-R).times..sigma..sub.q (R))).orgate.V

Given substitutions .mu..sub.1 and .mu..sub.2, the composition of .mu..sub.1 and .mu..sub.2, denoted .mu..sub.1 # .mu..sub.2, is the unique substitution .mu..sub.3 such that dom(.mu..sub.3)=dom(.mu..sub.1).orgate. dom(.mu..sub.2), and for any S .di-elect cons. dom(.mu..sub.3), ##EQU13## The condition that dom(.mu..sub.3)=dom(.mu..sub.1) .orgate. dom(.mu..sub.2) ensures that .mu..sub.1 # .mu..sub.2 is unique as a substitution. Without this condition, the substitution could be "padded out" with bindings of the form S/S. It can be shown that for each substitution .mu..sub.1, .mu..sub.2 and .mu..sub.3 and each query Q,

sub(Q, .mu..sub.1 # .mu..sub.2)=sub(sub(Q, .mu..sub.2), .mu..sub.1), and .mu..sub.1 # (.mu..sub.2 # .mu..sub.3)=(.mu..sub.1 # .mu..sub.2) #.mu..sub.3. For example, if .mu..sub.1 ={(S-R)/R, .sigma..sub.q (R)/S} and .mu..sub.2 =(.pi..sub.B (R T))/S, .sigma..sub.p (S)/V, then .mu..sub.1 # .mu..sub.2 ={(S-R)/R, (.pi..sub.B ((S-R) T))/S, .sigma..sub.p (.sigma..sub.q (R)/V}.

The substitution .mu.={Q.sub.1 /S.sub.1, . . . , Q.sub.j /S.sub.j } can also be viewed as an "algebraic" representation of the update that simultaneously replaces table S.sub.i with the value of query Q.sub.i. In other words, the update represented by .mu. executes the j operations S.sub.i :=Q.sub.i in parallel. To arrive at the value of sub(Q, .mu.) in state DB, one can therefore either evaluate sub(Q, .mu.) in DB or can execute the abstract update represented by .mu. and then evaluate Q in the resulting state. A function apply(DB, .mu.) can be defined as the database state DB' such that: ##EQU14## For every database state DB, ##EQU15## When substitutions are viewed as updates, then composition corresponds to sequential execution. For example, if .mu..sub.1 and .mu..sub.2 are substitutions, then for every database state DB,

apply(DB, .mu..sub.1 # .mu..sub.2)=apply(apply(DB, .mu..sub.1), .mu..sub.2). It should be noted that when substitutions are viewed as updates, the composition operator corresponds to composing update functions in the opposite order relative to that in which the corresponding substitution functions are applied.

Updates may also be transformed into substitutions in accordance with the invention. Given an update U, a substitution slice(U) is defined that has the same effect as U when it is treated as an "abstract" update. The substitution slice(U) is constructed using an operation which is related to program slicing as described in S. Horwitz and T. Reps, "The Use of Program Dependence Graphs in Software Engineering," Proceedings of the 14.sup.th International Conference on Software Engineering, 1992, which is incorporated by reference herein. If R .di-elect cons. dom(slice(U)), the query Q=(slice(U))(R) may be thought of as the "slice" of U that is relevant to R. In other words, as far as R is concerned, the update U has the same effect as executing R :=Q. The substitution slice(U) is defined recursively as:

slice (ins(R, Q))={(R .orgate. Q)/R} slice (del(R, Q))={(R-Z)/R} slice((U.sub.1 ; U.sub.2))=slice(U.sub.1 # slice(U.sub.2). For example, if U=(ins(R, Q.sub.1); del(S, .sigma..sub.p (R))), then: ##EQU16## From the foregoing, it can be seen that for each update U and each database state DB, apply(DB, slice(U))=U (DB). A hypothetical query Q when {U} can therefore be evaluated in the current state since it is equivalent to a substitution instance of Q. For each database state the RA query sub(Q, slice(U)) has the same value as the hypothetical query Q when {U}. That is, for each state DB, ##EQU17## For example, if U=(ins(R, Q.sub.1); del(S, .sigma..sub.p p(R))) as in the previous example, and Q=.pi..sub.A (S) V, then evaluating Q when {U} will be equivalent to evaluating: ##EQU18## Unlike the original hypothetical query Q when {U}, the above equivalent query can be evaluated using standard techniques.

The foregoing description demonstrated: (1) how substitutions can be treated as updates, (2) how updates can be expressed as substitutions, and (3) how hypothetical queries can be expressed as substitution instances. A number of syntactic constructs will now be introduced to define the full illustrative language HQL to represent substitutions and the application of suspended substitutions. The HQL will include a grammar for hypothetical state expressions .eta. and for explicit substitutions .epsilon.. The conventional grammar of RA queries is extended to RA.sup.hyp queries as follows: ##EQU19## That is, hypothetical queries can occur at any nesting level within a query. Hypothetical-state expressions .eta. .di-elect cons. HS are defined by the rule: ##EQU20## while explicit substitutions .epsilon. .di-elect cons. E are defined by the grammar:

.epsilon. ::={Q.sub.1 /S.sub.1, . . . , Q.sub.j /S.sub.j 56 j.gtoreq.0 where the Q.sub.i are RA.sup.hyp queries and the S.sub.i are distinct names from Z. The direct semantics of the illustrative HQL are defined by the functions: ##EQU21## The function Q is obtained by extending the semantic function defined previously and is given by: ##EQU22## The function .eta. (DB) is defined as: ##EQU23## This reflects the direct computational interpretation of a hypothetical-state expression. It will now be shown that any .eta. can be transformed into an equivalent abstract substitution that uses queries from RA, and that any hypothetical query in RA.sup.hyp can be transformed into an equivalent query in RA, that is, into an equivalent query having no when constructs.

A reduce function red(.eta.) is defined that maps any partial state expression .eta. to an abstract substitution red(.eta.) .di-elect cons. .SIGMA.(RA). In addition, a function red(Q) is defined that transforms any Q .di-elect cons. RA.sup.hyp into a query red(Q) .di-elect cons. RA. These functions are defined in a mutually recursive manner as follows:

red(. . . , Q.sub.j /S.sub.j, . . . )={ . . . , red(Q.sub.j)S.sub.j, . . .} red({U})=slice(U) red(.eta..sub.1 # .eta..sub.2)=red(.eta..sub.1) # red(.eta..sub.2) red(R)=R red({t})={t} red(u.sub.-- op(Q))=u.sub.-- op(red(Q)) red(Q.sub.1 b.sub.-- op Q.sub.2)=(red(Q.sub.1)) b.sub.-- op (red(Q.sub.2)) red(Q when .eta.)=sub(red(Q), red(.eta.)) where u.sub.-- op denotes any unary algebraic operator, and b.sub.-- op denotes any binary algebraic operator. This transformation makes explicit the relationship between meta-level and object-level constructs. It illustrates that hypothetical state expressions can be viewed as substitutions and that in a hypothetical expression `Q when .eta.` the expression `when .eta.` can be viewed as a suspended substitution. This is justified by the fact that, in general, if Q .di-elect cons. RA.sup.hyp, then red(Q) .di-elect cons. RA, and for each database state DB, each query Q .di-elect cons. RA.sup.hyp, and each hypothetical-state expression .eta., ##EQU24##

The reduction semantics presented above provide one type of strategy for evaluating HQL queries. That is, a query Q .di-elect cons. RA.sup.hyp can be reduced to the query red(Q) .di-elect cons. RA. This allows hypothetical queries to be optimized using standard techniques developed for RA. The above-described HQL can thus be implemented on top of standard relational database systems. A more general framework will now be developed for optimizing and evaluating hypothetical queries, that incorporates both eager and lazy evaluation of hypothetical state expressions. A central component of this framework is an equational theory for HQL which is a combination of (1) a conventional equational theory for the relational algebra, (2) an equational theory of explicit substitutions, and (3) a set of equations that relate hypothetical update expressions of the form {U} to explicit substitutions. The equational theory will be described in conjunction with FIGS. 2A through 2G. Another aspect of the optimization strategy is referred to herein as Evaluable Normal Form (ENF), and will be described in greater detail below. In general, an arbitrary HQL query Q can be converted into one or more equivalent ENF queries Q' using a set of rewriting rules based on the equational theory. The syntax tree of an ENF query Q' can be used to guide a systematic execution of the operators in Q'. Each explicit substitution present in Q' will be materialized during this execution, and subqueries within the scope of the explicit substitution will be "filtered" against that materialization. Thus, for a given HQL query Q, the choice of an equivalent ENF query Q' is in effect the choice of how eager or lazy the evaluation of Q will be. The concept of "explicit substitution value" or "xsub-value," a physical value that can be associated with an explicit substitution, will be described below, along with exemplary operators involving xsub-values. Two illustrative algorithms for evaluating ENF queries will also be described, both based on the use of xsub-values. The first performs a systematic depth-first traversal of an ENF syntax tree, while the second permits some clustered evaluation of individual algebraic operations, such as combining a join operation with select and project operations immediately above or below it. A generalization of one of the query evaluation algorithms to delta values will also be described. The generalization is useful for situations in which the hypothetical updates in an HQL query Q change only a small portion of the data, such that it is not efficient to compute the full value of the associated explicit substitutions.

FIGS. 2A through 2G illustrate an exemplary family of equivalences that can be used for rewriting HQL queries in accordance with the invention. Some of the equivalences are based on properties of explicit substitutions, while others are based on interaction of the when construct with relational algebra operators. Additional details regarding explicit substitutions suitable for use with the present invention may be found in the above-cited M. Abadi et al., G. Dowek et al. and P. Lescanne references. The family of equivalences involving constructs from HQL is referred to as EQUIV.sub.when, and includes "push-when-into-algebra-expressions" in FIG. 2A, "convert-to-explicit-substitutions" in FIG. 2B, "replace-nested-when" in FIG. 2C, "compute-composition" in FIG. 2D, "substitution-simplification" in FIG. 2E, "associativity" in FIG. 2F, and "commute-hypotheticals" in FIG. 2G. In FIGS. 2A through 2G, u.sub.-- op and b.sub.-- op denote arbitrary unary and binary relational algebra operators, respectively. A number of definitions are needed for the "substitution-simplification" and "commute-hypotheticals" rules of FIGS. 2E and 2G, respectively. If .epsilon. is an explicit substitution and R a relation name, then .epsilon..vertline..sub.-R denotes the explicit substitution obtained from .epsilon. by deleting the binding with a right-side `R`. FIGS. 3A and 3B show the mutually recursive definition of HQL functions free and dom, respectively. The function free(E) is the set of relation names occurring "free" in E. For hypothetical-state and update expressions E, dom(E) is the set of relation names that are "defined" by E. In essence, these functions articulate scoping rules for the when construct. It can be verified that the equations of EQUIV.sub.when exhibit the property of soundness. Soundness generally continues to hold if equations for the relational algebra are incorporated into the system.

The above-noted evaluable normal form (ENF) for HQL queries will now be described in more detail, along with a family of syntax trees that are used to represent queries in this form. An HQL query is in ENF if it does not use composition (#) or expressions of the form {U}. This means that all hypothetical-state expressions are in the form of explicit substitutions. Note that for an explicit substitution of form { . . . , Q/R, . . . }, the when construct may occur within Q. Specialized syntax trees may be used to represent ENF queries. Each node v of the syntax tree for an ENF query has a label, denoted as label(v), that is either a relational operator, or a relation name, or `when`, or `{ }`. These trees differ from conventional relational algebra trees in that: (1) the when construct may be present, as an infix binary operator, and (2) an explicit substitution {Q.sub.1 /R.sub.1, . . . , Q.sub.n /R.sub.n } is represented as follows: the parent node is labeled by { }, and has n children. For each i there is a child corresponding to query Q.sub.i, and the edge to that child has label R.sub.i.

FIG. 4 shows an example of a syntax tree, formed in accordance with the above description, for the query: ((Q.sub.0 (R, S, T) when {Q.sub.1 (R, S, T)} when {Q.sub.2 (R, T)/R, Q.sub.3 (R, S, T)/T})/S, Q.sub.4 (S, T)/T}) when {Q.sub.5 (R, T)/R, Q.sub.6 (S, T)/S}) (R .orgate. (Q.sub.7 (S, R, T) when {Q.sub.8 (R, S)/S, Q.sub.9 (R, S, T)/T})). The subqueries Q.sub.0, . . . , Q.sub.9 in this example are assumed to be from relational algebra. It should be noted that the above-described ENF is exemplary only. Numerous variants of this exemplary ENF may be used in accordance with the present invention. One possible variant, referred to as mod-ENF, will be described in detail below.

Evaluation of an ENF query generally involves the construction of relations, and of physical values corresponding to explicit substitutions. The concept of "explicit substitution value" will now be described, along with algebraic operators that use the explicit substitution value. More particularly, an illustrative equation is presented that describes how explicit substitution values should be combined when evaluating ENF queries with nested when constructs. In general, an ENF query is typically evaluated in the context of a fixed database state DB. As noted above, the semantics of an explicit substitution .epsilon. is a function from DB to DB. Thus, the value of .epsilon.(DB) is a full database state. In practice, if a relation name R .di-elect cons. Z is not in dom(.epsilon.) there is generally no point in replicating the value of DB(R) when constructing the value of .epsilon.(DB). An "explicit substitution value," abbreviated herein as "xsub-value," is a partial function E that maps relation names in Z into relations, where relation E(R) has the same arity as relation name R for each R .di-elect cons. dom(E). An xsub-value E may be denoted as a set E={J.sub.1 /R.sub.1, . . . J.sub.n /R.sub.n } of binding pairs, where each J.sub.i is a physical relation. The family of xsub-values is denoted by ESV.

A basic operator involving xsub-values is an apply operator that maps database states and xsub-values to database states. This apply operator is defined as: ##EQU25## There is a natural mapping .cndot..sub.x.sbsb.--.sub.val such that for each explicit substitution .epsilon., .epsilon..sub.x.sbsb.--.sub.val : DB.fwdarw.ESV. This mapping is defined by: ##EQU26## It is therefore apparent that for each query Q, explicit substitution .epsilon. and state DB, ##EQU27##

An exemplary smash operator for combining xsub-values will now be described. This smash operator, which is denoted `!`, is closely related to the above-described compose operator for explicit substitutions, and is also related to a smash operator used for delta values in the above-noted Heraclitus language. The smash of xsub-values E.sub.1 and E.sub.2 has domain dom(E.sub.1) .orgate. dom(E.sub.2), and is defined by: ##EQU28## It can be verified for explicit substitutions .epsilon..sub.1 and .epsilon..sub.2 and state DB that: ##EQU29## The following equation describes how the smash operator can be used to combine nested xsub-values during the evaluation of ENF queries. For each query Q, ##EQU30##

Evaluation of ENF queries using xsub-values in accordance with the invention will now be described using two exemplary evaluation algorithms. The first illustrates a basic approach to "filtering" the evaluation of a query by an xsub-value. The second provides improvements over the first by permitting clustered evaluation of relational algebra operators, such as combining a join operator with a select operator. The first algorithm utilizes a function filter.sub.1 which has signature:

filter.sub.1 : HQL expressions.times.ESV.times.DB.fwdarw.(R .orgate. ESV).

FIG. 5 shows the recursive definition of the filter.sub.1 function. On the left side of the equalities in FIG. 5, the symbols u.sub.-- op and b.sub.-- op are in the syntax of the relational algebra, and on the right side these symbols indicate the application of algebraic operators to relations. The function filter.sub.1 is constructed such that evaluating filter.sub.1 (Q, E) in state DB yields Q apply(DB, E)). The function filter.sub.1 in effect performs a depth-first traversal of the syntax tree of an ENF query Q. Upon encountering a when node, the right child is processed before the left child. The definition of filter.sub.1 on individual relation names performs the actual "filtering" by an xsub-value. The action of the filter.sub.1 function on the when construct has the effect of smashing together all of the xsub-values that affect a given subquery.

The first exemplary evaluation algorithm, designated HQL-1, may be summarized as follows. Given an HQL query Q in ENF and a database state DB, the value Q in DB is obtained by evaluating filter.sub.1 (Q, { }), that is, the function filter.sub.1 of FIG. 5 applied to Q and the empty xsub-value. A possible weakness of the above version of the HQL-1 algorithm is that it generally does not permit grouping of relational algebra operators into single physical operations, as frequently occurs in traditional optimizers for relational algebra queries. For example, the above HQL-1 algorithm would generally not permit the grouping of a join operator and a select operator in a query such as R .sigma.(S). However, the HQL-1 algorithm may be modified to permit certain types of grouping. For example, assume that T represents the syntax tree of the following query: ##EQU31## A collapse operator to be defined in detail below may be used to construct a tree collapse(T) having a root v that is labeled by S.sub.1 (R .sigma.(S.sub.2)) and has three children. The first child will be the root of the tree of Q.sub.1 when .epsilon..sub.1, the second child will be the root of the tree of Q.sub.2 when .epsilon..sub.2, and the third child will have label R.

As noted above, the collapse operator maps ENF syntax trees into a variant of ENF syntax trees. In this variant, nodes can be labeled by relation names in Z, by `when`, by `{ }` and by relational algebra queries of the form Q[S.sub.1, . . . , S.sub.k, R.sub.1, . . . R.sub.m ], where R.sub.1, . . . , R.sub.m are distinct relation names in Z and S.sub.1, . . . , Sk are "new" relation names. A node labeled Q[S.sub.1, . . . , S.sub.k, R.sub.1, . . . , R.sub.m ] will have k+m children. Below the first k children will be subtrees with a root node labeled by `when`, and the remaining m children will be leaves, labeled by R.sub.1, . . . , R.sub.m, respectively. The definition of the collapse operator is recursive on the structure of ENF syntax trees. If the ENF syntax tree has the structure `T.sub.0 when {T.sub.1 /R.sub.1, . . . , T.sub.n /R.sub.n }`, then collapse(T) may be defined as:

collapse(T.sub.0) when {collapse(T.sub.1)/R.sub.1, . . . , collapse(T.sub.n)R.sub.n }. If the ENF syntax tree T is a single node labeled by R .di-elect cons. Z, then collapse(T)=T. As the final case, assume that T is an ENF syntax tree with root node v labeled by a relational algebra operator. Let u.sub.1, . . . , u.sub.k be the nodes below v whose labels are relation names or when, such that each node between v and u.sub.i is labeled by a relational operator. Let T.sub.1, . . . , T.sub.m be the subtrees below the nodes among u.sub.1, . . . , u.sub.k that have label when, and let R.sub.1, . . . , R.sub.k be the relation names occurring as labels of u.sub.1, . . . , u.sub.n. Let Q.sub.v [S.sub.1, . . . , S.sub.m, R.sub.1, . . . , R.sub.k ] denote the relational algebra query that corresponds to the subtree of T with root v and leaves u.sub.1, . . . , u.sub.n, where S.sub.1, . . . , S.sub.m are new relation names corresponding to subtrees T.sub.1, . . . , T.sub.m. Then collapse(T) is the tree that has root v, where the label of v is Q.sub.v [S.sub.1, . . . , S.sub.m, R.sub.1, . . . , R.sub.k ], v has m+k children, the first m children are the roots of collapse(T.sub.1), . . . , collapse(T.sub.m), respectively, and the latter k children are leaves and are labeled by R.sub.1, . . . , R.sub.k, respectively.

FIG. 6 shows the definition of another filter function filter.sub.2 suitable for use in the second of the two hypothetical query evaluation algorithms noted above. The function filter.sub.2 is essentially the same as the previously-described function filter.sub.1, but modified to process collapsed ENF syntax trees. To describe filter.sub.2, it may be useful to first view filter.sub.1 as a mapping involving subtrees of ENF syntax trees, rather than expressions from HQL. The function filter.sub.2 is identical to the function filter.sub.1 except for its behavior on nodes with labels of the form Q [S.sub.1 . . . , S.sub.m R.sub.1 . . . R.sub.k ]. The exemplary function filter.sub.2 shown for xsub-value E in FIG. 6 assumes that v is such a label, with subtrees T.sub.1, . . . , T.sub.m below the first m children. The function eval.sub.-- filter.sub.-- x in FIG. 6 can be based on any conventional algorithm for evaluating relational algebra queries, with the modification that each relation R.sub.i mentioned explicitly in Q[S.sub.1, . . . , S.sub.m, R.sub.1, . . . , R.sub.k ] is filtered by xsub-value E, in a manner analogous to the action of function filter.sub.1 on individual relation names. Importantly, the function eval.sub.-- filter.sub.-- x as applied in filter.sub.2 may be used to cluster several relational operators together into a single physical operator. The function filter.sub.2 may form the basis of the second algorithm, denoted HQL-2, in which the value of an ENF query Q with syntax tree T in a database state DB is computed as filter.sub.2 (collapse(T), { }). It will be apparent to those skilled in the art that the illustrative algorithms HQL-1 and HQL-2 both exhibit the desirable property of correctness.

The exemplary HQL-2 evaluation algorithm can be generalized to cover delta values. Frequently, the hypothetical changes to a database state specified by a hypothetical update {U} will modify only a small fraction of the state. A delta value is a partial function .DELTA. that maps relation names in Z to pairs of relations, where the two relations in .DELTA.(R) have the same arity as R, for each R .epsilon. dom(.DELTA.). A delta value may be denoted as a set .DELTA.={<D.sub.1, I.sub.1 >/R.sub.1, . . . , <D.sub.n, I.sub.n >/R.sub.n } of binding pairs, where each D.sub.i and I.sub.i is a physical relation. If R .di-elect cons. dom(.DELTA.) and .DELTA.(R)=<D, I>, <D, I>, D may be denoted by R.sub..DELTA..sup.- and I may be denoted by R.sub..DELTA..sup.+. Also, if R .epsilon slash. dom(.DELTA.), then R.sub..DELTA..sup.- =R.sub..DELTA..sup.+ =0. The family of delta values is denoted by DV. Additional details regarding delta values may be found in, for example, S. Ghandeharizadeh, R. Hull and D. Jacobs, "Heraclitus: Elevating Deltas to be First-Class Citizens in a Database Programming Language," ACM Trans. on Database Systems, 21(3): 370-426, 1996, and J. Woodfill and M. Stonebraker, "An Implementation of Hypothetical Relations," Proc. of Intl. Conf. on Very Large Databases, pp. 157-165, September 1983. It should be noted that although the above-cited Ghandeharizadeh, Hull and Jacobs reference requires a delta value .DELTA. to satisfy the condition that for each R, R R.sub..DELTA..sup.- .orgate. R.sub..DELTA..sup.+ =0, this condition is not a requirement of the evaluation algorithms of the present invention.

One basic operator involving delta values is an apply operator that maps database states and delta values to database states. This apply operator may be defined, by analogy to the apply operator for xsub-values described above, as follows:

apply(DB, .DELTA.)(R)=((DB(R)-R.sub..DELTA..sup.-) .orgate. R.sub..DELTA..sup.+). The use of delta values can significantly improve the performance of the query evaluation algorithms of the present invention relative to the use of xsub-values. Consider as an example the simple hypothetical query Q=(R S) when {U}. In accordance with the above-described evaluation algorithm HQL-2, the hypothetical update {U} might first be transformed into an explicit substitution of the form .epsilon.={Q.sub.1 /R, Q.sub.2 /S}. If the difference between DB(R) and Q.sub.1 (DB) is relatively small, for example, about 1% of the size of DB(R), and the same holds true for Q.sub.2, algorithm HQL-2 would compute complete values for Q.sub.1 (DB) and Q.sub.2 (DB), and then perform the join using these new relations.

A more efficient alternative evaluation algorithm may be implemented in accordance with the invention by using special-purpose operators that essentially combine delta application with relational algebra operators. For example, suppose that in state DB, the delta corresponding to .epsilon. is .DELTA.. To compute R S when .epsilon., one can compute R S `when` A using the join.sub.-- when operator of the of the above-noted reference on the Heraclitus language. The when operator is placed in quotes because the delta value .DELTA. is a semantic object rather than a syntactic object. The above algebraic operator takes six physical relations as operands. In particular, join.sub.-- when(DB(R), DB(S), R.sub..DELTA..sup.-, R.sub..DELTA..sup.+, S.sub..DELTA..sup.-, S.sub..DELTA..sup.+) computes the value of. ##EQU32## Instead of computing the above value, as most conventional SQL optimizers might, the join.sub.-- when can be optimized for the case where the delta relations are relatively small. For example, a conventional implementation described in S. Ghandeharizadeh et al., "On Implementing a Language for Specifying Active Database Execution Models," Proc. of Intl. Conf. on Very Large Data Bases, pp. 441-454, 1993, which is incorporated by reference herein, is a variant of the sort-merge algorithm for join. In this variant, all six operands of join.sub.-- when are sorted, and the merge simultaneously tests for (i) the join condition, (ii) membership in R `when` .DELTA., and (iii) membership in S `when` A. For small deltas the cost of evaluating a join under a delta is only more expensive than evaluating the join without the when. As a general rule of thumb, if the delta has size x % of the base relations, then the join.sub.-- when will take an additional 2x % of time over the time for a join of the base relations. More generally, the two S. Ghandeharizadeh et al. references cited above describe how to use join.sub.-- when and analogous operators to efficiently evaluate hypothetical queries of the form Q `when` .DELTA., where Q is an arbitrarily deep relational algebra query.

The illustrative embodiments of the present invention will use the notation eval.sub.-- filter.sub.-- d(Q, .DELTA.) to denote the result of using conventional evaluation algorithms such as those described in the S. Ghandeharizadeh et al. references. It will be assumed without limitation that for each relational algebra query Q and state DB, the result of eval.sub.-- filter.sub.-- d(Q, .DELTA.) in DB is equal to Q (apply(DB, .DELTA.). There are a number of trade-offs to be made in connection with the selection of a delta value .DELTA. associated with an explicit substitution .epsilon.. One possibility which provides a high degree of precision involves, for each R .di-elect cons. dom(.epsilon.), setting: ##EQU33## However, this selection may be prohibitively expensive to evaluate in certain applications. Reduced computation complexity may be achieved by using approximate delta values in the following manner. A delta value .DELTA. is said to capture xsub-value E in state DB if apply(DB, .DELTA.)=apply(DB, E). As part of the generalization of evaluation algorithm HQL-2 to cover the use of delta values, an approach for building deltas that capture explicit substitutions will be described. In many applications, this approach will be more efficient than the high precision approach previously described.

By analogy to the above-described smash operator for xsub-values, a smash operator is defined for delta values as follows. The smash of delta values .DELTA..sub.1 and .DELTA..sub.2, denoted .DELTA..sub. !.DELTA..sub.2, is that delta value .DELTA. with domain equal to dom(.DELTA..sub.1) .orgate. dom(.DELTA..sub.2), and such that:

R.sub..DELTA..sup.- =(R.sub..DELTA.1.sup.- -R.sub..DELTA.2.sup.+) .orgate. R.sub..DELTA.2.sup.- R.sub..DELTA..sup.- =(R.sub..DELTA.1.sup.+ -R.sub..DELTA.2.sup.-) .orgate. R.sub..DELTA.2.sup.+ for each R in dom(.DELTA.). Let .epsilon..sub.1 and .epsilon..sub.2 be explicit substitutions and DB a database state. If .DELTA..sub.1 captures .epsilon..sub.1 .sub.x.sbsb..sub.val (DB) in DB and .DELTA..sub.2 captures .epsilon..sub.2 .sub.x.sbsb..sub.val (apply(DB, .DELTA..sub.1)) in apply(DB, .DELTA..sub.1), then .DELTA..sub.1 !.DELTA..sub.2 captures .epsilon..sub.1 # .epsilon..sub.2 .sub.x.sbsb..sub.val (DB) in state DB. Furthermore, for each relational algebra query Q, ##EQU34## This is analogous to the equation presented for explicit substitutions above that relates when and smash, and describes generally how deltas should be combined when evaluating queries under nested when constructs.

An illustrative generalization of the HQL-2 evaluation algorithm to cover the use of delta values will compute delta values based on a sequence of atomic inserts and deletes that make up a hypothetical update. To this end, the notation for ENF given above will be modified in the following manner. An HQL query is in modified ENF (mod-ENF) if it satisfies the definition of ENF given previously, except that instead of explicit substitutions, all hypothetical updates present in the query have the form {A.sub.1 ; . . . ; A.sub.n }, where each A.sub.1 is an atomic insert or delete. The generalization of the HQL-2 algorithm to cover the use of delta values is referred to as the HQL-3 algorithm.

FIG. 7 shows a recursively-defined function filter.sub.3 which is used in the exemplary HQL-3 algorithm. The function filter.sub.3 takes two arguments, the collapse of a mod-ENF syntax tree and a delta value. The hypothetical update A in FIG. 7 ranges over atomic deletes and inserts, and U ranges over update expressions of the form A.sub.1 ; . . . ; A.sub.n, where each A.sub.i is an atomic insert or delete. Of particular interest is the treatment by filter.sub.3 of hypothetical updates of the form {U}, where U=A.sub.1 ; . . . ; A.sub.n and each A.sub.i is an atomic insert or delete. It can be verified that if .DELTA. captures xsub-value E in state DB and .epsilon.' is an explicit substitution equivalent to {U}, then filter.sub.3 ({U},.DELTA.) captures the xsub-value .epsilon.'.sub.x.sbsb.--.sub.val (apply(DB, E)) in the state apply(DB, E). The algorithm HQL-3 may therefore be stated as follows. Given a mod-ENF query Q with syntax tree T, construct a syntax tree collapse(T), and compute the value of Q in state DB as filter.sub.3 (collapse(T), { }), that is, the application of filter.sub.3 to collapse(T) and the empty delta value. Like the other exemplary algorithms HQL-1 and HQL-2 described above, the HQL-3 algorithm can also be shown to exhibit the property of correctness.

Exemplary algorithms HQL-1 and HQL-2 use xsub-values as concrete, materialized representations of explicit substitutions, and exemplary algorithm HQL-3 uses delta values as concrete, materialized representations of explicit substitutions. A "hybrid xsub/delta value" may be defined as a partial function H that maps relation names in Z to values, where for each R in the domain of H, value H(R) is either a physical relation J with the same arity as R, or a pair <D, I> where both D and I are physical relations having the same arity as R. Hybrid xsub/delta values can also be used as concrete, materialized representations of explicit substitutions, in analogy to xsub-values and delta values. The operations of apply and smash on hybrid xsub/delta values can be defined in analogy with the definitions of those operators for xsub-values and delta values. It will be apparent to those skilled in the art that the techniques of exemplary algorithms HQL-1, HQL-2 and HQL-3 can be used to develop additional algorithms that use hybrid xsub/delta values in place of xsub-values or delta values.

A number of examples will now be provided to illustrate different strategies for evaluating hypothetical queries in accordance with the invention. Consider an application in which queries against many hypothetical states are to be considered and compared. A tree of potential updates could be constructed, where each edge is labeled with a hypothetical update expression. A node v in the tree corresponds to the hypothetical state produced by applying all of the hypothetical update expressions in the path from the root of the tree to the node v. An exemplary query may be of the form:

Q'.ident.((Q.sub.1 when .eta..sub.1)-(Q.sub.2 when .eta..sub.2)) when .eta..sub.3 where Q.sub.1 and Q.sub.2 are relational algebra queries, .eta..sub.3 is the hypothetical update expression associated with a path from the root to some node v, and .eta..sub.1 and .eta..sub.2 are the hypothetical update expressions associated with two possible extensions of this path. The query Q' is thus asking for the difference between the value of Q.sub.1 under hypothetical update .eta..sub.1 and the value of Q.sub.2 under hypothetical update .eta..sub.2, assuming the hypothetical update .eta..sub.3 has already occurred.

One embodiment of the invention may evaluate the query Q' using an approach which is "lazy" with regard to the evaluation of hypothetical states. This approach could involve building the relational algebra queries Q.sub.1 ' and Q.sub.2 ', with the properties that:

Q.sub.1 '.ident.(Q.sub.1 when .eta..sub.1) when .eta..sub.3 Q.sub.1 '.ident.(Q.sub.2 when .eta..sub.2) when .eta..sub.3 and then evaluating Q.sub.1 '-Q.sub.2 '. The queries Q.sub.1 ' and Q.sub.2 ' can be determined using the explicit substitution framework described above. This will be illustrated using the following query: ##EQU35## in which R and S have the same arity, Q.sub.1 =Q.sub.2 =R S, and the join condition is not specified. Note that in the absence of the outer hypothetical update, one would expect the exemplary query to yield the non-empty result .sigma..sub.30<A.ltoreq.50 (S) S. The construction of Q.sub.1 ' will be considered first. The construction process begins with the query: ##EQU36## The two hypothetical-state expressions are then replaced using the above-described explicit substitutions, to yield: ##EQU37## The inner substitution is then applied to the query, resulting in: ##EQU38## Applying the second substitution yields: ##EQU39## The query Q.sub.2 ' can be transformed in an analogous manner to: ##EQU40## It follows that the original exemplary query is equivalent to the empty query .phi.. Importantly, this analysis can be performed without reference to the underlying database state.

Other embodiments of the invention may utilize hybrids of "eager" and "lazy" approaches to evaluate hypothetical queries. For example, suppose that in the above query Q' the hypothetical state of .eta..sub.3 gives new values for relations R and S, and that the subqueries Q.sub.1 and Q.sub.2 involve many occurrences of R and S. Suppose further that, for i=1 and 2, the relation names affected by .eta..sub.i occur only once or twice in Q.sub.i. In this case it may be more efficient to use an evaluation strategy guided by:

(Q.sub.1 "-Q.sub.2 ") when .eta..sub.3 where Q.sub.i ".ident.(Q.sub.i when .eta..sub.i), such that .eta..sub.3 is evaluated eagerly, and .eta..sub.1 and .eta..sub.2 are evaluated lazily.

The following two examples illustrate optimizations based on the fact that hypothetical states can be viewed as substitutions in accordance with the present invention. The first example is based on composing substitutions, and the second is based on binding removal in substitutions. In the first example, multiple queries are asked against the same hypothetical state. For example, the query:

Q when {ins(R, .sigma..sub.A>30 (S)}) when {del(S, .sigma..sup.A<60 (S)} may be asked for many queries Q. As in the previous example, such queries could be evaluated by converting to explicit substitutions such as: (Q when {R .orgate. .sigma..sub.A>30 (S)/R}) when {S-.sigma..sub.A<60 (S)/S}. and then applying the two substitutions into Q. However, it might be more efficient to compute the composition of the two substitutions, and use that composition repeatedly. As described in detail above, the composition operator `#` is used to represent the composition of hypothetical-state expressions: Q when [{S-.sigma..sub.A<60 (S)/S} # {R .orgate. .sigma..sub.A>30 (S)/R}], and then expanding the definition of composition to obtain: Q when {.sigma..sub.A.gtoreq.60 (S)/S, R .orgate. .sigma..sub.A>30 (S-.sigma..sub.A<60 (S))/R}, and finally using algebraic simplification to arrive at: Q when [{.sigma..sub.A.gtoreq.60 (S)/S, R .orgate. .sigma..sub.A.gtoreq.60 (S)/R}. In accordance with a "lazy" approach, the new substitution can be applied to each of the queries Q. This substitution remains valid even if the underlying database state is changed. A hybrid approach might utilize an "eager" strategy to partially materialize the substitution, using it to filter evaluation of the various Q queries. More generally, assume that in a single state DB the expression:

((Q.sub.1 when .eta..sub.1)-((Q.sub.2 when .eta..sub.2) when .eta..sub.3

is to be evaluated with many different values for Q.sub.1, Q.sub.2, .eta..sub.1 and .eta..sub.2. In this case it may be more efficient to materialize part of .eta..sub.3 for use with Q.sub.1 when .eta..sub.1, and to materialize part of .eta..sub.3 # .eta..sub.2 for use with Q.sub.2.

The next example uses the technique of binding removal in substitutions. Suppose that queries of the form:

Q when {ins(R, .sigma..sub.p (S)); del(S, .sigma..sub.q (R)), ins(T, .pi..sub.A R)} are to be asked for many different Q. Direct application of the techniques used in the previous example suggests that the substitution: {R .orgate. .sigma..sub.p (S)/R} # {S-.sigma..sub.q (R)/S} # {T .orgate. .pi..sub.A (R)/T} should be computed. Suppose however that none of the queries Q will involve relation name S. This situation permits computation of the full composition to be avoided. In particular, the middle substitution can be dropped from consideration, in accordance with the following classical equivalence for substitutions: sub(E, .sigma.).ident.sub(E, [.sigma.-{t/v}]) where .sigma. is a substitution, t/v is a binding in .sigma., and variable v is not free in expression E. In the case of eager evaluation, this will reduce work on the underlying data, and in the case of lazy evaluation this will reduce work in the optimizer.

The final example shows that transforming to a fully lazy equivalent of an hypothetical query Q can lead to an exponential blow-up in certain situations. This is one motivation for exploring both lazy and hybrid lazy-eager forms of evaluation of hypothetical queries. It will also be demonstrated that the blow-up may be avoided in practice. Consider the query: ##EQU41## where for each i, E.sub.i (R.sub.i) is a relational algebra query whose only relation name is R.sub.i. In the following, the intended arities of relations can be inferred from the context. Note that expression `Q` is linear in n, and that the fully lazy equivalent of Q is:

QE.sub.1 (E.sub.2 (. . . (E.sub.n (R.sub.n) . . . )). Suppose that expression E.sub.i (R.sub.i) is R.sub.i .times.R.sub.i for each i. Then Q is exponential in n. Also, if the relations R.sub.i are non-empty, then the value of red(Q) is also exponential in n, leading to an exponential blow-up. In some cases, this situation can be avoided using relational algebra rewriting. For example, suppose that E.sub.i (R.sub.i)=R.sub.i .times.R.sub.i for each i except for one j, where E.sub.j (R.sub.j)=R.sub.j -R.sub.j. Then the size of the query Q is exponential in n, but its value is .phi., the empty query. This equivalence can be found using the equational theory described in detail above. In particular, an algebra rewriting rule can be used to replace {R.sub.j -R.sub.j /R.sub.j-1 } by {.phi./R.sub.j-1 }. Several applications of binding-removal, as in the previous example, will then yield: ##EQU42## A series of compositions and binding-removals can then be used to obtain Q.ident.R.sub.0 when {.phi./R.sub.j-1 }, which avoids the above-noted exponential blow-up. It should be noted that if each E.sub.i has the form .pi..sub.A0 . . . Ai-1 (.sigma..sub.Ai<0 R.sub.i) .orgate. .pi..sub.A0 . . . Ai-1 (.sigma..sub.Ai>0 R.sub.i), and these intersections are small, a hybrid lazy-eager evaluation strategy may be most efficient.

It is apparent from the above description that the present invention provides a framework for optimizing hypothetical queries, and supports a broad spectrum of implementation strategies from hybrid eager-lazy strategies to purely lazy strategies. An important aspect of the framework is a set of rewriting rules for processing hypothetical queries using explicit substitutions. The invention may also be combined with techniques for estimating the cost of execution plans involving xsub-values and delta values, heuristics whereby optimizers can reduce the space of queries that will be considered for execution, and techniques for evaluating sequences of closely-related hypothetical queries. Exemplary heuristics for query space reduction in an optimizer are described in G. Graefe and D. J. DeWitt, "The EXODUS optimizer generator," Proc. ACM SIGMOD Symp. on the Management of Data, pp. 160-172, 1987, and G. Graefe, "Query Evaluation Techniques for Large Databases," ACM Computing Surveys, 25(2):73-170, 1993, which are incorporated by reference herein. The framework may be extended, for example, to query languages that include bags and aggregation, and to update languages that include constructs such as conditionals, procedures, temporary tables, and aborting constructs. Such constructs generally do not extend the expressive power of the update language, but do dramatically increase the conciseness with which database management programs can be written. It should also be noted that the invention is applicable to constructs of the conventional Heraclitus language other than those described above, and to constructs of numerous other database programming languages. For example, the keyword when can be applied to hypothetical-state expressions on the left, that is, of the form .eta..sub.1 when .eta..sub.2.

The above-described embodiments of the invention are intended to be illustrative only. Numerous alternative embodiments may be devised by those skilled in the art without departing from the scope of the following claims.

Claims

1. A method of evaluating a hypothetical query in a database system, the method comprising the steps of:

applying to the hypothetical query one or more algebraic equivalences involving explicit substitutions in order to generate at least one query which is equivalent to the hypothetical query, wherein each of at least a subset of the explicit substitutions represents a hypothetical database state change; and

directly evaluating the equivalent query.

2. The method of claim 1 wherein the applying step includes applying to the hypothetical query algebraic equivalences involving explicit substitutions in order to generate a set of additional queries each of which is equivalent to the hypothetical query.

3. The method of claim 2 further including the steps of:

estimating an amount of time it would take to directly evaluate each of the queries in the set of additional queries; and

selecting a particular query from the set of additional queries which has the lowest estimated evaluation time for direct evaluation in the directly evaluating step.

4. The method of claim 1 wherein the hypothetical query includes at least one construct substantially equivalent to a when construct, and the at least one equivalent query includes no construct substantially equivalent to a when construct.

5. The method of claim 1 wherein the hypothetical query is in a normal form suitable for direct evaluation, wherein the normal form indicates that one or more hypothetical state expressions of the query correspond to explicit substitutions.

6. The method of claim 5 wherein the normal form indicates that the query does not utilize a composition operator.

7. The method of claim 1 wherein the at least one equivalent query is representative of a lazy evaluation strategy and includes no hypothetical state expressions.

8. The method of claim 1 wherein the at least one equivalent query is representative of a hybrid lazy-eager evaluation strategy.

9. The method of claim 1 wherein the directly evaluating step includes the step of converting at least one of the explicit substitutions into at least one value.

10. The method of claim 9 wherein the value is an xsub-value E that maps relation names into relations, where a relation E(R) has the same arity as a relation name R for each R .orgate. dom(E).

11. The method of claim 10 wherein the xsub-value is a set E={J.sub.1 /R.sub.1, . . . J.sub.n /R.sub.n } of binding pairs, where each J.sub.i is a physical relation.

12. The method of claim 9 wherein the value is a delta value .DELTA. that maps relation names into pairs of relations, where a pair of relations in .DELTA.(R) has the same arity as a relation name R, for each R .di-elect cons. dom(.DELTA.).

13. The method of claim 12 wherein the delta value is a set .DELTA.={<D.sub.1, I.sub.1 >/R.sub.1, . . . <D.sub.n, I.sub.n >/R.sub.n } of binding pairs, where each D.sub.i and I.sub.i is a physical relation.

14. The method of claim 9 wherein the value is a hybrid of an xsub-value and a delta value.

15. The method of claim 1 wherein the directly evaluating step includes filtering the equivalent query through a value representative of one of the explicit substitutions.

16. The method of claim 15 wherein the value is an xsub-value E that maps relation names into relations, where a relation E(R) has the same arity as a relation name R for each R .di-elect cons. dom(E).

17. The method of claim 15 wherein the value is a delta value .DELTA. that maps relation names into pairs of relations, where a pair of relations in .DELTA.(R) has the same arity as a relation name R, for each R .di-elect cons. dom(.DELTA.).

18. The method of claim 15 wherein the value is a hybrid of an xsub-value and a delta value.

19. The method of claim 1 wherein the applying step includes applying an algebraic equivalence that transforms an explicit substitution into an equivalent explicit substitution.

20. The method of claim 1 wherein the applying step includes applying an algebraic equivalence that transforms an explicit substitution into a hypothetical state expression which is not an explicit substitution.

21. The method of claim 1 wherein the applying step includes applying an algebraic equivalence that transforms a hypothetical state expression which is not an explicit substitution into an explicit substitution.

22. An apparatus for use in evaluating a hypothetical query in a database system, the apparatus comprising:

a memory for storing at least one data item referenced by the hypothetical query; and

a processor coupled to the memory and operative to (i) apply to the hypothetical query one or more algebraic equivalences involving explicit substitutions in order to generate at least one query which is equivalent to the hypothetical query, wherein each of at least a subset of the explicit substitutions represents a hypothetical database state change, and (ii) directly evaluate the equivalent query.

23. The apparatus of claim 22 wherein the processor is further operative to apply to the hypothetical query algebraic equivalences involving explicit substitutions in order to generate a set of additional queries each of which is equivalent to the hypothetical query.

24. The apparatus of claim 23 wherein the processor is further operative to estimate an amount of time it would take to directly evaluate each of the queries in the set of additional queries, and to select for direct evaluation a particular query from the set of additional queries with the lowest estimated evaluation time.

25. The apparatus of claim 22 wherein the hypothetical query includes at least one construct substantially equivalent to a when construct, and the at least one equivalent query includes no construct substantially equivalent to a when construct.

26. The apparatus of claim 22 wherein the hypothetical query is in a normal form suitable for direct evaluation, wherein the normal form indicates that one or more hypothetical state expressions of the query correspond to explicit substitutions.

27. The apparatus of claim 26 wherein the normal form indicates that the query does not utilize a composition operator.

28. The apparatus of claim 22 wherein the at least one equivalent query is representative of a lazy evaluation strategy and includes no hypothetical state expressions.

29. The apparatus of claim 22 wherein the at least one equivalent query is representative of a hybrid lazy-eager evaluation strategy.

30. The apparatus of claim 22 wherein the processor is further operative to convert at least one of the explicit substitutions into at least one value.

31. The apparatus of claim 30 wherein the value is an xsub-value E that maps relation names into relations, where a relation E(R) has the same arity as a relation name R for each R .di-elect cons. dom(E).

32. The apparatus of claim 31 wherein the xsub-value is a set E={J.sub.1 /R.sub.1, . . . , J.sub./R.sub.n } of binding pairs, where each J.sub.i is a physical relation.

33. The apparatus of claim 30 wherein the value is a delta value .DELTA. that maps relation names into pairs of relations, where a pair of relations in .DELTA.(R) has the same arity as a relation name R, for each R .di-elect cons. dom(.DELTA.).

34. The apparatus of claim 33 wherein the delta value is a set .DELTA.={<D.sub.1, I.sub.1 >/R.sub.1, . . . , <D.sub.n, I.sub.n >/R.sub.n } of binding pairs, where each D.sub.i and I.sub.i is a physical relation.

35. The apparatus of claim 30 wherein the value is a hybrid of an xsub-value and a delta value.

36. The apparatus of claim 22 wherein the processor directly evaluates the equivalent query by filtering the equivalent query through a value representative of one of the explicit substitutions.

37. The apparatus of claim 36 wherein the value is an xsub-value E that maps relation names into relations, where a relation E(R) has the same arity as a relation name R for each R .di-elect cons. dom(E).

38. The apparatus of claim 36 wherein the value is a delta value .DELTA. that maps relation names into pairs of relations, where a pair of relations in .DELTA.(R) has the same arity as a relation name R, for each R .di-elect cons. dom(.DELTA.).

39. The apparatus of claim 36 wherein the value is a hybrid of an xsub-value and a delta value.

40. The apparatus of claim 22 wherein the processor is further operative to apply an algebraic equivalence that transforms an explicit substitution into an equivalent explicit substitution.

41. The apparatus of claim 22 wherein the processor is further operative to apply an algebraic equivalence that transforms an explicit substitution into a hypothetical state expression which is not an explicit substitution.

42. The apparatus of claim 22 wherein the processor is further operative to apply an algebraic equivalence that transforms a hypothetical state expression which is not an explicit substitution into an explicit substitution.

43. An apparatus for evaluating a hypothetical query in a database system, the apparatus comprising:

means for applying to the hypothetical query one or more algebraic equivalences involving explicit substitutions in order to generate at least one query which is equivalent to the hypothetical query, wherein each of at least a subset of the explicit substitutions represents a hypothetical database state change; and

means for directly evaluating the equivalent query.

44. The apparatus of claim 43 wherein the applying means is operative to apply to the hypothetical query algebraic equivalences involving explicit substitutions in order to generate a set of additional queries each of which is equivalent to the hypothetical query.

45. The apparatus of claim 44 further including:

means for estimating an amount of time it would take to directly evaluate each of the queries in the set of additional queries; and

means for selecting a particular query from the set of additional queries which has the lowest estimated evaluation time for direct evaluation.

46. The apparatus of claim 43 wherein the hypothetical query includes at least one construct substantially equivalent to a when construct, and the at least one equivalent query includes no construct substantially equivalent to a when construct.

47. The apparatus of claim 43 wherein the hypothetical query is in a normal form suitable for direct evaluation, wherein the normal form indicates that one or more hypothetical state expressions of the query correspond to explicit substitutions.

48. The apparatus of claim 47 wherein the normal form indicates that the query does not utilize a composition operator.

49. The apparatus of claim 43 wherein the at least one equivalent query is representative of a lazy evaluation strategy and includes no hypothetical state expressions.

50. The apparatus of claim 43 wherein the at least one equivalent query is representative of a hybrid lazy-eager evaluation strategy.