WO1998009238A1 - Method and system for using materialized views to evaluate queries involving aggregation - Google Patents

Abstract

The present invention is a method and system for using materialized views to compute answers to SQL queries with grouping and aggregation. A query is evaluated by using a materialized view. The materialized view is semantically analyzed to determine whether the materialized view is usable in evaluating an input query. The semantic analysis includes determining that the materialized view does not project out any columns needed to evaluate the input query and determining that the view does not discard any tuple that satisfies a condition enforced in the input query. If the view is usable, the input query is rewritten to produce an output query that is multi-set equivalent to the input query and that specifies one or more occurrences of the materialized view as a source of information to be returned by the output query. The output query is then evaluated. The semantic analysis an rewritting may be iterated, with the output query of each iteration being the input query of the next iteration. The output query is evaluated after the last iteration.

Description

METHOD AND SYSTEM FOR USING MATERIALIZED VIEWS TO EVALUATE QUERIES INVOLVING AGGREGATION

Related Applications

This application claims the benefit of U.S. Provisional Application No. 60/024,635, filed August 27, 1996.

Field of the Invention

The present invention relates to a method and system for using materialized views to compute answers to (evaluate) SQL queries with grouping and aggregation.

Background of the Invention

Databases are compilations of information. Useful operations that can be performed on a database include the selection of specific data from the database, and the computation of summary information on all or part of the data in the database. One well-known way of specifying operations to be performed on a database is Structured Query Language (SQL). An SQL statement that performs an operation on a database is known as a query.

In large database applications, such as data warehouses and very large transaction recording systems, the size of the database and the volume of incoming data may be very large. Operations on very large databases are time consuming, due to the large amount of data to be processed, and expensive, due to the large quantity of system resources that must be utilized. One solution to this problem is to materialize and maintain appropriately defined aggregation views (summary tables), which are much smaller than the underlying database and can be cached in fast memory. In globally distributed information systems, the relations may be distributed or replicated, and locating as well as accessing them may be expensive and sometimes not even possible. In mobile computing applications, the relations may be stored on a server and be accessible only via low bandwidth wireless communication, which may additionally become unavailable. Locally cached materialized views of the data, such as results of previous queries, may considerably improve the performance of such applications.

A need arises for an automated technique by which a time consuming and expensive query can be converted or rewritten to form an equivalent query that uses materialized views to compute the answers.

There has been previous work on using views to answer queries (e.g., H. Z. Yang and P. A. Larson, "Query transformation for PSJ-queries," In Proc. VLDB, 1987; M. Stonebraker, A. Jhingran, J. Goh, and S. Potamianos, "On rules, procedures, caching and views in database systems", In Proc. ACM SIGMOD, 1990; O. G. Tsatalos, M. H. Solomon, and Y. E. Ioannidis, "The GMAP: A versatile tool for physical data independence", In Proc. VLDB, 1994; C. M. Chen and N. Roussopoulos, "The implementation and performance evaluation of the ADMS query optimizer: Integrating query result caching and matching", In Proc. EDBT, 1994; S. Chaudhuri, R. Krishnamurthy, S. Potamianos, and K. Shim, "Optimizing queries with materialized views", In. Proc. ICDE, 1995; A. Y. Levy, A.O. Mendelzon,, Y. Sagiv,, and D. Srivastava, "Answering queries using views", In Proc. ACM PODS, 1995], but the problem of finding the equivalent rewritings for SQL queries with multiset semantics, grouping and aggregation, have received little attention. Caching of previous query results was explored in T. Sellis, "Intelligent caching and indexing techniques for relational database systems", Information Systems, pp. 175-188, 1988, as a means of supporting stored procedures. This corresponds to using materialized views when they match syntactically a subexpression of the query. In the ADMS optimizer, subquery expressions corresponding to nodes in the query execution (operator) tree were also cached. A cached result was matched against a new query by using common expression analysis. Grouping and aggregation issued were not addressed.

View usability has been studied for conjunctive queries with set semantics and without grouping and aggregation. Levy et al. showed a close condition between the problem of usability of a view in evaluating a query and the problem of query containment. However, this connection does not carry over to the multiset case. Yang et al also presented a simple technique for generating a rewriting of a query Q using view V, under set semantics. Essentially the technique consists of first conjoining V to the FROM clause of Q, and then (independently) minimizing the resulting query to eliminate redundant tables. In the case of SQL queries, however, because of the multiset semantics, the query will not be equivalent after conjoining V to the FROM clause, even if it may be equivalent after removing other tables. Therefore, we need to find a priori which tables in the FROM clause will be replaced by V.

Optimization of conjunctive SQL queries using conjunctive views has been studied in Chaudhuri et al. In addition to considering when such views are usable in evaluating a query, they suggest how to perform this optimization in a cost- based fashion. However, they did not consider the possibility of rewritings that are UNION ALLS of single-block queries.

Recently, the problem of using materialized aggregation views to answer aggregation queries using a purely transformational approach has been considered in A. Gupta, V. Harinarayan, and D. Quass, "Aggregate-query processing in data warehousing environments", Proc. VLDB, 1995. They perform syntactic transformations on the operator tree representation of the query such that the definition of the view would be identical to a sub-part of the definition of the query. Additional information of queries involving aggregation have been proposed. The transformational approach is more restrictive than our semantic approach. For example, these techniques would neither determine the usability of view Vj in evaluating query Q\ in Example 1.1, nor the usability of view V, in evaluating Q\ in the same example. Also, no formal guarantees of completeness are provided.

A related problem is studied in A. Gupta, LS. Mumick, and K.A. Ross. "Adapting materialized views after redefinitions", In Proc. ACM SIGMOD, 1995. They assume that a materialized view may be redefined, and investigate how to adapt the materialization of the view to reflect the redefinition. This problem is clearly a special case of the one we study., with the additional assumptions that the system knows the type of modification that took place, that the new view definition is "close" to the old definition, and that the view materialization may be modified.

Summary of the Invention

The present invention is a method and system for using materialized views to compute answers to SQL queries with grouping and aggregation. A query is evaluated using a materialized view. The materialized view is semantically analyzed to determine whether the materialized view is usable in evaluating an input query. The semantic analysis includes determining that the materialized view does not project out any columns needed to evaluate the input query and determining that the view does not discard any tuple that satisfies a condition enforced in the input query. If the view is usable, the input query is rewritten to produce an output query that is multi-set equivalent to the input query and that specifies one or more occurrences of the materialized view as a source of information to be returned by the output query. The output query is then evaluated.

The semantic analysis and rewriting may be iterated, with the output query of each iteration being the input query of the next iteration. The output query is evaluated after the last iteration.

In one embodiment of the present invention there are a plurality of materialized views. The steps of semantically analyzing and rewriting are iterated at least once for each of the materialized views, with the output query of each iteration being the input query of the next iteration. The evaluating step is performed after the last iteration.

In one embodiment of the present invention, each one of the plurality of materialized views is similar. In another embodiment of the present invention, at least one of the plurality of materialized views is different. In another embodiment of the present invention, each one of the plurality of materialized views is different.

In one embodiment of the present invention, the materialized view does not have aggregation. In another embodiment of the present invention, the materialized view has aggregation.

Brief Description of the Drawings

The details of the present invention, both as to its structure and operation, can best be understood by referring to the accompanying drawings, in which like reference numbers and designations refer to like elements.

Fig. la is a block diagram of an exemplary database system with materialized views.

Fig. lb is a block diagram of relationship among tables, queries and views. Fig. lc is an exemplary format of a database table. Fig. Id is an exemplary format of another database table. Fig. 2 is a flow diagram of a process for rewriting a query, according to the present invention, implemented in the system of Fig. la.

Fig. 3 is a diagram of usability conditions for a single-block aggregation query without a HAVING clause, a single-block conjunctive view, with a single- block rewritten query.

Fig. 4 is a flow diagram of a rewriting process for a single-block aggregation query without a HAVING Clause, a single-block conjunctive view, with a single-block rewritten query. Fig. 5 is a diagram of a modification of condition C4 of Fig. 3 when multi- block rewritten queries are permitted.

Fig. 6 is a flow diagram of a rewriting process for a single-block aggregation query without a HAVING clause, a single-block conjunctive view, with a multi-block rewritten query. Fig. 7 is a diagram of a modification of condition C3 of Fig. 3, for a single- block aggregation query with a HAVING clause.

Fig. 8 is a flow diagram of a rewriting process for a single-block aggregation query with a HAVING clause, a single-block conjunctive view, with a single-block rewritten query. Fig. 9 is a diagram of usability conditions for a single-block aggregation query without a HAVING clause, a single-block aggregation view without a HAVING clause, with a single-block rewritten query.

Fig. 10 is a flow diagram of a rewriting process for a single-block aggregation query without a HAVING clause, a single-block aggregation view without a HAVING clause, with a single-block rewritten query. Detailed Description of the Invention

The present invention uses materialized views to compute answers to SQL queries with grouping and aggregation, in the presence of multiset tables. In addition to its obvious potential in query optimization, this problem is important in many applications, such as data warehousing, very large transaction recording systems, global information systems and mobile computing, where access to local or cached materialized views may be cheaper than access to the underlying database. Several conclusions result: First, we show that in the case where the query has grouping and aggregation but the views do not, a view is usable in answering a query only if there is an isomorphism between the view and a portion of the query. Second, when the views also have grouping and aggregation we identify conditions under which the aggregation information present in a view is sufficient to perform the aggregation computations required in the query.

The described procedures for rewriting a query also consider the case in which the rewritten query may be a union of single-block queries. The approach is a semantic one, in that it detects when the information existing in a view is sufficient to answer a query. In contrast, previous work performed syntactic transformations on the query such that the definition of the view would be a sub- part of the definition of the query. Consequently, these prior methods can only detect usages of views in limited cases.

The problem of using materialized SQL views to answer SQL queries is formalized as finding a rewriting of a query Q where the views occur in the FROM clause, and the rewritten query is multiset-equivalent to Q. The technical challenges arise from the multiset semantics of SQL, in conjunction with the use of grouping and aggregation.

We focus on queries and views of the form "SELECT-FROM-WHERE- GROUPBY-HAVING", i.e., single-block queries, where the SELECT and HAVING clauses may contain the SQL aggregate functions MIN, MAX, SUM and COUNT. The SQL aggregate functions SUM, COUNT and AVG are related in that, given values for two of them over some column, the third can be computed. Dealing with AVG is consequently straightforward, but complicates the presentation. Hence, AVG is not considered. The availability of any meta- information about the schema, such as keys or functional dependencies, is not assumed.

An exemplary database system 100 is shown in Fig. la. System 100 includes central processing unit (CPU) 102, which is connected to random access memory (RAM) 104, read-only memory (ROM) 106, input/output devices (I/O) 108 and storage device 1 10. CPU 102 may comprise a microprocessor, for example, an INTEL PENTIUM processor, or CPU 102 may comprise a minicomputer or mainframe processor. RAM 104 and ROM 106 store program instructions that are executed by CPU 102 and data that is used during program execution. I/O devices 108 may include any devices used to input data to system 100, such as a keyboard, mouse, trackpad, trackball and graphics tablet, to output data from system 100, such as a display and a printer, and to both input and output data, such as a modem and a network adapter. Storage device 110, which stores data that is used by the present invention, may comprise, for example, a magnetic disk and/or optical disk and may also comprise a magnetic tape.

Storage device 110 includes database 1 12, which is typically stored as one or more files. Database 1 12 includes one or more tables 114a-z, which are compilations of information. Generally, tables are conceptualized as being organized in rows and columns, although the storage format may be different. Query definitions 1 17 includes one or more queries 1 18a-z, which are specifications of operations that are to be performed on one or more tables 1 14 in database 112 or on one or more materialized views 116. Queries are typically written using SQL. Materialized views 1 15 includes one or more materialized views 1 16a-z, each of which is the result of a query that has been performed. Each view is a compilation of information, similar in structure to a table. A view that has been stored is usable, like a table, as an input to a query. Such a view is termed a materialized view. A query may therefore be performed on one or more materialized views, or on a combination of materialized views and tables. SQL Engine 113 is a software module that performs the operations specified by the queries and generates the views.

The relationship among tables, queries and views is shown in Fig. lb. Query 1 18a is a specification of an operation that selects data from one or more tables, such as 1 14a and 114b, and/or views, such as 116a and 1 16b, and of operations to be performed on the selected data, such as finding the largest value (MAX), the smallest value (MIN), the sum of the selected data (SUM), the average of the selected data (AVG), etc. The query 1 18a and the tables 1 14a and 1 14b and views 1 16a and 1 16b that are specified by the query are input to SQL engine 1 13, which evaluates the query to generate the resulting view 1 19. The result of a query represents a particular way of looking at the data in the input tables and or views, thus, the result of a query is termed a view 1 19.

An example of a database having two tables, and several queries and their resulting views, is described below. This example is illustrative only; the present invention is in no way limited to the described example. EXAMPLE 1

Consider a data warehouse that holds information useful to a telephone company. The database maintains the following tables: Customer(Phone_Number, CustJVame) table 120, shown in Fig. lc, maintains information about individual customers of the telephone company. Customer table 120 includes a plurality of rows, such as rows 123a-b. Each row corresponds to one data record, including one or more data fields. The data fields are organized into two columns, Phone Number column 121 and Cust Name column 122. Each column corresponds to a particular piece of data that is included in each data record. Thus, each row in Customer table 120 has, in its Phone Number column 121 , the phone number corresponding to the customer named in the Cust Name column 122.

Likewise, Calls(From, To, Time, Day, Month, Year, Duration, Planjd, Charge) table 130, shown in Fig. Id, which maintains information about each individual call includes a plurality of rows, such as rows 140a-b and nine columns 131- 139. Column 131, From, contains the phone number from which the call was placed. Column 132, To, contains the phone number to which the call was placed. Column 133, Time, contains the time at which the call was placed. Column 134, Day, contains the day on which the call was placed. Column 135, Month, contains the month in which the call was placed. Column 136, Year, contains the year in which the call was placed. Column 137, Duration, contains the duration of the call. Column 138, Planjd, contains an identifier of the calling plan applicable to the call. Column 139, Charge, contains the charge for the call.

Assuming that the telephone company is interested in determining calling plans that have earned more than a million dollars in one of the years between 1990 and 1995. The following SQL query Q\ may be used for this purpose:

Qi : SELECT Year, Plan_Name, SUM(Charge) FROM Calls. Calling_Plans

WHERE Calls.PlanJd = Calling _Plans. Planjd AND Year ≥ 1990 AND Year ≤1995

GROUPBY Year, Plan Jlame HAVING SUM(Charge) > 1, 000, 000 The telephone company also maintains materialized views that summarize the performance of each of their calling plans on a periodical basis. In particular assume that the following materialized view V j(PlanJd,Month, Year, Earnings) is available:

V SELECT Plan-ID, Month, Year, SUM(Charge) FROM Calls

GROUPBY Planjd, Month, Year

View Vj can be used to evaluate the query Qj by joining Vj with the table

Calling JPlans, collapsing multiple groups corresponding to the monthly plan earnings into annual plan earnings, and enforcing the additional conditions to get the summaries of plans earning more than a million dollars in one of the years between 1990 and 1995. The rewritten query Qj that uses V\ is:

Q. SELECT Year, Plan_Name,SUM(Earnings)

FROM Vi, Calling JPlans

WHERE V i .Planjd = Calling_Plans.PlanJd AND Year ≥ 1990 AND Year ≤ 1995

GROUPBY Year, Pla _Name

HAVING SUM(Earnings) > 1, 000, 000

The Calls table may be huge, and the materialized view Vj is likely to be orders of magnitude smaller than the Calls table. Hence, evaluating Qj will be much faster than evaluating Qj, emphasizing the importance of recognizing that Ql can be rewritten to use the materialized view V] . Consider now the case where, instead of V\ , the telephone company maintains the materialized view Vj (Plan-Id, Month, Year, Earnings), summarizing the performance of their calling plans only since 1991 :

V, : SELECT Plan-ID, Month, Year, SUM(Charge)

FROM Calls

WHERE Year ≥ 1991

GROUPBY Plan Id, Month, Year

View V, can still be used to evaluate query Q\ . However, not all the tuples in Qi can be computed using Vj : the summary information computation for 1990 would have to access the Calls table, and the rewritten query Qj : involves a UNION ALL.

Q^', : SELECT Year, PlanJJame, SUM(Earnings)

FROM V; , Calling _Plans

WHERE V; .Planjd = Calling _Plans.PlanJd

AND Year ≤1995

GROUPBY Year, Plan ϊame

HAVING SUM(Earnings) > 1,000,000

UNION ALL

SELECT Year, Plan fame, SUM(Charge)

FROM Calls, Calling Plans

WHERE Calls.PlanJd = Calling _Plans. Planjd

AND Year = 1990

GROUPBY Year, Plan Name HAVING SUM(Charge) > 1,000,000

Evaluating Qj will still be faster than evaluating Q\ , even though it involves accessing the Calls table. A process for rewriting a query is shown in Fig. 2. The process begins with step 202, in which the original query, which is to be rewritten, and the views that are to be used, are provided. One view is selected to be analyzed first. In step 204, the view is semantically analyzed to determine whether it is usable in evaluating the original query. The semantic analysis involves two parts. First, the view is analyzed to determine whether the view projects out any needed columns. Second, the view is analyzed to determine whether the view discards any tuples that satisf a condition enforced in the original query. If a view V is usable in evaluating a query Q, then V must "replace" some of the tables and conditions enforced in Q: other tables and conditions from Q must remain in the rewritten query Q' . The rewritten query Q¹ can be a single-block query, or a multi-block query that is a UNION ALL of single-block queries. For view V to be usable in answering query Q, such that Q' is a single-block query, it must be the case that:

V does not project out any columns needed by Q.

A column A is needed by Q if it appears in the result of Q or if Q needs to enforce a condition involving A that has not been enforced in the computation of V.

V does not discard any tuples needed by Q.

A tuple is needed by Q if it satisfies the conditions enforced in Q. When Q' can be a multi-block query, the second requirement can be somewhat relaxed to require that V not discard any tuples needed for some of the groups in Q. In step 206, it is determined whether the view is usable, based on the results of step 204. If the view is not usable, then in step 214, the next view from among those provided is selected to be analyzed. If the view is usable, then in step 208, the query is rewritten to form a multi-set equivalent query that uses the view. In step 210, it is determined whether the iteration of the selected view is complete. If it is not, then the process loops back to step 204, in which the rewritten query is semantically analyzed. As long as the view remains usable, a rewritten query is generated for each iteration. If the iteration is complete, then in step 212, it is determined whether all provided views have been analyzed. If not, then in step 214, the next provided view is selected. The process then loops back to step 204 and the newly selected view is iteratively analyzed using the current rewritten query. If, in step 212, all provided views have been analyzed, then in step 216, the final rewritten query that results from the process is evaluated.

NOTATION AND DEFINITIONS

We consider SQL queries and views with grouping and aggregation. Queries can either be single-block queries (described below) or union multi-block queries that are the UNION ALL (i.e., additive multiset union) of single-block queries. A view is defined by a query, and the name of the view is associated with the result of the query. In this document, we consider only views defined by single-block queries. We give the form as well as a simple example of a single- block query below:

Q: SELECT Sel(Q) FROM R- (A Rβ

WHERE Conds(Q) GROUPBY Groups(Q)

HAVING GConds(Q)

Qe: SELECT A, MAX(D), SUM(E)

FROM R(A,B), S(C,D,E)

WHERE B = C

GROUPBY A, B HAVING SUM(D) > 1000

For notational convenience, we modify the naming convention of standard

SQL to guarantee unique column names for each of the columns in a single-block query. For example, let R\ and R2 be two tables each with a single column named A. If a single-block query Q has both R\ and R2 in its FROM clause, our notation would replace them by R](Aι) and R2(A2). Every reference to Rj .A in Q is replaced by Ai , and every reference to R2-A in Q is replaced by A2. Similarly, if a single-block query Q has two range variables R\ and R2 ranging over table R in its FROM clause, our notation would replace them by R(Aj) and R(A2). Every reference to Rj .A in Q is replaced by A] , and every reference to R2-A in Q is replaced by A2. We use TablesiQ) to denote the set of tables (along with their columns)

{R, (,?. ),..., Λ„( „)} in the FROM clause of a single-block query Q, and ColsiQ) to denote A. U***U A_n , i.e., the set of columns of tables in TablesiQ). In the example of query Q_e, TablesiQ^) is (R(A,B),S(C,D,E)} and ColsiQ_e) is {A,B,C,D,E} .

The set of columns in the SELECT clause of Q, denoted by SeliQ), consists of: (a) non-aggregation columns: this is a subset of the columns in ColsiQ): and is denoted by ColSel(Q); and (b) aggregation columns: these are of the form AGG(Y), where Y is in ColsiQ) and AGG is one of the aggregate functions MIN, MAX, SUM and COUNT. The set of columns that are aggregated upon, such as Y above, is a subset of ColsiQ), and is denoted by AggSeliQ). In the example of query Q_e, SW(Q_e) is {A,MAX(D),SUM(E)}, ColSeliQ_e) is {A} and AggSel(Q_e) is {D,E} . The grouping columns of query Q, denoted by GroupsiQ), consists of a subset of the columns in ColsiQ). SQL requires that if GroupsiQ) is not empty, then ColSeliQ) must be a subset of GroupsiQ). In the example of query Q_e, GroupsiQ_e) is {A,B} and CøΛSW(Q_e) is a proper subset of GroupsiQ_e).

We consider built-in predicates that are arithmetic predicates of the form α op β, where op is one of the comparison predicates {<,<,=,>,>} , and α and β are terms formed from columns of tables, aggregation columns, and constants using the arithmetic operations +, - and *.

The conditions in the WHERE clause of query Q, denoted by CondsiQ), consists of a Boolean combination of built-in predicates formed using columns in ColsiQ) and constants. The conditions in the HAVING clause of query Q, denoted by GCondsiQ), consists of a Boolean combination of built-in predicates formed using columns in GroupsiQ), aggregation columns of the form AGG(Y) where Y is in ColsiQ), and constants. In the example of query Q_e, CondsiQ_c) is B = C, and GCondsiQ_t) is SUM(D)>1000. Given a single-block query Q, if GroupsiQ), AggSeliQ) and GCondsiQ) are non-empty (Note that each of GroupsiQ), AggSeliQ) and GCondsiQ) can be empty without the other two being empty.), then Q is referred to as an aggregation query.

Determining that a single-block view V is usable in evaluating a single- block query requires (as we show later in the paper) that we consider mappings from V to Q. These are specified by column mappings, defined below. Column Mapping

A column mapping from a single-block query Q_a to a single-block query Qb is a mapping ψ from ColsiQ-) to ColsiQ^) such that if R(A_j ,...,A_n) is a table in TablesiQ_a), then: ( 1 ) there exists a table R(B ι ,...,B_n) in TablesiQ^), and (2) B. = (A. ),l ≤i≤n .

A 1 - 1 column mapping φ is a column mapping from Q_a to Qb such that distinct columns in ColsiQ^) are mapped to distinct columns in ColsiQ^). Otherwise, the column mapping is a many-to-1 column mapping.

As a shorthand, if R is a table in TablesiQ_a), we use φ(R(Aι,...,A_n)) to denote R(φ(A} ),..., φ(A_n)), where A j A_n are columns in ColsiQ_a). We use similar shorthand notation for mapping query results, sets and lists of columns, sets of tables, and conditions.

We formalize the intuitive notation of "usability" of view V in evaluating query Q as finding a rewriting of Q, defined below. In this paper, we consider only rewritings that are either single-block queries or multi-block queries that are UNION ALLS of single-block queries. For example, rewriting Qj in Example 1.1 is a single block query, whereas rewriting Qj in the same example is a multi- block query that is a UNION ALL of single-block queries. Rewriting of a Query A query Q' is a rewriting of query Q that uses view V if: (1) Q and Q' are multiset-equivalent, i.e., they compute the same multiset of answers for any given database, and (2) Q' contains one or more occurrences of V in the FROM clause of one of its blocks.

In the sequel, we say that view V is usable in evaluating query Q, if there exists a single-block or a union multi-block query Q' such that Q' is a rewriting of Q that uses V. When the rewritten query can be a multi-block query, there is a certain trivial sense in which any view V is usable in evaluating a given query Q — the rewritten query can be the UNION ALL of Q itself and a single-block query in which V occurs in the FROM clause and which has an unsatisfiabie conjunction of built-in predicates in the WHERE clause. Further, when Q is unsatisfiabie, any rewriting of Q would also have to be unsatisfiabie. Dealing with these and other such possibilities would complicate our presentation without aiding our understanding of the problem. Hence, we consider satisfiable queries and views, and do not permit multi-block rewritings where any block is unsatisfiabie.

AGGREGATION QUERY AND CONJUNCTIVE VIEWS

In this section we consider the problem of using single-block conjunctive views to evaluate a single-block query with grouping and aggregation. Using a single-block view to evaluate a multi-block query can be achieved by independently testing usability of the view in evaluating each block of the multi- block query separately.

We formalize these intuitions below, show that they yield both necessary and sufficient conditions for certain kinds of queries, and present an algorithm to rewrite Q using V. We first examine the case when the query does not have a HAVING clause, and then describe the effect of the HAVING clause on the conditions for usability and the rewriting algorithm.

Aggregation Query Without A HAVING Clause Single-block Rewritten Query The conditions for usability of a single-block view V in evaluating a single-block query Q, such that the rewritten query Q' is a single-block query, are presented formally in Fig. 3 in terms of column mappings. Note that the conditions apply also to the restricted case when both the view and the query are conjunctive.

Condition C \ 302 and the first part of condition C4 308 essentially guarantee that the view is multiset equivalent to its image under φ: these are a reformulation of the conditions presented in S. Chaudhuri and M. Y. Vardi. "Optimization of real conjunctive queries", In Proc. ACM PODS, 1993 for testing equivalence of conjunctive queries under the multiset semantics. Note that the 1- 1 mapping is necessary because of the multiset semantics, whereas a many-to-1 mapping would suffice in the case of sets. Condition C4 308 ensures that constraints not enforced in the view can still be enforced in the query when the view is used, since they do not refer to columns that are projected out in the view and hence are no longer available. Conditions C2 304 and C3 306 ensure that the view does not project out any columns that are required in the SELECT clause of the query. If conditions C1-C4 302-308 are satisfied, the rewritten query Q' is obtained from Q by applying process ConjViewSingleBIock 400, shown in Fig. 4. Process 400 begins with step S\ 402, in which all the tables in φ(Tables(V)) are replaced by φ(V). In step S2 404, each column A in Groups(Q) I7p ColSel(Q) ftp AggSel(Q) are replaced by φ(B _ ), where B ^ satisfies conditions C2 304 and C3 306, part 1. In step S2 404-406, a Boolean combination of built-in predicates Conds' satisfying condition C4 308 is determined. CondsiQ) in Q is then replaced by Conds' . In step S4 408, COUNT(A) is replaced by COUNT(B), where B is any column in φ(V). COUNT(A) is an aggregation column in Sel(Q) such that A is in isf(Cols(V)), but not in φtSelrø. Theorem 3.1 Let Q be a single-block aggregation query without a HA VING Clause, and let V be a single-block conjunctive view.

If conditions CJ-CJ are satisfied, V is usable in evaluating Q . In that case Q', obtained by applying algorithm ConjViewSingleBlock is a rewriting of Q using V.

If Conds(Q) and Conds(V) contain only equality predicates of the form A =B, where A and B are column names or constants, and the rewritten query is required to be a single-block query, V is usable in evaluating Q only if conditions C1-C4 are satisfied.

The following example illustrates conditions C1-C4 302-308 and process ConjViewSingleBlock 400 for obtaining a single-block rewritten query.

EXAMPLE 2 Consider the telephone company database from Example 1. The following query Q2 can be used to determine the total earnings of each calling plan as well as the total number of calls charged under each calling plan in December 1995. Q₂: SELECT PNj, SUM(Cj) ,COUNT(Cj)

FROM CallsfFj, Tj , TIj, Dj, M], Yj, DU], Pj, Cj), Calling _Plans(Pl j, PNj)

WHERE Pj = PI] AND Yj = 1995 AND Mi = 12 GROUPBY PN]

Assume that the telephone company maintains call data for December 1995 as the view V2 below: V₂: SELECT F₂. T₂, TI₂, D₂, M₂, Y₂, DU₂, P₂, C₂

FROM Calls (F₂, T₂, TI₂, D₂, M₂, Y₂, DU₂, P₂, C^

WHERE Y₂ -= 1995 and M₂ = 12 View V2 can be used to evaluate query Q2 since conditions C 1 -C4 are satisfied: (C] ) The 1-1 column mapping £ from V2 to Q2 is {F₂→F ι ,T₂→T₁ ,Tl2 →TIι ,D₂ - D] .M₂ →Mj ,Y₂ ->Y] ,DU →DUι .P₂ - P l ,C2 - Cι } . (C2) Trivially satisfied. (C3) For column C \ , B_c is the column C2 in Sel(V2). (C4) Conds' is given by ?\ = PI] . The single-block rewriting of Q2 that uses V2 is: Q SELECT PNj, SUM(Cj), COUNT(Cj)

FROM V₂(Fj, Tj, TIi, Dj, M_}, Yj, DUj, Pj, Cj),

Calling_Plans(PIj, PNj) WHERE P} = PIι

GROUPBY PNj

Multi-Block Rewritten Query

When the rewritten query is not required to be a single-block query, but can be a multi-block query that is a UNION ALL of single-block queries, additional usage of views in evaluating queries are possible.

The conditions for usability of a single-block view V in evaluating a single-block query Q, when Q' can be a multi-block rewritten query, are similar to the conditions for usability when Q' has to be a single-block query. In particular conditions C1-C3 302-306 are unchanged. Condition C4 308 has to be modified to reflect the possibility that V can be used to compute only some of the tuples of Q. The modified condition C™ 500 is formally presented in Fig. 5.

Intuitively, given a view V that satisfied condition C\ 302 query Q can always be reformulated as a UNION ALL of 2 single-block queries Q_a and Q -, that differ from Q (and from each other) only in their WHERE clauses such that (1 ) CondsiQa) is equivalent to CondsiQ) & φ(Co«ώ(V)), and (2) C<wκώ(Qb) is equivalent to CondsiQ) & -,φiConds(V)).

View V can be potentially used to evaluate Q_a, but clearly not Qb- Conditions C1-C3, 302-306 and parts 1, 2(a) and 2(b) of condition C₄ ^m 500 essentially check whether view V can be used to evaluate Q_a The reformulation of Q as the UNION ALL of Q_a and Qb, however, does not always preserve the semantics of Q. To preserve the semantics, it must be guaranteed that Q_a and Qb do not compute tuples for the same group of Q - part 2(c) of condition C™ 500 embodies this requirement. If conditions C1 -C3 302-306 and C₄ ^m 500 are satisfied, the multi-block rewritten query Q' is obtained, using process ConjViewMultiBlock 600, shown in Fig. 6.

Process 600 begins with step 602, in which iCondsiV)) is used to split Q into Q_a and Q , such that CondsiQ-*.) is equivalent to CondsiQ) & φ(Co/ιώ(V)), and CondsiQ^) is equivalent to CondsiQ) and ^~-§iConds(V)). In step 604, process ConjViewSingleBlock is used to rewrite Q_a to make use of view V, resulting in the single-block query Q_a . In step 606, if Qb is satisfiable, the multi-block query Q' that is the rewriting of Q using V is the UNION ALL of Qj, and Qb_* Else Q' is the same as Q. .

Theorem 3.2

Let Q be a single-block aggregation query without a HA VING clause, and let V be a single-block conjunctive view.

If conditions C]-C and C™ are satisfied, V is usable in evaluating Q. In that case Q\ obtained by applying algorithm ConjViewMultiBlock, is a multi- block rewriting ofQ using V. Multiple Uses of Views

Often a query can make use of multiple views, or the same view times. The multiple rewriting algorithms ConjViewSingleBlock and ConjViewMultiBlock presented above can be used to incorporate multiple uses of views. To obtain rewritings with multiple views we create successive rewritings Q_a ,...,Q_n , where each rewriting is obtained from the previous one by testing conditions C 1-C3 302-306 and either C4 308 or C™ 500 (depending on the form of the rewriting desired), and applying the corresponding rewriting algorithm. At each successive rewriting, the views incorporated in previous rewritings are treated as database tables rather than being expanded using their view definitions.

Theorem 3.3 Let Q be a single-block aggregation query without a HA VING clause, and let V_a,... V_m be single-block conjunctive views. Then the following hold:

1. An iterative application of algorithm ConjViewSingleBlock is sound, i.e., each successive rewriting is multiset-equivalent to Q.

2. An iterative application of algorithm ConjViewMultiBlock is sound, i.e., each successive rewriting is multiset-equivalent to Q.

3. The rewriting algorithm ConjViewSingleBlock is order- independent. That is, if there is a single-block rewriting of Q that uses each of V_a,... V_m then the result of rewriting Q to incorporate views V_a,... V_m, would be the same regardless of the order in which the views are considered. 4. If Conds(Q), Conds(V_a),...,Conds(V_m) contain only equality predicates of the form A = B, where A and B are column names, or constants, and the rewritten query is required to be a single-block query, then the iterative application of algorithm ConjViewSingleBlock is complete. That is, any rewriting of Q that uses one or more of V_a V_m can be obtained by iterativelv applying algorithm ConjViewSingleBlock.

It is important to note that, for the case of equality predicates, the iterative application of ConjViewSingleBlock guarantees that we find all ways of using the views to answer a query, provided the rewritten query is required to be a single-block query.

AGGREGATION QUERY WITH A HAVING CLAUSE We now describe how to extend the previous algorithms to the case in which the queries may contain a HAVING clause. We only consider the case when the rewritten query is required to be a single-block query. The case when the rewritten query can be a multi-block query is a straightforward extension, along the lines described for aggregation queries without HAVING clauses. We first describe how to extend our usability conditions to accommodate the HAVING clause, and then show how we can use various transformations on the query that can cause the conditions to be satisfied in a larger number of cases.

Intuitively, when the single-block query Q has a HAVING clause, the conditions for usability of a conjunctive view V in evaluating Q and the rewriting algorithm ConjViewSingleBlock need to be extended to account for:

Conditions in GConds(Q) that must be satisfied by the query, in addition to conditions in CondsiQ), ^and

Aggregation columns of the form AGG(Y), that occur in GCondsiQ), but not in Sel(Q). To accommodate such conditions we modify C3 306 to also consider arguments that appear in GCondsiQ). The extended condition, Cξ 700, is formally presented in Fig. 7. If Q and V satisfy conditions C \ 302, C2 304, C 700 and C4 308, the single-block rewritten query Q' is obtained using the algorithm HavingConjViewSingleBlock, comprising steps 802, 804 and 806, presented in Fig. 8.

Theorem 3.4

Let Q be a single-block aggregation query with a HA VING clause, and let V be a single-block conjunctive view.

If conditions C], C₂, C₃' and C4 are satisfied, V is usable in evaluating Q. In that case Q' , obtained by applying algorithm HavingConjViewSingleBlock, is a rewriting ofQ using V.

Strengthening the Conditions in the Query

When query Q has a HAVING clause, the conditions in its HAVING clause may enable us to strengthen the conditions in the WHERE clause, without affecting the result of the query. Strengthening the conditions in the WHERE clause may allow us to detect usability of views that would otherwise not be determined to be usable, because it makes it more likely that condition C4 308 will be satisfied.

Several authors have considered the problem of inferring conditions that can be conjoined to CondsiQ) given the conditions in GCondsiQ), and removing redundant conditions in GCondsiQ). These techniques can be applied to rewrite the query Q as a pre-processing step, yielding possibly modified conditions CondsiQ) ^and GCondsiQ). The modified CondsiQ) a d GCondsiQ) are then used in checking conditions C2 304, C$ 700, and C4 308. EXAMPLE 3

Consider again the telephone company database from Example 1.1. The following query Q3 can be used to determine, for each customer, the maximum charge for a single call under the calling plan "TrueUniverse" in December 1995, provided the charge exceeds $10.

Q . SELECT Fj,MAX(Cj)

FROM Calls(Fj, Tj, TIJ,DJ,MJ, YJ,DUJ,PJ, Cj),

Calling J^>lans(Pl j ,PNj) WHERE Pj = PIj AND PNj = "TrueUniverse "

AND Yj •*^■= 1995 AND Mj = 12 GROUPBY F; HA VING MAX(C ) > 10

Assume that the telephone company maintains detailed call data for 1995, for calls whose charge exceeds $1, as the view V3 below:

V₃: SELECT F₂, T₂, TI₂,D₂,M₂, Y₂,DU₂,P₂,C₂

FROM CallsiF₂, T₂, TI₂,D₂,M₂, Y₂,DU₂,P₂, CJ

WHERE Y₂ = 1995 AND C₂ > 1 Although the WHERE clause of Q3 does not enforce any conditions on the

Charge column, while the WHERE clause of V3 does, V3 can still be used to evaluate Q3. This is because the condition MAX(Cj) > 10 in the HAVING clause of Q3 is equivalent to having the condition C\ >10 in the WHERE clause of Q3. Strengthening CondsiQ^) by conjoining C \ >10 (and subsequently removing the redundant HAVING clause) allows the detection of usability of V3 in evaluating Q3 The rewriting of Q3 that uses V3 is: Q₃ : SELECT FJ,MAX(CJ)

FROM V₃ (FJ, TJ, TIJ,DJ,MJ, YJ,DUJ,PJ,CJ),

Calling _Plans(PIj, PNj ) WHERE Pj = PIj AND PNj = ^•'TrueUniverse " AND Mj = 12 AND Cj > 10

GROUPBY F;

Note that view V3 cannot be used to answer query Q2 (from Example 3.1) since conditions C4 308 and C™ 500 are violated — in particular V3 enforces the condition C2 304 >1, which results in the discarding of Calls tuples needed by Q₂*

AGGREGATION QUERY AND VIEWS

In this section we consider the problem of using single-block views in evaluating single-block queries when both the view and the query have grouping and aggregation. We only consider the case when the rewritten query is required to be a single-block query.

Recall that the two intuitive requirements for the usability of a conjunctive view V in answering a single-block aggregation query Q are that V not project out columns needed in Q, and that V not discard tuples needed in Q. In the presence of grouping and aggregation in the view, these requirements become more subtle: An aggregation over a column in V can be thought of as though that column was partially projected out, since V contains just aggregate values over that column, not the original column values themselves.

A GROUPBY in V results in the multiplicities of the tuples being lost. However, as the following examples illustrate, in some cases it is possible to overcome the difficulties introduced by grouping and aggregation in the view. EXAMPLE 4 (Coalescing Subgroups)

The following example illustrates that the aggregate information in a view may be sufficient to compute the aggregate information needed in the query. Consider the telephone company database from Example 1. The following query Q4 can be used to determine the total earnings of various calling plans as well as the maximum charge under each calling plan in 1995. Q : SELECT ?j,PNj SUM(Cj), MAX(Cj)

FROM Calls(Fj, Tj, TIJ,DJ,MJ, YJ,DUJ,PJ,CJ), Calling_Plans(PIj,PNj)

WHERE Pj = PIj AND Yj = 1995 GROUPBY ?J, PNJ

Assume that the telephone company also maintains information giving the total earnings as well as the maximum charge of each calling plan in each month in the form of view V4 below:

V₄: SELECT ?₂,M2, Y2 SUMfC^, MAXfC-y)

FROM Calls(F₂, T₂, TI₂,D₂,M₂, Y₂,DU₂,P₂, C-y),

GROUPBY ? , M₂, Y₂

View V4 groups the table Calls by the Plan ld.Month, and Year columns, and computes aggregate information on each such group. Query Q4, on the other hand, groups the table Calls only on the Planjd column, resulting in more coarse groups than those computed in V4. However, the aggregate information of the Planjd groups in Q4 can be computed by further aggregating the aggregate information computed for the iPlan-Id, Month, Year) groups in V4 as illustrated in the following rewritten query:

Q₄ SELECT VJ.PNJ SUM(MEj), MAX(MC ) FROM V_a (Pj_ιMj, Yj,MEj,MCj),

Calling_Plans(PIj,PNj ) WHERE P = PIj AND Yj = 1995 GROUPBY ?J, PNJ The following example illustrates that the existence of other columns in the view may enable us to recover the tuple multiplicities lost because of grouping in the view.

EXAMPLE 5 (Recovery of Lost Multiplicities) Consider again the telephone company database from Example 1. The following query Q5 can be used to determine the total number of calls under each calling plan in 1995: Q₅: SELECT P J,COUNT(CNJ)

FROM Calls(Fj_y Tj, Tl ,DJ,MJ, Yj, DUJ PJ_> Cj) Customer (PNj, CNJ )

WHERE Fj = PNj AND Yj = 1995 GROUPBY P;

View V5_a below maintains the total annual revenue for each customer, plan, and year: V_5a: SELECT F^P^ Y^SUMfC^

FROM CallsiF₂, T₂, TI₂,D₂,M₂, Y₂,DU₂,P₂, Cz)

WHERE F₂,P₂, Y₂

V5_a cannot be used to evaluate Q5. This is because the multiplicity of the From column of Calls is needed in order to compute COUNT(CNι ), but that multiplicity is lost in the view V5_a. However, consider view V5 below. V_5b: SELECT F₂,P₂, Y₂, SUMfC^, COUNTfC^

FROM Calls(F₂, T₂, TI₂,D₂,M₂, Y₂,DU₂,P₂, Cz) GROUPBY F₂,P₂, Y₂

Although the multiplicities of the From column are not explicit in V5b, they can be computed using the available information, V5b can be used to evaluate Q5 as follows: Q₅ SELECT Pj,SUM(GCj)

FROM V_5b(Fj,Pj, Yj, YEJ.CGJ),

Customer(PNj,CNj) WHERE Fj = PNj AND Yj = 1995 GROUPBY Pj As the examples illustrate, to use views that involve aggregations, we need to verify that (a) the aggregate information in the view is sufficient to compute the aggregates needed in the query, and that (b) the correct multiplicities exist or can be computed. We formalize these intuitions below, present conditions for usability, and provide an algorithm to rewrite Q using V.

WITHOUT HAVING CLAUSES

To specify conditions for usability for single-block aggregation views, we need to slightly modify conditions C2 304 and C4 308 and to substantially modify condition C3 306 to deal with the different cases of aggregates appearing in the SELECT clause of the query. (Condition C j 302 is unchanged.) The modified conditions are formally presented in Fig. 9.

Since ColSeliQ) must be a subset of GroupsiQ), condition C 904 is a generalization of condition C2 304. Intuitively, condition C* 906 guarantees that the columns in the view contain enough information to compute the aggregates required in the query. In particular, condition C* 906 parts 1(b), 1(c) and 2 guarantee that we can recover the multiplicities in the view in order to perform an aggregation that depends on such multiplicities (i.e., either SUM or COUNT). The two parts of the condition cover the cases when the aggregation is on a column mapped by the view, and not mapped by the view, respectively. Note that the second part of condition CJ 908 does not allow Conds' to constrain any of the columns in φ(AggSel(V)). Intuitively, this is because the columns in AggSel(V) are aggregated upon in view V, and hence are not available for imposition of additional constraints in the rewritten query Q' .

If conditions C - C₄ 902-908 are satisfied, the rewritten query Q' is obtained from Q by applying algorithm AggViewSingleBlock, presented in Fig. 10. Steps S^a 1002,S^a ₁₀₀₄,S₃ ^a1006 are similar to steps S\ 402, S2 404 and S3 406 of algorithm ConjViewSingleBlock. Steps S^a 1008 and S₅ ^a 1010 deal with the various kinds of aggregation that may occur in the view and the query.

Theorem 4.1 Let Q and V be single-block aggregation queries without HA VING clauses.

If conditions C -C₄ are satisfied, V is usable in evaluating Q. In that case, Q\ obtained by applying algorithm AggViewSingleBlock, is a rewriting of Q using V. EXAMPLE 6 Consider again the query Q4 and view V4 from Example 4.1. View V4 can be used to evaluate Q_a since conditions C^a -C₄ are satisfied. Condition C^a :

The 1-1 column mapping φ from V4 to Q4 is { F₂→ Fj,T₂ → T_j,TI₂ → TIj D₂ →D M → Mj Y₂ → Yj,DU₂ →DUj,P₂ →Pj,C₂ → C_jj. Condition C^a : For column P\ in Groups(Q4), B_P] is the column P2 in ColSeliV^).

Condition C^a :

For column SUM(Cj) in Sel(Q4), el(V4) contains column SUM(Cz), and for column MAX(Cj) in Sel(Q ), Sel(V4) contains column MAX(Cz). Condition C₄ :

Conds' is the same as CondsiQ^), i.e., P\ = PI J &YJ = 1995 since no conditions are enforced in V4.

The rewritten query Q₄ resulting from applying steps S^a - S₅ ^a is given in Example 4.1.

EXAMPLE 7 (Constraining AggSel(V)))

Consider again the telephone database from Example 1.1. The following Q can be used to determine the total earnings of various calling plans in 1995, considering only calls whose charge exceeds $1.

Q6* SELECT Pj.SUM(Cj)

FROM Calls(Fj,Tj,TIj,

WHERE Yj = 1995 AND Cj > 1

GROUPBY Pj

Let the view V be the same as view V4 (from Example 4.1):

V₆: SELECT P₂,M₂, Y₂,SUM(Cj),MAX(C₂)

FROM Calls(F₂, T , TI₂, D₂,M₂, Y ,DU₂,P_2> C^ GROUPBY P₂,M₂, Y₂ View V6 cannot be used to evaluate Q above, although in the absence of the condition "C j > 1" in the WHERE clause in Q , Vβ could be used to evaluate Q . Intuitively, this is because the built-in predicates in the query constrain the possible values of Cj and C2 is aggregated upon in the view V , no condition on the result of the SUM or the MAX in V can capture the effect of the condition on C ι in Q₆.

With HAVING Clauses

Essentially, the additional subtleties that must be considered involve the relationships between the GROUPBY and HAVING clauses in the view V and the query Q. Intuitively, the HAVING clause in V may eliminate certain groups in V (i.e., those that do not satisf GConds(V)). If any of these eliminated groups in V is "needed" to compute an aggregate function over a group in Q, by coalescing multiple groups in V, then V cannot be used to evaluate Q. Hence, condition C₄ 908 must be extended to test whether there exists GConds' such that GConds (Q) is equivalent to the combination of GConds(V) and GConds' , taking the grouping columns Groups(V) and Groups(Q) into account.

Before checking any of the conditions for usability, the query Q and view V can be independently preprocessed to "move" maximum sets of conditions from the HAVING clause to the WHERE clause, as discussed in Section 3.3; the resulting normal form allows independent comparison of Conds(Q) and Conds(V) , on the one hand, and of GConds(Q) and GConds(V), on the other.

The rewriting algorithm takes these additional refinements of the conditions of usability into account. Specifically, step S^a 1006 determines a GConds' in addition to Conds' , using GConds(V) and GConds(Q) (resulting from the preprocessing step). Steps S₄ 1008 and S^a 1010 are augmented to compute aggregation columns appearing in GConds(Q) in addition to those appearing in Sel(Q).

Conjunctive Query and Aggregation Views

Consider the case when the query Q is a conjunctive query (i.e., no grouping and aggregation), but the view V has grouping and aggregation. In this case the GROUPBY clause in the view results in losing information about the multiplicities of tuples, and view V cannot be used to evaluate Q if the multiset semantics is desired.

Theorem 5.1

Let Q be a conjunctive query, and V be a single-block aggregation view. Then, there is no single-block rewriting ofQ using V.

The following example illustrates the problem with conjunctive queries and aggregation views:

EXAMPLE 8

Consider the telephone company database from Example 1.1. The query Qη below is used to obtain information about calls exceeding an hour in duration:

Qη: SELECT FJ,DJ, MJ, YJ

FROM Calls(Fj, Tj, TIj, DJ.MJ, YJ,DUJ,PJ, Cj)

WHERE DU; >3600

The view V7 below counts the number of calls exceeding an hour in duration made by each caller on a daily basis:

V₇ : SELECT F₂,D₂, M₂, Y₂, COUNT (T₂ ) FROM Calls(F₂, T₂, TI₂, D ,M₂, Y₂.DU₂,P_2> C^

WHERE O ₂>3600

GROUPBY F₂,D₂,M₂, Y₂

There is a 1-1 column mapping from V7 to Qη, Sel(Vγ) contains all the columns required in Sel(Qγ), and the conditions enforced by the WHERE clauses are identical. Even through COUNTS) has the required multiplicity information, this information cannot be used in an SQL query to "replicate" the tuples in V7 the appropriate number of times. Thus there is no rewriting of Q7 that uses view V7. Although a specific embodiment of the present invention has been described, it will be understood by those of skill in the art that there are other embodiments which are equivalent to the described embodiment. Accordingly, it is to be understood that the invention is not to be limited by the specific illustrated embodiment, but only by the scope of the appended claims.

Claims

What is claimed is: 1. A method of evaluating a query having aggregation using a materialized view, comprising the steps of: semantically analyzing the materialized view to determine whether the materialized view is usable in evaluating an input query; if the view is usable, rewriting the input query to produce an output query that is multi-set equivalent to the input query and that specifies one or more occurrences of the materialized view as a source of information to be returned by the output query; and evaluating the output query.

2. The method of claim 1, wherein the semantically analyzing step comprises the steps of: determining that the materialized view does not project out any columns needed to evaluate the input query; and determining that the view does not discard any tuple that satisfies a condition enforced in the input query.

3. The method of claim 2, wherein: the steps of semantically analyzing and rewriting are iterated, with the output query of each iteration being the input query of the next iteration; and the evaluating step is performed after the last iteration.

4. The method of claim 2, wherein: there are a plurality of materialized views; the steps of semantically analyzing and rewriting are iterated at least once for each of the materialized views, with the output query of each iteration being the input query of the next iteration; and the evaluating step is performed after the last iteration.

5. The method of claim 4, wherein: the steps of semantically analyzing and rewriting are further iterated for a given one of the materialized views.

6. The method of claim 4, wherein each one of the plurality of materialized views is similar.

7. The method of claim 4, wherein at least one of the plurality of materialized views is different.

8. The method of claim 4, wherein each one of the plurality of materialized views is different.

9. The method of claim 1, wherein the materialized view does not have aggregation.

10. The method of claim 1, wherein the materialized view has aggregation.

11. A system for evaluating a query using a materialized view, comprising: a semantic analyzer analyzing the materialized view to determine whether the materialized view is usable in evaluating an input query; a query rewriting device rewriting the input query to produce an output query that is multi-set equivalent to the input query and that specifies one or more occurrences of the materialized view as a source of information to be returned by the output query, if the view is usable; and a query evaluator evaluating the output query.

12. The system of claim 1 1, wherein the semantic analyzer comprises: a first analysis unit determining that the materialized view does not project out any columns needed to evaluate the input query; and a second analysis unit determining that the view does not discard any tuple that satisfies a condition enforced in the input query.

13. The system of claim 12, wherein: the semantic analyzer and query rewriting device are further iteratively operable, with the output query of each iteration coupled to the input query of the next iteration; and the query evaluator is coupled to the output query of the last iteration.

14. The system of claim 12, wherein: there are a plurality of materialized views; the semantic analyzer and query rewriting device are further iteratively operable at least once for each of the materialized views, with the output query of each iteration coupled to the input query of the next iteration; and the query evaluator is coupled to the output query of the last iteration.

15. The system of claim 14, wherein: the semantic analyzer and query rewriting device are further iteratively operable for a given one of the materialized views.

16. The system of claim 14, wherein each one of the plurality of materialized views is similar.

17. The system of claim 14, wherein at least one of the plurality of materialized views is different.

18. The system of claim 14, wherein each one of the plurality of materialized views is different.

19. The system of claim 1 1, wherein the materialized view does not have aggregation.

20. The system of claim 1 1 , wherein the materialized view has aggregation.

21. A computer program storage device, comprising: a computer readable medium embodying computer program instructions for evaluating a query using a materialized view, including: computer program instruction means for semantically analyzing the materialized view to determine whether the materialized view is usable in evaluating an input query; computer program instruction means for rewriting the input query to produce an output query that is multi-set equivalent to the input query and that specifies one or more occurrences of the materialized view as a source of information to be returned by the output query, if the view is usable; and computer program instruction means for evaluating the output query.

22. The computer program storage device of claim 21 , wherein the computer program instruction means for semantically analyzing the materialized view comprises: computer program instruction means for determining that the materialized view does not project out any columns needed to evaluate the input query; and computer program instruction means for determining that the view does not discard any tuple that satisfies a condition enforced in the input query.

23. The computer program storage device of claim 22, further comprising: computer program instruction means for iteratively operating the computer program instruction means for semantically analyzing the materialized view and the computer program instruction means for rewriting the input query, with the output query of each iteration being the input query of the next iteration; and wherein the computer program instruction means for evaluating the output query operates after the last iteration.

24. The computer program storage device of claim 22, wherein there are a plurality of materialized views and the computer program storage device further comprises: computer program instruction means for iteratively operating the computer program instruction means for semantically analyzing the materialized view and the computer program instruction means for rewriting the input query at least once for each of the materialized views, with the output query of each iteration being the input query of the next iteration; and wherein the computer program instruction means for evaluating the output query operates after the last iteration.

25. The computer program storage device of claim 24, wherein: the computer program instruction means for iteratively operating the computer program instruction means for semantically analyzing the materialized view and the computer program instruction means for rewriting the input query are operable to iterate for a given one of the materialized views.

26. The computer program storage device of claim 24, wherein each one of the plurality of materialized views is similar.

27. The computer program storage device of claim 24, wherein at least one of the plurality of materialized views is different.

28. The computer program storage device of claim 24, wherein each one of the plurality of materialized views is different.

29. The computer program storage device of claim 21, wherein the materialized view does not have aggregation.

30. The computer program storage device of claim 21, wherein the materialized view has aggregation.