New! View global litigation for patent families

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

Method and system for using materialized views to evaluate queries involving aggregation Download PDF

Info

Publication number
US5897632A
US5897632A US08895024 US89502497A US5897632A US 5897632 A US5897632 A US 5897632A US 08895024 US08895024 US 08895024 US 89502497 A US89502497 A US 89502497A US 5897632 A US5897632 A US 5897632A
Authority
US
Grant status
Grant
Patent type
Prior art keywords
sub
query
view
block
views
Prior art date
Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
Expired - Lifetime
Application number
US08895024
Inventor
Shaul Dar
Hosagrahar Visvesvaraya Jagadish
Alon Yitzchak Levy
Divesh Srivastava
Current Assignee (The listed assignees may be inaccurate. Google has not performed a legal analysis and makes no representation or warranty as to the accuracy of the list.)
LinkedIn Corp
Original Assignee
AT&T Corp
Priority date (The priority date is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the date listed.)
Filing date
Publication date
Grant date

Links

Images

Classifications

    • GPHYSICS
    • G06COMPUTING; CALCULATING; COUNTING
    • G06FELECTRICAL DIGITAL DATA PROCESSING
    • G06F17/00Digital computing or data processing equipment or methods, specially adapted for specific functions
    • G06F17/30Information retrieval; Database structures therefor ; File system structures therefor
    • G06F17/30286Information retrieval; Database structures therefor ; File system structures therefor in structured data stores
    • G06F17/30386Retrieval requests
    • G06F17/30424Query processing
    • G06F17/30442Query optimisation
    • G06F17/30448Query rewriting and transformation
    • G06F17/30457Query rewriting and transformation to use cached/materialised query results
    • GPHYSICS
    • G06COMPUTING; CALCULATING; COUNTING
    • G06FELECTRICAL DIGITAL DATA PROCESSING
    • G06F17/00Digital computing or data processing equipment or methods, specially adapted for specific functions
    • G06F17/30Information retrieval; Database structures therefor ; File system structures therefor
    • G06F17/30286Information retrieval; Database structures therefor ; File system structures therefor in structured data stores
    • G06F17/30386Retrieval requests
    • G06F17/30424Query processing
    • G06F17/30477Query execution
    • G06F17/30483Query execution of query operations
    • G06F17/30486Unary operations; data partitioning operations
    • G06F17/30489Aggregation and duplicate elimination
    • YGENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
    • Y10TECHNICAL SUBJECTS COVERED BY FORMER USPC
    • Y10STECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
    • Y10S707/00Data processing: database and file management or data structures
    • Y10S707/99931Database or file accessing
    • YGENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
    • Y10TECHNICAL SUBJECTS COVERED BY FORMER USPC
    • Y10STECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
    • Y10S707/00Data processing: database and file management or data structures
    • Y10S707/99931Database or file accessing
    • Y10S707/99932Access augmentation or optimizing
    • YGENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
    • Y10TECHNICAL SUBJECTS COVERED BY FORMER USPC
    • Y10STECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
    • Y10S707/00Data processing: database and file management or data structures
    • Y10S707/99931Database or file accessing
    • Y10S707/99933Query processing, i.e. searching

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 a 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.

Description

RELATED APPLICATIONS

This application claims the benefit of U.S. Provisional Application Ser. No. 60/024,635, filed Aug. 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. Potarianos, 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 sub-expression 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 V1 in evaluating query Q1 in Example 1.1, nor the usability of view V1 ' in evaluating Q1 in the same example. Also, no formal guarantees of completeness are provided.

A related problem is studied in A. Gupta, I. S. 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. 1a is a block diagram of an exemplary database system with materialized views.

FIG. 1b is a block diagram of relationship among tables, queries and views.

FIG. 1c is an exemplary format of a database table.

FIG. 1d 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. 1a.

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. 1a. 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 110. 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 112, which is typically stored as one or more files. Database 112 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 117 includes one or more queries 118a-z, which are specifications of operations that are to be performed on one or more tables 114 in database 112 or on one or more materialized views 116. Queries are typically written using SQL. Materialized views 115 includes one or more materialized views 116a-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. 1b. Query 118a is a specification of an operation that selects data from one or more tables, such as 114a and 114b, and/or views, such as 116a and 116b, 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 118a and the tables 114a and 114b and views 116a and 116b that are specified by the query are input to SQL engine 113, which evaluates the query to generate the resulting view 119. 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 119.

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, Cust-- Name) table 120, shown in FIG. 1c, 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, Plan-- Id, Charge) table 130, shown in FIG. 1d, 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, Plan-- Id, 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 Q1 may be used for this purpose:

______________________________________Q.sub.1 :  SELECT     Year, Plan.sub.-- Name, SUM(Charge)  FROM       Calls, Calling.sub.-- Plans  WHERE      Calls.Plan.sub.-- Id = Calling.sub.-- Plans.Plan.sub.--             Id             AND Year ≧ 1990 AND Year ≦ 1995  GROUPBY    Year, Plan.sub.-- Name  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 V1 (Plan-- Id,Month,Year,Earnings) is available:

______________________________________V.sub.1 :  SELECT      Plan-ID, Month, Year, SUM(Charge)  FROM        Calls  GROUPBY     Plan.sub.-- Id, Month, Year______________________________________

View V1 can be used to evaluate the query Q1 by joining V1 with the table Calling-- Plans, 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 Q1 that uses V1 is:

______________________________________Q.sub.1 '  SELECT      Year, Plan.sub.-- Name, SUM(Earnings)  FROM        V.sub.1, Calling.sub.-- Plans  WHERE       V.sub.1.Plan.sub.-- Id = Calling.sub.-- Plans.Plan.sub.              -- Id              AND Year ≧ 1990 AND Year ≦ 1995  GROUPBY     Year, Plan.sub.-- Name  HAVING      SUM(Earnings) > 1,000,000______________________________________

The Calls table may be huge, and the materialized view V1 is likely to be orders of magnitude smaller than the Calls table. Hence, evaluating Q1 will be much faster than evaluating Q1, emphasizing the importance of recognizing that Q1 can be rewritten to use the materialized view V1.

Consider now the case where, instead of V1, the telephone company maintains the materialized view V1 (Plan-Id, Month, Year, Earnings), summarizing the performance of their calling plans only since 1991:

______________________________________V.sub.1 ':   SELECT      Plan-ID, Month, Year, SUM(Charge)   FROM        Calls   WHERE       Year ≧ 1991   GROUPBY     Plan.sub.-- Id, Month, Year______________________________________

View V1 can still be used to evaluate query Q1, However, not all the table in Q1 can be computed using V1 ': the summary information computation for 1990 would have to access the Calls table, and the rewritten query Q1 ": involves a UNION ALL.

______________________________________Q.sub.1 ": SELECT     Year, Plan.sub.-- Name, SUM(Earnings) FROM       V.sub.1 ', Calling.sub.-- Plans WHERE      V.sub.1 '.Plan.sub.-- Id = Calling.sub.-- Plans.Plan.sub.            -- Id            AND Year ≦ 1995 GROUPBY    Year, Plan.sub.-- Name HAVING     SUM(Earnings) > 1,000,000            UNION ALL SELECT     Year, Plan.sub.-- Name, SUM(Charge) FROM       Calls, Calling.sub.-- Plans WHERE      Calls.Plan.sub.-- Id = Calling.sub.-- Plans.Plan.sub.--            Id            AND Year = 1990 GROUPBY    Year, Plan.sub.-- Name HAVING     SUM(Charge) > 1,000,000______________________________________

Evaluating Q1 " will still be faster than evaluating Q1, 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 satisfy 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.sub.1 (A.sub.1), . . . , R.sub.n (A.sub.n)      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 R1 and R2 be two tables each with a single column named A. If a single-block query Q has both R1 and R2 in its FROM clause, our notation would replace them by R1 (A1) and R2 (A2). Every reference to R1.A in Q is replaced by A1, and every reference to R2.A in Q is replaced by A2. Similarly, if a single-block query Q has two range variables R1 and R2 ranging over table R in its FROM clause, our notation would replace them by R(A1) and R(A2). Every reference to R1.A in Q is replaced by A1, and every reference to R2.A in Q is replaced by A2.

We use Tables(Q) to denote the set of tables (along with their columns) {R1 (A1), . . . ,Rn (An)} in the FROM clause of a single-block query Q, and Cols(Q) to denote A1 ∪ . . . ∪An, i.e., the set of columns of tables in Tables(Q). In the example of query Qe, Tables(Qe) is {R(A,B),S(C,D,E)} and Cols(Qe) is {A,B,C,D,E}.

The set of columns in the SELECT clause of Q, denoted by Sel(Q), consists of: (a) non-aggregation columns: this is a subset of the columns in Cols(Q): and is denoted by ColSel(Q); and (b) aggregation columns: these are of the form AGG(Y), where Y is in Cols(Q) 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 Cols(Q), and is denoted by AggSel(Q). In the example of query Qe, Sel(Qe) is {A,MAX(D),SUM(E)}, ColSel(Qe) is {A} and AggSel(Qe) is {D,E}.

The grouping columns of query Q, denoted by Groups(Q), consists of a subset of the columns in Cols(Q). SQL requires that if Groups(Q) is not empty, then ColSel(Q) must be a subset of Groups(Q). In the example of query Qe, Groups(Qe) is {A,B} and ColSel(Qe) is a proper subset of Groups(Qe).

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 Conds(Q), consists of a Boolean combination of built-in predicates formed using columns in Cols(Q) and constants. The conditions in the HAVING clause of query Q, denoted by GConds(Q), consists of a Boolean combination of built-in predicates formed using columns in Groups(Q), aggregation columns of the form AGG(Y) where Y is in Cols(Q), and constants. In the example of query Qe, Conds(Qe) is B=C, and GConds(Qe) is SUM(D)>1000.

Given a single-block query Q, if Groups(Q), AggSel(Q) and GConds(Q) are non-empty (Note that each of Groups(Q), AggSel(Q) and GConds(Q) 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 Mappings

A column mapping from a single-block query Qa to a single-block query Qb is a mapping φ from Cols(Qa) to Cols(Qb) such that if R(A1, . . . ,An) is a table in Tables(Qa), then: (1) there exists a table R(B1, . . . ,Bn) in Tables(Qb), and (2) Bi =φ(Ai),1≦i≦n.

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

As a shorthand, if R is a table in Tables(Qa), we use φ(R(A1, . . . ,An)) to denote R(φ(A1), . . . , φ(An)), where A1, . . . ,An are columns in Cols(Qa). 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 Q1 ' in Example 1.1 is a single block query, whereas rewriting Q1 " 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 unsatisfiable conjunction of built-in predicates in the WHERE clause. Further, when Q is unsatisfiable, any rewriting of Q would also have to be unsatisfiable. 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 unsatisfiable.

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 algorithms

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 C1 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 ConjViewSingleBlock 400, shown in FIG. 4. Process 400 begins with step S1 402, in which all the tables in φ(Tables(V)) are replaced by φ(V). In step S2 404, each column A in Groups(Q) ∪ ColSel(Q) ∪ AggSel(Q) are replaced by φ(BA), where BA 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. Conds(Q) 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 φ(Cols(V)), but not in φ(Sel(V)).

Theorem 3.1

Let Q be a single-block aggregation query without a HAVING Clause, and let V be a single-block conjunctive view.

If conditions C1 -C4 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.sub.2 :SELECT     PN.sub.1, SUM(C.sub.1), COUNT(C.sub.1)FROM       Calls(F.sub.1, T.sub.1, TI.sub.1, D.sub.1, M.sub.1,           Y.sub.1, DU.sub.1, P.sub.1, C.sub.1),           Calling.sub.-- Plans(PI.sub.1, PN.sub.1)WHERE      P.sub.1 = PI.sub.1 AND Y.sub.1 = 1995 AND M.sub.1 = 12GROUPBY    PN.sub.1______________________________________

Assume that the telephone company maintains call data for December 1995 as the view V2 below:

______________________________________V.sub.2 :  SELECT    F.sub.2, T.sub.2, TI.sub.2, D.sub.2, M.sub.2, Y.sub.2,            DU.sub.2, P.sub.2, C.sub.2  FROM      Calls (F.sub.2, T.sub.2, TI.sub.2, D.sub.2, M.sub.2,            Y.sub.2, DU.sub.2, P.sub.2, C.sub.2)  WHERE     Y.sub.2 = 1995 and M.sub.2 = 12______________________________________

View V2 can be used to evaluate query Q2 since conditions C1 -C4 are satisfied: (C1) The 1-1 column mapping φ from V2 to Q2 is {F2 →F1,T2 →T1,TI2 →TI1,D2 →D1,M2 →M1,Y2 →Y1,DU2 →DU1,P2 →P1,C2 →C1 }. (C2) Trivially satisfied. (C3) For column C1, BC.sbsb.1 is the column C2 in Sel(V2). (C4) Conds' is given by P1 =PI1.

The single-block rewriting of Q2 that uses V2 is:

______________________________________Q.sub.2 '  SELECT     PN.sub.1, SUM(C.sub.1), COUNT(C.sub.1)  FROM       V.sub.2 (F.sub.1, T.sub.1, TI.sub.1, D.sub.1, M.sub.1,             Y.sub.1, DU.sub.1, P.sub.1, C.sub.1),             Calling.sub.-- Plans(PI.sub.1, PN.sub.1)  WHERE      P.sub.1 = PI.sub.1  GROUPBY    PN.sub.1______________________________________
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 C4 m 500 is formally presented in FIG. 5.

Intuitively, given a view V that satisfied condition C1 302 query Q can always be reformulated as a UNION ALL of 2 single-block queries Qa and Qb, that differ from Q (and from each other) only in their WHERE clauses such that (1) Conds(Qa) is equivalent to Conds(Q) & φ(Conds(V)), and (2) Conds(Qb) is equivalent to Conds(Q) & φ(Conds(V)).

View V can be potentially used to evaluate Qa, but clearly not Qb. Conditions C1 -C3, 302-306 and parts 1, 2(a) and 2(b) of condition C4 m 500 essentially check whether view V can be used to evaluate Qa. The reformulation of Q as the UNION ALL of Qa and Qb, however, does not always preserve the semantics of Q. To preserve the semantics, it must be guaranteed that Qa and Qb do not compute tuples for the same group of Q--part 2(c) of condition C4 m 500 embodies this requirement. If conditions C1 -C3 302-306 and C4 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 φ(Conds(V)) is used to split Q into Qa and Qb, such that Conds(Qa) is equivalent to Conds(Q) & φ(Conds(V)), and Conds(Qb) is equivalent to Conds(Q) and φ(Conds(V)). In step 604, process ConjViewSingleBlock is used to rewrite Qa 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 Q'a and Qb. Else Q' is the same as Q'a.

Theorem 3.2

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

If conditions C1 -C3 and C4 m are satisfied, V is usable in evaluating Q. In that case Q', obtained by applying algorithm ConjViewMuliBlock, is a multi-block rewriting of Q using V.

Multiple Uses of Views

Often a query can make use of multiple views, or the same view multiple times. The 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 C1 -C3 302-306 and either C4 308 or C4 m 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 HAVING clause, and let Va, . . . Vm 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 ConjViewMuldiBlock 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 Va, . . .Vm then the result of rewriting Q to incorporate views Va, . . .Vm, would be the same regardless of the order in which the views are considered.

4. If Conds(Q), Conds(Va), . . . ,Conds(Vm) 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 Va, . . . ,Vm can be obtained by iteratively applying algorithm ConjyiewSingleBlock.

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 Conds(Q), and

Aggregation columns of the form AGG(Y), that occur in GConds(Q), but not in Sel(Q).

To accommodate such conditions we modify C3 306 to also consider Ad arguments that appear in GConds(Q). The extended condition, C3 h 700, is formally presented in FIG. 7. If Q and V satisfy conditions C1 302, C2 304, C3 h 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 HAVING clause, and let V be a single-block conjunctive view.

If conditions C1, C2, C3 h and C4 are satisfied, V is usable in evaluating Q. In that case Q', obtained by applying algorithm HavingConjViewSingleBlock, is a rewriting of Q 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 Conds(Q) given the conditions in GConds(Q), and removing redundant conditions in GConds(Q). These techniques can be applied to rewrite the query Q as a pre

processing step, yielding possibly modified conditions Conds(Q) and GConds(Q). The modified Conds(Q) and GConds(Q) are then used in checking conditions C2 304, C3 h 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.sub.3 :SELECT     F.sub.1, MAX(C.sub.1)FROM       Calls(F.sub.1, T.sub.1, TI.sub.1, D.sub.1, M.sub.1,           Y.sub.1, DU.sub.1, P.sub.1, C.sub.1),           Calling.sub.-- Plans(PI.sub.1, PN.sub.1)WHERE      P.sub.1 = PI.sub.1 AND PN.sub.1 = "TrueUniverse"           AND Y.sub.1 = 1995 AND M.sub.1 = 12GROUPBY    F.sub.1HAVING     MAX(C.sub.1) > 10______________________________________

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

______________________________________V.sub.3 :  SELECT    F.sub.2, T.sub.2, TI.sub.2, D.sub.2, M.sub.2, Y.sub.2,            DU.sub.2, P.sub.2, C.sub.2  FROM      Calls(F.sub.2, T.sub.2, TI.sub.2, D.sub.2, M.sub.2,            Y.sub.2, DU.sub.2, P.sub.2, C.sub.2)  WHERE     Y.sub.2 = 1995 AND C.sub.2 > 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(C1)>10 in the HAVING clause of Q3 is equivalent to having the condition C1 >10 in the WHERE clause of Q3. Strengthening. Conds(Q3) by conjoining C1 >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.sub.3 ': SELECT     F.sub.1, MAX(C.sub.1) FROM       V.sub.3 (F.sub.1, T.sub.1, TI.sub.1, D.sub.1, M.sub.1,            Y.sub.1, DU.sub.1, P.sub.1, C.sub.1),            Calling.sub.-- Plans(PI.sub.1, PN.sub.1) WHERE      P.sub.1 = PI.sub.1 AND PN.sub.1 = "TrueUniverse"            AND M.sub.1 = 12 AND C.sub.1 > 10 GROUPBY    F.sub.1______________________________________

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

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.sub.4 :SELECT     P.sub.1, PN.sub.1 SUM(C.sub.1), MAX(C.sub.1)FROM       Calls(F.sub.1, T.sub.1, TI.sub.1, D.sub.1, M.sub.1,           Y.sub.1, DU.sub.1, P.sub.1, C.sub.1),           Calling.sub.-- Plans(PI.sub.1, PN.sub.1)WHERE      P.sub.1 = PI.sub.1 AND Y.sub.1 = 1995GROUPBY    P.sub.1, PN.sub.1______________________________________

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.sub.4 :SELECT     P.sub.2, M2, Y2 SUM(C.sub.2), MAX(C.sub.2)FROM       Calls(F.sub.2, T.sub.2, TI.sub.2, D.sub.2, M.sub.2,           Y.sub.2, DU.sub.2, P.sub.2, C.sub.2),GROUPBY    P.sub.2, M.sub.2, Y.sub.2______________________________________

View V4 groups the table Calls by the Plan-- Id,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 Plan-- Id column, resulting in more coarse groups than those computed in V4. However, the aggregate information of the Plan-- Id groups in Q4 can be computed by further aggregating the aggregate information computed for the (Plan-Id, Month, Year) groups in V4, as illustrated in the following rewritten query:

______________________________________Q.sub.4.sup.'   SELECT      P.sub.1, PN.sub.1 SUM(ME.sub.1), MAX(MC.sub.1)   FROM        V.sub.a (P.sub.1, M.sub.1, Y.sub.1, ME.sub.1,               MC.sub.1),               Calling.sub.-- Plans(PI.sub.1, PN.sub.1)   WHERE       P.sub.1 = PI.sub.1 AND Y.sub.1 = 1995   GROUPBY     P.sub.1, PN.sub.1______________________________________

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.sub.5 :SELECT     P.sub.1, COUNT(CN.sub.1)FROM       Calls(F.sub.1, T.sub.1, TI.sub.1, D.sub.1, M.sub.1,           Y.sub.1, DU.sub.1, P.sub.1, C.sub.1)           Customer(PN.sub.1, CN.sub.1)WHERE      F.sub.1 = PN.sub.1 AND Y.sub.1 = 1995GROUPBY    P.sub.1______________________________________

View V5a below maintains the total annual revenue for each customer, plan, and year:

______________________________________V.sub.5a : SELECT    F.sub.2, P.sub.2, Y.sub.2, SUM(C.sub.2) FROM      Calls(F.sub.2, T.sub.2, TI.sub.2, D.sub.2, M.sub.2,           Y.sub.2, DU.sub.2, P.sub.2, C.sub.2) WHERE     F.sub.2, P.sub.2, Y.sub.2______________________________________

V5a cannot be used to evaluate Q5. This is because the multiplicity of the From column of Calls is needed in order to compute COUNT(CN1), but that multiplicity is lost in the view V5a. However, consider view V5b below.

______________________________________V.sub.5b : SELECT     F.sub.2, P.sub.2, Y.sub.2, SUM(C.sub.2), COUNT(C.sub.2) FROM       Calls(F.sub.2, T.sub.2, TI.sub.2, D.sub.2, M.sub.2,            Y.sub.2, DU.sub.2, P.sub.2, C.sub.2) GROUPBY    F.sub.2, P.sub.2, Y.sub.2______________________________________

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.sub.5.sup.'    SELECT       P.sub.1, SUM(GC.sub.1)    FROM         V.sub.5b (F.sub.1, P.sub.1, Y.sub.1, YE.sub.1,                 CG.sub.1),                 Customer(PN.sub.1, CN.sub.1)    WHERE        F.sub.1 = PN.sub.1 AND Y.sub.1 = 1995    GROUPBY      P.sub.1______________________________________

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 C1 302 is unchanged.) The modified conditions are formally presented in FIG. 9.

Since ColSel(Q) must be a subset of Groups(Q), condition C2 a 904 is a generalization of condition C2 304. Intuitively, condition C3 a 906 guarantees that the columns in the view contain enough information to compute the aggregates required in the query. In particular, condition C3 a 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 C4 a 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 C1 a -C4 a 902-908 are satisfied, the rewritten query Q' is obtained from Q by applying algorithm AggViewSingleBlock, presented in FIG. 10. Steps S1 a 1002,S2 a 1004,S3 a 1006 are similar to steps S1 402, S2 404 and S3 406 of algorithm ConjViewSingleBlock. Steps S4 a 1008 and S5 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 HAVING clauses.

If conditions C1 a -C4 a 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 Qa since conditions C1 a -C4 a are satisfied.

Condition C1 a :

The 1-1 column mapping φ from V4 to Q4 is {F2 →F1,T2 →T1,TI2 →TI1,D2 →D1,M2 →M1,Y2 →Y1,DU2 →DU1,P2 →P1,C2 →C1 }.

Condition C2 a :

For column P1 in Groups(Q4), BP.sbsb.1 is the column P2 in ColSel(V4).

Condition C3 a :

For column SUM(C1) in Sel(Q4), Sel(V4) contains column SUM(C2), and for column MAX(C1) in Sel(Q4), Sel(V4) contains column MAX(C2).

Condition C4 a :

Conds' is the same as Conds(Q4), i.e., P1 =PI1 &Y1 =1995 since no conditions are enforced in V4.

The rewritten query Q4 resulting from applying steps S1 a -S5 a is given in Example 4.1.

EXAMPLE 7 Constraining φ(AggSel(V))

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

______________________________________Q.sub.6 :SELECT     P.sub.1, SUM(C.sub.1)FROM       Calls(F.sub.1, T.sub.1, TI.sub.1, D.sub.1, M.sub.1,           Y.sub.1, DU.sub.1 P.sub.1, C.sub.1),WHERE      Y.sub.1 = 1995 and C.sub.1 > 1GROUPBY    P.sub.1______________________________________

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

______________________________________V.sub.6 :SELECT     P.sub.2, M.sub.2, Y.sub.2, SUM(C.sub.1), MAX(C.sub.2)FROM       Calls(F.sub.2, T.sub.2, TI.sub.2, D.sub.2, M.sub.2,           Y.sub.2, DU.sub.2, P.sub.2, C.sub.2)GROUPBY    P.sub.2, M.sub.2, Y.sub.2______________________________________

View V6 cannot be used to evaluate Q6 above, although in the absence of the condition "C1 >1" in the WHERE clause in Q6, V6 could be used to evaluate Q6. Intuitively, this is because the built-in predicates in the query constrain the possible values of C1 and C2 is aggregated upon in the view V6, no condition on the result of the SUM or the MAX in V6 can capture the effect of the condition on C1 in Q6.

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 satisfy 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 C4 a 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 S3 a 1006 determines a GConds' in addition to Conds', using GConds(V) and GConds(Q) (resulting from the preprocessing step). Steps S4 a 1008 and S5 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 of Q 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 Q7 below is used to obtain information about calls exceeding an hour in duration:

______________________________________Q.sub.7 :  SELECT    F.sub.1, D.sub.1, M.sub.1, Y.sub.1  FROM      Calls(F.sub.1, T.sub.1, TI.sub.1, D.sub.1, M.sub.1,            Y.sub.1, DU.sub.1, P.sub.1, C.sub.1)  WHERE     DU.sub.1 > 3600______________________________________

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

______________________________________V.sub.7 :SELECT     F.sub.2, D.sub.2, M.sub.2, Y.sub.2, COUNT(T.sub.2)FROM       Calls(F.sub.2, T.sub.2, TI.sub.2, D.sub.2, M.sub.2,           Y.sub.2, DU.sub.2, P.sub.2, C.sub.2)WHERE      DU.sub.2 > 3600GROUPBY    F.sub.2, D.sub.2, M.sub.2, Y.sub.2______________________________________

There is a 1-1 column mapping from V7 to Q7, Sel(V7) contains all the columns required in Sel(Q7), and the conditions enforced by the WHERE clauses are identical. Even through COUNT(T2) 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 (30)

What is claimed is:
1. A method of evaluating a query in a database system including a materialized view, the method comprising the steps of:
receiving an input query;
semantically analyzing the materialized view to determine whether the materialized view is usable in evaluating the 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, the output query including at least one reference to the materialized view as a source of information to be returned by the output query that was not included in the input 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 comprising:
a storage device storing a materialized view;
a receiver operable to receive an input query;
a semantic analyzer operable to analyze the materialized view to determine whether the materialized view is usable in evaluating the input query;
a query rewriting device operable to rewrite the input query to produce an output query that is multi-set equivalent to the input query, the output query including at least one reference to the materialized view as a source of information to be returned by the output query that was not included in the input query, if the view is usable; and
a query evaluator operable to evaluate the output query.
12. The system of claim 11, wherein the semantic analyzer comprises:
a first analysis unit operable to determine that the materialized view does not project out any columns needed to evaluate the input query; and
a second analysis unit operable to determine 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 11, wherein the materialized view does not have aggregation.
20. The system of claim 11, 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, including:
computer program instruction means for receiving an input query;
computer program instruction means for semantically analyzing a materialized view to determine whether the materialized view is usable in evaluating the 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, the output query including at least one reference to the materialized view as a source of information to be returned by the output query that was not included in the input 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.
US08895024 1996-08-27 1997-07-16 Method and system for using materialized views to evaluate queries involving aggregation Expired - Lifetime US5897632A (en)

Priority Applications (2)

Application Number Priority Date Filing Date Title
US2463596 true 1996-08-27 1996-08-27
US08895024 US5897632A (en) 1996-08-27 1997-07-16 Method and system for using materialized views to evaluate queries involving aggregation

Applications Claiming Priority (2)

Application Number Priority Date Filing Date Title
US08895024 US5897632A (en) 1996-08-27 1997-07-16 Method and system for using materialized views to evaluate queries involving aggregation
PCT/US1997/014660 WO1998009238A1 (en) 1996-08-27 1997-08-19 Method and system for using materialized views to evaluate queries involving aggregation

Publications (1)

Publication Number Publication Date
US5897632A true US5897632A (en) 1999-04-27

Family

ID=26698682

Family Applications (1)

Application Number Title Priority Date Filing Date
US08895024 Expired - Lifetime US5897632A (en) 1996-08-27 1997-07-16 Method and system for using materialized views to evaluate queries involving aggregation

Country Status (2)

Country Link
US (1) US5897632A (en)
WO (1) WO1998009238A1 (en)

Cited By (70)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
WO1999050732A2 (en) * 1998-03-27 1999-10-07 Red Brick Systems, Inc. System and method for rewriting relational database queries
US5987450A (en) * 1996-08-22 1999-11-16 At&T System and method for obtaining complete and correct answers from incomplete and/or incorrect databases
US6026390A (en) * 1996-05-29 2000-02-15 At&T Corp Cost-based maintenance of materialized views
US6134545A (en) * 1997-11-13 2000-10-17 Electronics Data Systems Corporation Method and system for processing a query
US6275818B1 (en) * 1997-11-06 2001-08-14 International Business Machines Corporation Cost based optimization of decision support queries using transient views
US6334128B1 (en) 1998-12-28 2001-12-25 Oracle Corporation Method and apparatus for efficiently refreshing sets of summary tables and materialized views in a database management system
US6339769B1 (en) * 1998-09-14 2002-01-15 International Business Machines Corporation Query optimization by transparently altering properties of relational tables using materialized views
US6345272B1 (en) * 1999-07-27 2002-02-05 Oracle Corporation Rewriting queries to access materialized views that group along an ordered dimension
US6353835B1 (en) * 1998-08-03 2002-03-05 Lucent Technologies Inc. Technique for effectively maintaining materialized views in a data warehouse
US6356890B1 (en) * 2000-04-20 2002-03-12 Microsoft Corporation Merging materialized view pairs for database workload materialized view selection
US6356891B1 (en) * 2000-04-20 2002-03-12 Microsoft Corporation Identifying indexes on materialized views for database workload
US6366903B1 (en) * 2000-04-20 2002-04-02 Microsoft Corporation Index and materialized view selection for a given workload
EP1193618A2 (en) * 2000-09-27 2002-04-03 Microsoft Corporation Cost based materialized view selection for query optimization
US6389410B1 (en) 2000-09-07 2002-05-14 Oracle Corporation Method for minimizing the number of sorts required for a query block containing window functions
US20020072951A1 (en) * 1999-03-03 2002-06-13 Michael Lee Marketing support database management method, system and program product
US6439783B1 (en) 1994-07-19 2002-08-27 Oracle Corporation Range-based query optimizer
US6449606B1 (en) 1998-12-28 2002-09-10 Oracle Corporation Using a materialized view to process a related query containing an antijoin
US6449609B1 (en) 1998-12-28 2002-09-10 Oracle Corporation Using materialized view to process a related query containing a one to many lossless join
US6449605B1 (en) 1998-12-28 2002-09-10 Oracle Corporation Using a materialized view to process a related query containing a one to many lossless join
US20020147578A1 (en) * 2000-09-29 2002-10-10 Lingomotors, Inc. Method and system for query reformulation for searching of information
US6477525B1 (en) * 1998-12-28 2002-11-05 Oracle Corporation Rewriting a query in terms of a summary based on one-to-one and one-to-many losslessness of joins
US6484159B1 (en) * 1999-05-20 2002-11-19 At&T Corp. Method and system for incremental database maintenance
US6493699B2 (en) * 1998-03-27 2002-12-10 International Business Machines Corporation Defining and characterizing an analysis space for precomputed views
US6496819B1 (en) 1998-12-28 2002-12-17 Oracle Corporation Rewriting a query in terms of a summary based on functional dependencies and join backs, and based on join derivability
US20020198872A1 (en) * 2001-06-21 2002-12-26 Sybase, Inc. Database system providing optimization of group by operator over a union all
US6532470B1 (en) * 1999-12-17 2003-03-11 International Business Machines Corporation Support for summary tables in a database system that does not otherwise support summary tables
US6546382B1 (en) 1999-11-03 2003-04-08 Oracle Corporation Finding the TOP N values through the execution of a query
US20030088558A1 (en) * 1999-05-20 2003-05-08 International Business Machines Corporation Optimizing correlated queries using automatic summary tables
US20030093415A1 (en) * 2001-11-15 2003-05-15 Microsoft Corporation System and method for optimizing queries using materialized views and fast view matching
US6622138B1 (en) 2000-09-06 2003-09-16 Oracle International Corporation Method and apparatus for optimizing computation of OLAP ranking functions
US6636846B1 (en) * 2000-04-28 2003-10-21 International Business Machines Corporation Method for providing a system maintained materialized functionally dependent column for a database management system
US6721735B1 (en) 2000-03-13 2004-04-13 Lucent Technologies Inc. Method and apparatus for synchronizing databases in a network management system
US20040122814A1 (en) * 2002-12-18 2004-06-24 International Business Machines Corporation Matching groupings, re-aggregation avoidance and comprehensive aggregate function derivation rules in query rewrites using materialized views
US20040128289A1 (en) * 2002-12-30 2004-07-01 Cochrane Roberta Jo System and method for incrementally maintaining non-distributive aggregate functions in a relational database
US20040133538A1 (en) * 2002-12-23 2004-07-08 Amiri Khalil S. Transparent edge-of-network data cache
US20040181521A1 (en) * 1999-12-22 2004-09-16 Simmen David E. Query optimization technique for obtaining improved cardinality estimates using statistics on pre-defined queries
US6847962B1 (en) * 1999-05-20 2005-01-25 International Business Machines Corporation Analyzing, optimizing and rewriting queries using matching and compensation between query and automatic summary tables
US20050125430A1 (en) * 2002-08-01 2005-06-09 Oracle International Corporation Asynchronous actions using flashback
US20050235004A1 (en) * 2004-04-14 2005-10-20 Oracle International Corporation Using estimated cost to schedule an order for refreshing a set of materialized views (MVS)
US20050234971A1 (en) * 2004-04-14 2005-10-20 Oracle International Corporation Using estimated cost to refresh a set of materialized views (MVS)
US20050240624A1 (en) * 2004-04-21 2005-10-27 Oracle International Corporation Cost-based optimizer for an XML data repository within a database
US20060106765A1 (en) * 2004-11-12 2006-05-18 International Business Machines Corporation Method, system and program product for rewriting view statements in structured query language (SQL) statements
US7092951B1 (en) * 2001-07-06 2006-08-15 Ncr Corporation Auxiliary relation for materialized view
US20060206468A1 (en) * 2003-04-17 2006-09-14 Dettinger Richard D Rule application management in an abstract database
US20060212436A1 (en) * 2002-03-26 2006-09-21 Oracle International Corporation Rewrite of queries containing rank or rownumber or Min/Max aggregate functions using a materialized view
US20060235840A1 (en) * 2005-04-19 2006-10-19 Anand Manikutty Optimization of queries over XML views that are based on union all operators
US20060282424A1 (en) * 2005-06-14 2006-12-14 Microsoft Corporation View matching for materialized outer-join views
US7158994B1 (en) 2001-09-28 2007-01-02 Oracle International Corporation Object-oriented materialized views
US20070043726A1 (en) * 2005-08-16 2007-02-22 Chan Wilson W S Affinity-based recovery/failover in a cluster environment
US20070083809A1 (en) * 2005-10-07 2007-04-12 Asha Tarachandani Optimizing correlated XML extracts
EP1777630A1 (en) * 2005-10-24 2007-04-25 Sap Ag Combining multi-dimensional data-sources using database operations
US20070239659A1 (en) * 2006-03-30 2007-10-11 Oracle International Corporation Query generator
US20070299834A1 (en) * 2006-06-23 2007-12-27 Zhen Hua Liu Techniques of rewriting descendant and wildcard XPath using combination of SQL OR, UNION ALL, and XMLConcat() construct
US20080091623A1 (en) * 2006-10-16 2008-04-17 Oracle International Corporation Technique to estimate the cost of streaming evaluation of XPaths
US7379933B1 (en) * 2002-11-27 2008-05-27 Oracle International Corporation Union all rewrite for aggregate queries with grouping sets
US20080133492A1 (en) * 2006-11-30 2008-06-05 Microsoft Corporation Efficient execution of aggregation queries
US20080306904A1 (en) * 2006-10-30 2008-12-11 Takeshi Fukuda System, method, and program product for integrating databases
US20090064160A1 (en) * 2007-08-31 2009-03-05 Microsoft Corporation Transparent lazy maintenance of indexes and materialized views
US20090307275A1 (en) * 2005-12-02 2009-12-10 International Business Machines Corporation System for improving access efficiency in database and method thereof
US20090319476A1 (en) * 2008-06-20 2009-12-24 Chris Olston Adaptive materialized view selection for databases
US20100036825A1 (en) * 2008-08-08 2010-02-11 Oracle International Corporation Interleaving Query Transformations For XML Indexes
US7734602B2 (en) 2004-04-14 2010-06-08 Oracle International Corporation Choosing whether to use a delayed index maintenance depending on the portion of the materialized view (MV) changed
US20100274785A1 (en) * 2009-04-24 2010-10-28 At&T Intellectual Property I, L.P. Database Analysis Using Clusters
US20110066600A1 (en) * 2009-09-15 2011-03-17 At&T Intellectual Property I, L.P. Forward decay temporal data analysis
US20110196857A1 (en) * 2010-02-09 2011-08-11 International Business Machines Corporation Generating Materialized Query Table Candidates
US20120036165A1 (en) * 2010-08-04 2012-02-09 Sap Ag Upgrading column-based databases
US20120173589A1 (en) * 2010-12-29 2012-07-05 Yong Sik Kwon In-Memory Database For Multi-Tenancy
US8312099B1 (en) 1998-12-22 2012-11-13 At&T Intellectual Property Ii, L.P. Pseudo proxy server
US20140164897A1 (en) * 2011-03-08 2014-06-12 Certusoft, Inc. Constraint-Choice-Action Matrix for Decision Making: Apparatus and Method
US9798771B2 (en) 2010-08-06 2017-10-24 At&T Intellectual Property I, L.P. Securing database content

Families Citing this family (5)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
WO1999057658A1 (en) * 1998-05-01 1999-11-11 Information Advantage System and method for updating a multi-dimensional database
US6546381B1 (en) 1998-11-02 2003-04-08 International Business Machines Corporation Query optimization system and method
US6718320B1 (en) 1998-11-02 2004-04-06 International Business Machines Corporation Schema mapping system and method
US6438538B1 (en) 1999-10-07 2002-08-20 International Business Machines Corporation Data replication in data warehousing scenarios
US7467128B2 (en) * 2006-02-15 2008-12-16 Microsoft Corporation Maintenance of materialized outer-join views

Citations (4)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US5161225A (en) * 1989-10-23 1992-11-03 International Business Machines Corporation Persistent stream for processing time consuming and reusable queries in an object oriented database management system
US5276870A (en) * 1987-12-11 1994-01-04 Hewlett-Packard Company View composition in a data base management system
US5600831A (en) * 1994-02-28 1997-02-04 Lucent Technologies Inc. Apparatus and methods for retrieving information by modifying query plan based on description of information sources
US5655116A (en) * 1994-02-28 1997-08-05 Lucent Technologies Inc. Apparatus and methods for retrieving information

Patent Citations (5)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US5276870A (en) * 1987-12-11 1994-01-04 Hewlett-Packard Company View composition in a data base management system
US5161225A (en) * 1989-10-23 1992-11-03 International Business Machines Corporation Persistent stream for processing time consuming and reusable queries in an object oriented database management system
US5600831A (en) * 1994-02-28 1997-02-04 Lucent Technologies Inc. Apparatus and methods for retrieving information by modifying query plan based on description of information sources
US5655116A (en) * 1994-02-28 1997-08-05 Lucent Technologies Inc. Apparatus and methods for retrieving information
US5768578A (en) * 1994-02-28 1998-06-16 Lucent Technologies Inc. User interface for information retrieval system

Non-Patent Citations (26)

* Cited by examiner, † Cited by third party
Title
Chaudhuri et al. "Optimizing Queries with Materialized Views", IEEE, pp. 190-200, 1995.
Chaudhuri et al. Optimizing Queries with Materialized Views , IEEE, pp. 190 200, 1995. *
Chen et al. "The Implementation and Performance Evaluation of the ADMS Query Optimizer: Integrating Query Result Caching and Matching", Proceedings of EDBT, pp. 1-15, 1994.
Chen et al. The Implementation and Performance Evaluation of the ADMS Query Optimizer: Integrating Query Result Caching and Matching , Proceedings of EDBT, pp. 1 15, 1994. *
E. N. Hanson, "A Performance Analysis Of View Materialization Strategies," Association for Computing Machinery Special Interest Group on Management of Data 1987 Annual Conference, San Francisco, CA, May 27-29, 1987, vol. 16, No. 3, ISSN 0163-5808, Sigmod Record, Dec. 1987, pp. 440-453.
E. N. Hanson, A Performance Analysis Of View Materialization Strategies, Association for Computing Machinery Special Interest Group on Management of Data 1987 Annual Conference, San Francisco, CA, May 27 29, 1987, vol. 16, No. 3, ISSN 0163 5808, Sigmod Record, Dec. 1987, pp. 440 453. *
Gupta et al. "Aggregate-Query Processing Data Warehousing Environments", VLDB Conference, pp. 358-369, 1995.
Gupta et al. Aggregate Query Processing Data Warehousing Environments , VLDB Conference, pp. 358 369, 1995. *
Gupta et al., "Adapting Materialized Views after Redefinitions", Sigmond, 95, pp. 211-222, 1995.
Gupta et al., Adapting Materialized Views after Redefinitions , Sigmond, 95, pp. 211 222, 1995. *
Hanson, E.M. "A Performance Analysis of View Materialization Strategies, " Association for Computing Machinery Special Interest Group on Management of Data. 1987 Annual Conference, San Francisco, CA, USA, May 27-29, 1987. Vol. 16, No. 3., pp. 440-453.
Hanson, E.M. A Performance Analysis of View Materialization Strategies, Association for Computing Machinery Special Interest Group on Management of Data. 1987 Annual Conference, San Francisco, CA, USA, May 27 29, 1987. Vol. 16, No. 3., pp. 440 453. *
J. Srivastava and D. Rotem, "Analytical Modeling of Materialized View Maintenance," Proceedings of the Seventh ACM Sigmod-Sigart Symposium on Principles of Database Systems, Austin, TX, Mar. 21-23, 1998, ISBN 0-89791-263-2, NY, NY, ACM, pp. 126-134.
J. Srivastava and D. Rotem, Analytical Modeling of Materialized View Maintenance, Proceedings of the Seventh ACM Sigmod Sigart Symposium on Principles of Database Systems, Austin, TX, Mar. 21 23, 1998, ISBN 0 89791 263 2, NY, NY, ACM, pp. 126 134. *
Levy et al. "Answering Queries Using Views", pp. 95-104, 1995.
Levy et al. Answering Queries Using Views , pp. 95 104, 1995. *
Sellis, "Intelligent Caching and Indexing Techniques for Relational Database Systems", Inform Systems, vol. 13, No. 2, pp. 175-185, 1988.
Sellis, Intelligent Caching and Indexing Techniques for Relational Database Systems , Inform Systems, vol. 13, No. 2, pp. 175 185, 1988. *
Srivistava, J. et al. "Analytical Modeling of Materialized View Maintenance," Proceedings of the Seventh ACM Sigact-Sigmod-Sigart Symposium on Principles of Database Systems, Austin, TX, USA, Mar. 21-23, 1998, pp. 126-134.
Srivistava, J. et al. Analytical Modeling of Materialized View Maintenance, Proceedings of the Seventh ACM Sigact Sigmod Sigart Symposium on Principles of Database Systems, Austin, TX, USA, Mar. 21 23, 1998, pp. 126 134. *
Stonebraker et al., "On Rules, Procedures, Caching and Views In Data Base Systems", pp. 281-290, 1990.
Stonebraker et al., On Rules, Procedures, Caching and Views In Data Base Systems , pp. 281 290, 1990. *
Tsatalos et al. "The GMAP: A Versatile Tool for Physical Data Independence", VLDB Conference, pp. 367-378, 1994.
Tsatalos et al. The GMAP: A Versatile Tool for Physical Data Independence , VLDB Conference, pp. 367 378, 1994. *
Yang, "Query Transformation for PSJ-queries", VLDB Conference, pp. 245-254, 1987.
Yang, Query Transformation for PSJ queries , VLDB Conference, pp. 245 254, 1987. *

Cited By (114)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US6439783B1 (en) 1994-07-19 2002-08-27 Oracle Corporation Range-based query optimizer
US6026390A (en) * 1996-05-29 2000-02-15 At&T Corp Cost-based maintenance of materialized views
US5987450A (en) * 1996-08-22 1999-11-16 At&T System and method for obtaining complete and correct answers from incomplete and/or incorrect databases
US6275818B1 (en) * 1997-11-06 2001-08-14 International Business Machines Corporation Cost based optimization of decision support queries using transient views
US6134545A (en) * 1997-11-13 2000-10-17 Electronics Data Systems Corporation Method and system for processing a query
US6493699B2 (en) * 1998-03-27 2002-12-10 International Business Machines Corporation Defining and characterizing an analysis space for precomputed views
US6199063B1 (en) * 1998-03-27 2001-03-06 Red Brick Systems, Inc. System and method for rewriting relational database queries
WO1999050732A2 (en) * 1998-03-27 1999-10-07 Red Brick Systems, Inc. System and method for rewriting relational database queries
US6594653B2 (en) * 1998-03-27 2003-07-15 International Business Machines Corporation Server integrated system and methods for processing precomputed views
US6629094B1 (en) * 1998-03-27 2003-09-30 International Business Machines Corporation System and method for rewriting relational database queries
US6353835B1 (en) * 1998-08-03 2002-03-05 Lucent Technologies Inc. Technique for effectively maintaining materialized views in a data warehouse
US6339769B1 (en) * 1998-09-14 2002-01-15 International Business Machines Corporation Query optimization by transparently altering properties of relational tables using materialized views
US8312099B1 (en) 1998-12-22 2012-11-13 At&T Intellectual Property Ii, L.P. Pseudo proxy server
US6449605B1 (en) 1998-12-28 2002-09-10 Oracle Corporation Using a materialized view to process a related query containing a one to many lossless join
US6477525B1 (en) * 1998-12-28 2002-11-05 Oracle Corporation Rewriting a query in terms of a summary based on one-to-one and one-to-many losslessness of joins
US6449606B1 (en) 1998-12-28 2002-09-10 Oracle Corporation Using a materialized view to process a related query containing an antijoin
US6496819B1 (en) 1998-12-28 2002-12-17 Oracle Corporation Rewriting a query in terms of a summary based on functional dependencies and join backs, and based on join derivability
US6334128B1 (en) 1998-12-28 2001-12-25 Oracle Corporation Method and apparatus for efficiently refreshing sets of summary tables and materialized views in a database management system
US6449609B1 (en) 1998-12-28 2002-09-10 Oracle Corporation Using materialized view to process a related query containing a one to many lossless join
US20020072951A1 (en) * 1999-03-03 2002-06-13 Michael Lee Marketing support database management method, system and program product
US20030088558A1 (en) * 1999-05-20 2003-05-08 International Business Machines Corporation Optimizing correlated queries using automatic summary tables
US7167853B2 (en) 1999-05-20 2007-01-23 International Business Machines Corporation Matching and compensation tests for optimizing correlated subqueries within query using automatic summary tables
US6847962B1 (en) * 1999-05-20 2005-01-25 International Business Machines Corporation Analyzing, optimizing and rewriting queries using matching and compensation between query and automatic summary tables
US6484159B1 (en) * 1999-05-20 2002-11-19 At&T Corp. Method and system for incremental database maintenance
US6345272B1 (en) * 1999-07-27 2002-02-05 Oracle Corporation Rewriting queries to access materialized views that group along an ordered dimension
US6546382B1 (en) 1999-11-03 2003-04-08 Oracle Corporation Finding the TOP N values through the execution of a query
US6532470B1 (en) * 1999-12-17 2003-03-11 International Business Machines Corporation Support for summary tables in a database system that does not otherwise support summary tables
US8386450B2 (en) * 1999-12-22 2013-02-26 International Business Machines Corporation Query optimization technique for obtaining improved cardinality estimates using statistics on pre-defined queries
US20040181521A1 (en) * 1999-12-22 2004-09-16 Simmen David E. Query optimization technique for obtaining improved cardinality estimates using statistics on pre-defined queries
US7890491B1 (en) * 1999-12-22 2011-02-15 International Business Machines Corporation Query optimization technique for obtaining improved cardinality estimates using statistics on automatic summary tables
US6721735B1 (en) 2000-03-13 2004-04-13 Lucent Technologies Inc. Method and apparatus for synchronizing databases in a network management system
US6366903B1 (en) * 2000-04-20 2002-04-02 Microsoft Corporation Index and materialized view selection for a given workload
US6356890B1 (en) * 2000-04-20 2002-03-12 Microsoft Corporation Merging materialized view pairs for database workload materialized view selection
US6356891B1 (en) * 2000-04-20 2002-03-12 Microsoft Corporation Identifying indexes on materialized views for database workload
US6636846B1 (en) * 2000-04-28 2003-10-21 International Business Machines Corporation Method for providing a system maintained materialized functionally dependent column for a database management system
US6622138B1 (en) 2000-09-06 2003-09-16 Oracle International Corporation Method and apparatus for optimizing computation of OLAP ranking functions
US6389410B1 (en) 2000-09-07 2002-05-14 Oracle Corporation Method for minimizing the number of sorts required for a query block containing window functions
EP1193618A3 (en) * 2000-09-27 2005-11-16 Microsoft Corporation Cost based materialized view selection for query optimization
JP2002163290A (en) * 2000-09-27 2002-06-07 Microsoft Corp Selection of materializing view on the basis of cost for optimizing query
EP1193618A2 (en) * 2000-09-27 2002-04-03 Microsoft Corporation Cost based materialized view selection for query optimization
US6510422B1 (en) * 2000-09-27 2003-01-21 Microsoft Corporation Cost based materialized view selection for query optimization
US20020147578A1 (en) * 2000-09-29 2002-10-10 Lingomotors, Inc. Method and system for query reformulation for searching of information
US6691101B2 (en) * 2001-06-21 2004-02-10 Sybase, Inc. Database system providing optimization of group by operator over a union all
US20020198872A1 (en) * 2001-06-21 2002-12-26 Sybase, Inc. Database system providing optimization of group by operator over a union all
US7761477B1 (en) 2001-07-06 2010-07-20 Teradata Us, Inc. Auxiliary relation for materialized view
US7092951B1 (en) * 2001-07-06 2006-08-15 Ncr Corporation Auxiliary relation for materialized view
US7158994B1 (en) 2001-09-28 2007-01-02 Oracle International Corporation Object-oriented materialized views
US7383256B2 (en) * 2001-11-15 2008-06-03 Microsoft Corporation System and method for optimizing queries using materialized views and fast view matching
US20050091208A1 (en) * 2001-11-15 2005-04-28 Microsoft Corporation System and method for optimizing queries using materialized views and fast view matching
US20030093415A1 (en) * 2001-11-15 2003-05-15 Microsoft Corporation System and method for optimizing queries using materialized views and fast view matching
US6850933B2 (en) * 2001-11-15 2005-02-01 Microsoft Corporation System and method for optimizing queries using materialized views and fast view matching
US20060212436A1 (en) * 2002-03-26 2006-09-21 Oracle International Corporation Rewrite of queries containing rank or rownumber or Min/Max aggregate functions using a materialized view
US20110106790A1 (en) * 2002-03-26 2011-05-05 Oracle International Corporation Rewrite of Queries Containing Rank or Rownumber or Min/Max Aggregate Functions Using a Materialized View
US8103689B2 (en) 2002-03-26 2012-01-24 Oracle International Corporation Rewrite of queries containing rank or rownumber or min/max aggregate functions using a materialized view
US7912834B2 (en) 2002-03-26 2011-03-22 Oracle International Corporation Rewrite of queries containing rank or rownumber or Min/Max aggregate functions using a materialized view
US7814067B2 (en) 2002-08-01 2010-10-12 Oracle International Corporation Asynchronous actions using flashback
US20050125430A1 (en) * 2002-08-01 2005-06-09 Oracle International Corporation Asynchronous actions using flashback
US7379933B1 (en) * 2002-11-27 2008-05-27 Oracle International Corporation Union all rewrite for aggregate queries with grouping sets
US20040122814A1 (en) * 2002-12-18 2004-06-24 International Business Machines Corporation Matching groupings, re-aggregation avoidance and comprehensive aggregate function derivation rules in query rewrites using materialized views
CN1708757B (en) 2002-12-23 2010-04-28 国际商业机器公 A transparent edge-of-network data cache
US20040133538A1 (en) * 2002-12-23 2004-07-08 Amiri Khalil S. Transparent edge-of-network data cache
WO2004061716A1 (en) * 2002-12-23 2004-07-22 International Business Machines Corporation A transparent edge-of-network data cache
US6950823B2 (en) 2002-12-23 2005-09-27 International Business Machines Corporation Transparent edge-of-network data cache
US7020649B2 (en) 2002-12-30 2006-03-28 International Business Machines Corporation System and method for incrementally maintaining non-distributive aggregate functions in a relational database
US20040128289A1 (en) * 2002-12-30 2004-07-01 Cochrane Roberta Jo System and method for incrementally maintaining non-distributive aggregate functions in a relational database
US20060206468A1 (en) * 2003-04-17 2006-09-14 Dettinger Richard D Rule application management in an abstract database
US20050235004A1 (en) * 2004-04-14 2005-10-20 Oracle International Corporation Using estimated cost to schedule an order for refreshing a set of materialized views (MVS)
US7890497B2 (en) 2004-04-14 2011-02-15 Oracle International Corporation Using estimated cost to schedule an order for refreshing a set of materialized views (MVS)
US20050234971A1 (en) * 2004-04-14 2005-10-20 Oracle International Corporation Using estimated cost to refresh a set of materialized views (MVS)
US8478742B2 (en) 2004-04-14 2013-07-02 Oracle Corporation Using estimated cost to refresh a set of materialized views (MVS)
US7734602B2 (en) 2004-04-14 2010-06-08 Oracle International Corporation Choosing whether to use a delayed index maintenance depending on the portion of the materialized view (MV) changed
US20050240624A1 (en) * 2004-04-21 2005-10-27 Oracle International Corporation Cost-based optimizer for an XML data repository within a database
US7930277B2 (en) 2004-04-21 2011-04-19 Oracle International Corporation Cost-based optimizer for an XML data repository within a database
US20060106765A1 (en) * 2004-11-12 2006-05-18 International Business Machines Corporation Method, system and program product for rewriting view statements in structured query language (SQL) statements
US7711692B2 (en) 2004-11-12 2010-05-04 International Business Machines Corporation Method, system and program product for rewriting view statements in structured query language (SQL) statements
US7685150B2 (en) 2005-04-19 2010-03-23 Oracle International Corporation Optimization of queries over XML views that are based on union all operators
US20060235840A1 (en) * 2005-04-19 2006-10-19 Anand Manikutty Optimization of queries over XML views that are based on union all operators
US20060282424A1 (en) * 2005-06-14 2006-12-14 Microsoft Corporation View matching for materialized outer-join views
US7406468B2 (en) 2005-06-14 2008-07-29 Microsoft Corporation View matching for materialized outer-join views
US7814065B2 (en) 2005-08-16 2010-10-12 Oracle International Corporation Affinity-based recovery/failover in a cluster environment
US20070043726A1 (en) * 2005-08-16 2007-02-22 Chan Wilson W S Affinity-based recovery/failover in a cluster environment
US8073841B2 (en) 2005-10-07 2011-12-06 Oracle International Corporation Optimizing correlated XML extracts
US20070083809A1 (en) * 2005-10-07 2007-04-12 Asha Tarachandani Optimizing correlated XML extracts
EP1777630A1 (en) * 2005-10-24 2007-04-25 Sap Ag Combining multi-dimensional data-sources using database operations
US7464083B2 (en) 2005-10-24 2008-12-09 Wolfgang Otter Combining multi-dimensional data sources using database operations
US20070094236A1 (en) * 2005-10-24 2007-04-26 Wolfgang Otter Combining multi-dimensional data sources using database operations
US20090307275A1 (en) * 2005-12-02 2009-12-10 International Business Machines Corporation System for improving access efficiency in database and method thereof
US8417680B2 (en) * 2005-12-02 2013-04-09 International Business Machines Corporation System for improving access efficiency in database and method thereof
US20070239659A1 (en) * 2006-03-30 2007-10-11 Oracle International Corporation Query generator
US7730080B2 (en) * 2006-06-23 2010-06-01 Oracle International Corporation Techniques of rewriting descendant and wildcard XPath using one or more of SQL OR, UNION ALL, and XMLConcat() construct
US20070299834A1 (en) * 2006-06-23 2007-12-27 Zhen Hua Liu Techniques of rewriting descendant and wildcard XPath using combination of SQL OR, UNION ALL, and XMLConcat() construct
US20080091623A1 (en) * 2006-10-16 2008-04-17 Oracle International Corporation Technique to estimate the cost of streaming evaluation of XPaths
US7797310B2 (en) 2006-10-16 2010-09-14 Oracle International Corporation Technique to estimate the cost of streaming evaluation of XPaths
US8965912B2 (en) * 2006-10-30 2015-02-24 International Business Machines Corporation Integrating databases
US20080306904A1 (en) * 2006-10-30 2008-12-11 Takeshi Fukuda System, method, and program product for integrating databases
US7769755B2 (en) 2006-11-30 2010-08-03 Microsoft Corporation Efficient execution of aggregation queries
US20080133492A1 (en) * 2006-11-30 2008-06-05 Microsoft Corporation Efficient execution of aggregation queries
US20090064160A1 (en) * 2007-08-31 2009-03-05 Microsoft Corporation Transparent lazy maintenance of indexes and materialized views
US20090319476A1 (en) * 2008-06-20 2009-12-24 Chris Olston Adaptive materialized view selection for databases
US7921103B2 (en) * 2008-06-20 2011-04-05 Yahoo! Inc. Adaptive materialized view selection for databases
US7958112B2 (en) 2008-08-08 2011-06-07 Oracle International Corporation Interleaving query transformations for XML indexes
US20100036825A1 (en) * 2008-08-08 2010-02-11 Oracle International Corporation Interleaving Query Transformations For XML Indexes
US8161048B2 (en) 2009-04-24 2012-04-17 At&T Intellectual Property I, L.P. Database analysis using clusters
US20100274785A1 (en) * 2009-04-24 2010-10-28 At&T Intellectual Property I, L.P. Database Analysis Using Clusters
US20110066600A1 (en) * 2009-09-15 2011-03-17 At&T Intellectual Property I, L.P. Forward decay temporal data analysis
US8595194B2 (en) 2009-09-15 2013-11-26 At&T Intellectual Property I, L.P. Forward decay temporal data analysis
US20110196857A1 (en) * 2010-02-09 2011-08-11 International Business Machines Corporation Generating Materialized Query Table Candidates
US8620899B2 (en) * 2010-02-09 2013-12-31 International Business Machines Corporation Generating materialized query table candidates
US20120036165A1 (en) * 2010-08-04 2012-02-09 Sap Ag Upgrading column-based databases
US8924384B2 (en) * 2010-08-04 2014-12-30 Sap Ag Upgrading column-based databases
US9798771B2 (en) 2010-08-06 2017-10-24 At&T Intellectual Property I, L.P. Securing database content
US20120173589A1 (en) * 2010-12-29 2012-07-05 Yong Sik Kwon In-Memory Database For Multi-Tenancy
US9460176B2 (en) * 2010-12-29 2016-10-04 Sap Se In-memory database for multi-tenancy
US20140164897A1 (en) * 2011-03-08 2014-06-12 Certusoft, Inc. Constraint-Choice-Action Matrix for Decision Making: Apparatus and Method

Also Published As

Publication number Publication date Type
WO1998009238A1 (en) 1998-03-05 application

Similar Documents

Publication Publication Date Title
Ono et al. Measuring the Complexity of Join Enumeration in Query Optimization.
Hristidis et al. Discover: Keyword search in relational databases
Abiteboul et al. The Lorel query language for semistructured data
Bosc et al. Fuzzy querying with SQL: extensions and implementation aspects
Haas et al. Extensible query processing in Starburst
Dittrich Object-oriented database systems (extended abstract): the notions and the issues
Baralis et al. Materialized Views Selection in a Multidimensional Database.
US5864840A (en) Evaluation of existential and universal subquery in a relational database management system for increased efficiency
Levy et al. Answering queries using views
Arasu et al. Characterizing memory requirements for queries over continuous data streams
Gupta et al. Adapting materialized views after redefinitions
US5797136A (en) Optional quantifiers in relational and object-oriented views of database systems
Hammer et al. Knowledge-based query processing
O'Neil et al. Improved query performance with variant indexes
US6374252B1 (en) Modeling of object-oriented database structures, translation to relational database structures, and dynamic searches thereon
US6882993B1 (en) Incremental refresh of materialized views with joins and aggregates after arbitrary DML operations to multiple tables
US6272487B1 (en) Query optimization through the use of multi-column statistics to avoid the problems of non-indexed column correlation
US6272488B1 (en) Managing results of federated searches across heterogeneous datastores with a federated collection object
US7127467B2 (en) Managing expressions in a database system
Cui et al. Tracing the lineage of view data in a warehousing environment
Lakshmanan et al. SchemaSQL: An extension to SQL for multidatabase interoperability
US5799300A (en) Method and system for performing range-sum queries on a data cube
US6233586B1 (en) Federated searching of heterogeneous datastores using a federated query object
Simmen et al. Fundamental techniques for order optimization
US8086598B1 (en) Query optimizer with schema conversion

Legal Events

Date Code Title Description
AS Assignment

Owner name: AT&T CORP., NEW YORK

Free format text: ASSIGNMENT OF ASSIGNORS INTEREST;ASSIGNORS:DAR, SHAUL;JAGADISH, HOSAGRAHAR VISVESVARAYA;LEVY, ALON YITZCHAK;AND OTHERS;REEL/FRAME:008997/0648;SIGNING DATES FROM 19980105 TO 19980218

FPAY Fee payment

Year of fee payment: 4

FPAY Fee payment

Year of fee payment: 8

FPAY Fee payment

Year of fee payment: 12

AS Assignment

Owner name: AT&T PROPERTIES, LLC, NEVADA

Free format text: ASSIGNMENT OF ASSIGNORS INTEREST;ASSIGNOR:AT&T CORP.;REEL/FRAME:034042/0574

Effective date: 20141023

Owner name: AT&T INTELLECTUAL PROPERTY II, L.P., GEORGIA

Free format text: ASSIGNMENT OF ASSIGNORS INTEREST;ASSIGNOR:AT&T PROPERTIES, LLC;REEL/FRAME:034042/0725

Effective date: 20141023

AS Assignment

Owner name: LINKEDIN CORPORATION, CALIFORNIA

Free format text: ASSIGNMENT OF ASSIGNORS INTEREST;ASSIGNOR:AT&T INTELLECTUAL PROPERTY II, L.P.;REEL/FRAME:035160/0466

Effective date: 20141216