Method and system for incremental database maintenance

[0002] The present invention relates to a method and system for incremental database maintenance, and in particular to updating materialized views in a database.

[0003] In a data warehouse, views are computed and stored in the database to allow efficient querying and analysis of the data. These views stored at the data warehouse are known as materialized views. In order to keep the views in the data warehouse up to date, it is necessary to maintain the materialized views in response to the changes at the sources. The view can be either recomputed from scratch, or incrementally maintained by propagating the base data changes onto the view so that the view reflects the changes. Incrementally maintaining a view can be significantly cheaper than recomputing the view from scratch, especially if the size of the view is large compared to the size of the changes.

[0004] The problem of finding such changes at the views based on changes to the base relations has come to be known as the view maintenance problem and has been studied extensively. Traditional maintenance techniques propagate insertions and deletions from the base relations to the view through each of its operations. Several improved schemes have been proposed for incremental maintenance of view expressions. However, problems arise with traditional techniques and with prior improved schemes. For example, none of the previously known schemes efficiently handles the case of general view expressions involving aggregate and outerjoin operators. As another example, most of the incremental maintenance approaches compute and propagate insertions and deletions at each node in a view expression tree, which is often inefficient.

[0005] A need arises for an incremental maintenance technique which efficiently handles general view expressions involving aggregate and outerjoin operators.

[0006] The present invention is a method and system for incrementally maintaining a database having at least one materialized view based on at least one table. When changes to the table are received, a change table based on the received changes is generated. The generated change table is propagated upwards to form a higher-level change table and the materialized view is updated by applying the higher-level change table to the materialized view using a refresh operation.

[0007] In one aspect of the present invention, the change table includes a plurality of tuples representing the changes and the materialized view includes a plurality of tuples. The refresh operation has two parameters, a join condition and an update function specification. The materialized view is updated by finding all tuples in the materialized view that match the tuple in the change table, using the join condition, for each tuple in the change table and updating each found tuple in the materialized view by performing operations indicated by the update function specification.

[0008] In one embodiment of the present invention, the materialized view is an aggregated materialized view. In this embodiment, each tuple in the materialized view and in the change table includes at least one aggregated attribute and may include one or more non-aggregated attributes. Tuples in the materialized view that match the tuple in the change table are found, using the join condition, by matching the non-aggregated attribute of a tuple in the change table, if any, with the non-aggregated attribute of a tuple in the view. Each found tuple in the materialized view is updated by updating the aggregated attribute of the tuple in the view using the aggregated attribute of the tuple in the change table.

[0009] In one aspect of this embodiment, the materialized view is further updated by inserting a tuple from the change table into the materialized view, if no tuples are found in the materialized view that match the tuple from the change table, and deleting a tuple from the materialized view, if an aggregated attribute representing a count of a number of tuples in a group represented by a the tuple becomes zero.

[0010] In another embodiment of the present invention, the materialized view is an outerjoin materialized view based on a plurality of tables. In this embodiment, there is a tuple in the materialized view corresponding to each value of at least one selected attribute in any of the plurality of tables. Each tuple in the materialized view comprises at least one selected attribute and at least one other attribute. Tuples in the materialized view that match the tuple in the change table are found, using the join by matching the at least one selected attribute of a tuple in the change table with the at least one selected attribute of a tuple in the view. Each found tuple in the materialized view is updated by updating the at least one other attribute of the tuple in the view using the at least one other attribute of the tuple in the change table.

[0011] In one aspect of this embodiment, the materialized view is further updated by inserting a tuple from the change table into the materialized view, if no tuples are found in the materialized view that match the tuple from the change table and deleting a tuple from the materialized view, if a value of the at least one selected attribute of the tuple is no longer present in any table.

[0012] 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.

[0013] FIG. 1 is an exemplary block diagram of a database system, according to the present invention.

[0014] FIG. 2 is a data flow diagram of the present invention.

[0015] FIG. 3 is an example of the computation of change tables and the refreshing of a materialized view.

[0016] FIG. 4 is another example of the change tables and refreshing of a materialized view.

[0017] FIG. 5 is a block diagram of a general process for refreshing an aggregated materialized view, according to the present invention.

[0018] FIG. 6 is a block diagram of a general refresh operation performed in the process shown in FIG. 5.

[0019] The present invention uses a framework of change-tables and a refresh operator for incrementally maintaining general view expressions involving relational and aggregate operators. The developed framework easily extends to efficiently maintaining view expressions containing outerjoin operators. Moreover, the framework also yields an approach to allow propagation of certain kinds of deletions and updates directly in a very efficient manner.

[0020] An exemplary database system 100, according to the present invention, is shown in FIG. 1. 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 mini-computer 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, random-access memory, a magnetic disk and/or optical disk and may also comprise a magnetic tape.

[0021] 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 116 include 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 120. Queries are typically written using SQL. Materialized views 120 includes one or more materialized views 122A-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 124 is typically a software module that performs the operations specified by the queries and generates the views.

[0022] Change tables 126 include one or more change tables 128A-Z. A change table, according to the present invention, is a compilation of updates that are to be performed to a particular materialized view. In order to update a materialized view, the corresponding change table cannot be simply inserted or deleted from the materialized view. Rather, the change table must be applied to the materialized view using a novel update operation that is another aspect of the present invention. The performance of this update operation is indicated by use of a “refresh” operator, denoted refresh. Change update or refresh engine 130 is typically a software module that performs the refresh operation to apply a change table to its associated materialized view.

[0023] The flow of data in the present invention is shown in FIG. 2. Materialized view 122A is initially generated based on database 112. Changes 127 are made to database 112, necessitating updating of materialized view 122A. Rather than regenerate materialized view 122A based on database 112, the present invention generates change table 128A, which is a compilation of updates that are to be performed to materialized view 122A. Change table 128A and materialized view 122A are input to change update engine 130, which performs the refresh operation to apply change table 128A to its associated materialized view 122A. The result is updated materialized view 122A′.

[0024] 1. Notation

[0025] The present invention is described using bag semantics, that is, all the relational operators used are duplicate preserving. The notation used to describe the present invention is defined as follows:

[0026] denotes bag union,

[0027] {dot over (−)} denotes monus (bag minus),

[0028] ∇E denotes deletions from a bag-algebra expression E,

[0029] ΔE denotes insertions into E,

[0030] σ_pdenotes selection on condition p,

[0031] Π_Adenotes duplicate preserving projection on a set of attributes A,

[0032] π denotes the generalized projection operator (note that slightly different symbols are used for duplicate-preserving projection (Π) and for generalized projection (π) operators),

[0033] × denotes the cross-product,

[0034] denotes natural join,

[0035] and denote, respectively, join and full outerjoin operations with the join condition J.

[0036] and denote, respectively, left and right outerjoin.

[0037] Attrs(J) denotes the set of attributes used in a predicate J or a relation J.

[0038] The only operators that may require explanation are the outerjoin and generalized projection operators. The (full) outerjoin differs from an ordinary join by including in the result any “dangling” tuple (dangling tuples are the ones that fail to join with any tuple from the other relation) of either relation after “padding” it with NULL's in those attributes that belong to the other relation. For example, R(A, B)_R.B=S.BS(B, C) tuple (a, b, NULL, NULL), if (a, b)∈R and (b, c)∉S for any c. One variant of the outerjoin operator is a left (right) outerjoin, where the dangling tuples of only the left (right) operand relation are padded with NULL's and included in the result. Hence, the above example, (a, b, NULL, NULL) would be included in RS, but not in RS. The generalized projection operator introduced in an article by A. Gupta, V. Harinarayan, and D. Quass., entitled Generalized projections: A powerful approach to aggregation, published in Proceedings of the21^stInternational Conference on Very Large Databases, Zurich, Switzerland, Sep. 11-15 1995, is used to algebraically represent the group by operation of SQL. For example, the following expression could be used to define the SISales view (V₁) of Example 1:

[0039] V₁=π_{storeID,itemID,SumSISales=sum(price),NumSISales=count(*)}(σ_date>1/1/95(sales))

[0040] For a set of attributes G, the notation ≡_Gis used to represent the predicate

[0041] Ë_g∈G(LHS.g=RHS.g)

[0042] in a join condition, where LHS and RHS are the right and left operand relations of the join operator. For example, the expression RS, where J is (≡_GËp), represents a join operation with the join condition (Ë_g∈G(R.g.=S.g.)Ëp), for a predicate p and a set of attributes G in R and S.

[0043] 2. Motivating Example

[0044] Consider the classic example of a warehouse containing information about stores, items, and day-to-day sales. The warehouse stores the information in three base relations: stores, items, and sales defined by their schemas as follows.

[0045] stores (storeID, city, state)

[0046] items (itemID, category, cost)

[0047] sales (storeID, itemID, date, price)

[0048] For each store location, the relation stores contains the storeID, the city, and the state in which the store is located. For each item, the relation items contains its itemID, its category and its purchase price (cost). An item can belong to multiple categories, e.g. a whitening toothpaste could belong to dental care, cosmetics, and hygiene categories. The relation sales contains detailed information about sales transactions. For each item sold, the relation sales contains a tuple storing the storeID of the selling store, itemID of the item sold, date of sale, and the sale price.

[0049] Consider the following views SISales, CitySales, CategorySales, SSInfo, and SSRecentInfo defined over the base relations. The view SISales computes for each storeID and itemID the total price of items sold after Jan. 1, 1995. The view SISales is an intermediate table used to define the views CitySales and CategorySales. The view CitySales stores, for each city, the total number and dollar value of sales of all the stores in the city. The view CategorySales stores the total sale for each category of items. All the above described views consider only those sales that occur after Jan. 1, 1995. The view SSInfo stores the full outerjoin of the base relations sales and stores, retaining stores that have had no sales, for some reason, and also retaining those sales whose corresponding storeID is missing from the table stores, because, perhaps, the table stores has not been updated yet. The view SSRecentInfo retains only the recent sales information from the view SSInfo, discarding sales that occurred before Jan. 1, 1995. The views CitySales, CategorySales, and SSRecentInfo are the only views that are stored (materialized) at the data warehouse and this is represented below by the keyword “MATERIALIZED” in the SQI, definitions of the views. The keyword “MATERIALIZED” is not supported by SQL, but has been introduced as an aspect of the present invention. These materialized views are to be maintained in response to changes at the base relations.

[0050] CREATE VIEW SISales AS

[0051] SELECT storeID, itemID, sum(price) AS SumSISales, count(*) AS NumSISales

[0052] FROM sales

[0053] WHERE date >Jan. 1, 1995

[0054] GROUP BY storeID, ItemID

[0055] CREATE MATERIALIZED VIEW CitySales AS

[0056] SELECT city, sum (SumSISales) AS SumCiSales, sum(NumSISales) AS NumCiSales

[0057] FROM SISales, stores

[0058] WHERE SISales.storeID=stores.storeID

[0059] GROUP BY city

[0060] CREATE MATERIALIZED VIEW CategorySales AS

[0061] SELECT category, sum (SumSISales) AS SumCaSales, sum(NumSISales) AS

[0062] NumCaSales

[0063] FROM SISales, items

[0064] WHERE SISales.itemID=items.itemID

[0065] GROUP BY category

[0066] CREATE VIEW SSInfo AS

[0067] SELECT*

[0068] FROM sales FULL OUTERJOIN stores

[0069] WHERE sales.storeID=stores.storeID

[0070] CREATE MATERIALIZED VIEW SSRecentInfo AS

[0071] FROM SSInfo

[0072] WHERE date≧Jan. 1, 1995

[0073] Consider the database sizes shown in Table 1. Assuming that the base relation sales has one billion sales transactions, and the base relations stores and items have 1,000 and 10,000 tuples respectively. The various maintenance approaches are illustrated for the particular case when 10,000 tuples are inserted into the base relation sales. Table 1 shows the number of tuples changed in the views, as the result of the insertion of 10,000 tuples into sales. The table also shows the number of tuple accesses (reads and writes) incurred by different maintenance techniques to update the materialized views. Counting tuple accesses is a simple model and suitable for our purposes, because under uniform access the number of tuple accesses directly translate into number of disk accesses. The names V₁, V₂, V₃, V₄, and V₅are used as follows:

[0074] V₁=SISales,

[0075] V₂=CitySales,

[0076] V₃=CategorySales,

[0077] V₄=SSInfo, and

[0078] V₅=SSRecentInfo.

[0079] It is assumed that the relations stores and items are small enough to fit in main-memory.

[0080] Rather than computing and propagating insertions and deletions beyond an aggregate node V₁, as was done in the prior art, the present invention computes and propagates a change table for V₁. A change table is a general form of the summary-delta tables introduced in an article by I. Mumick, D. Quass, and B. Mumick, entitled Maintenance of data cubes and summary tables in a warehouse, published in Proceedings of the AGM SIGMOD International Conference of Management of Data, Tucson, Ariz., June 1997.

[0081] The symbol V is used to denote the change table of a view V. It is shown that propagation of change tables yields very efficient and simple maintenance expressions for general view expressions involving aggregate and outerjoin operators. The change table cannot be simply inserted or deleted from the materialized view. Rather, the change table must be applied to the materialized view using a special “refresh” operator. In Section 4, a refresh operator that is used to refresh views using the change tables is defined. The symbol is used to denote the refresh operator, where θ and U are parameters specifying join conditions and updated functions respectively.

[0082] An example of the computation of change tables and refreshing of the CategorySales (V₃) view 301 is shown in FIG. 3, when an exemplary sales table 302 is updated with insertions Δsales 304. As seen by its SQL definition, CategorySales is an aggregate view based on another aggregate view, SISales and on the items table 305. SISales is an aggregate view that is based on the sales table 304 and not on any other materialized views. Thus, SISales is termed a first-level materialized view, while CategorySales is a higher-level materialized view. A change table for SISales, change table V₁306 is generated based on the insertion table Δsales 304, as well as any deletion tables ∇sales (not shown). Since table V₁306 is associated with first-level materialized view, it is termed a first-level change table. In order to update CategorySales (V₃) view 301, which is a higher-level view, a change table for CategorySales (V₃) view 301 must be generated. This is done by propagating change table V₁306 upward to form a higher-level change table for the CategorySales (V₃) view, V₃308. Change table V₃308 is applied to view V₃using the refresh operator to form refreshed view V₃312.

[0083] The example starts with computing the change table V₁306 that summarizes the net changes to V₁. For this first level of aggregates, the expression that computes V₁306 is similar to that derived in the article by Mumick, Quass and Mumick.

[0084] V₁=π_{storeID,itemID,SumSISales=sum(price),NumSISales=sum(count)}(Π_{storeID,itemID,price,count=1}(σ_p(Δsales))

[0085] Π_{storeID,itemID,price=−price,}_count=−1 (σ_p(∇sales))), where pis (date>Jan. 1, 1995).

[0086] FIG. 3 presents an instance of the base relation sales 302 and the table Δsales 304, which is the set of insertions into sales. For the given tables, FIG. 3 also shows the computed change table V₁306. Next the change table V₁306 is propagated upwards to derive expressions for the change tables V₂(not shown) and V₃308

[0087] V₃=π_{category,SumCaSales=sum(SumSISales),NumCaSales=sum(NumSISales)}(V₁items)

[0088] FIG. 3 shows how the change table V₃308 is computed and the CategorySales (V₃) view is refreshed. The change table V₂can be similarly computed. The new propagated change tables are then used to refresh their respective materialized views V₂and V₃using the refresh equations below (disregard θ and U for now). The details of the refresh equations are given in Example 3. Note that V₁does not need to be refreshed.

[0089] V₂=V₂₂, and V₃=V₃₂.

[0090] The refresh operation is illustrated by showing how the CategorySales view (V₃) is refreshed. FIG. 3 shows the materialized table V₃=CategorySales for the given instance of base tables.

[0091] Going back to computing the number of tuple accesses in Table 1, note that most of the computation is done in computing V₁, which requires 10,000 tuple accesses to read Δsales. Given the small sizes of V₁, items, and stores, the rest of the computation can be done in main memory and hence, the total number of tuples accesses is 10,000(to read Δsales)+11,000(to read items and sales)+2,020(to refresh V₂and V₃)=23,020, showing that our technique is very efficient in comparison to previous approaches.

[0092] A block diagram of a general process 500 for refreshing an aggregated materialized view is shown in FIG. 5. In step 502, change tables are computed for the first-level aggregates on which the higher-level materialized view being refreshed is based. In step 504, the first-level change tables are propagated upwards and the higher-level change table is computed. In step 506, the higher-level materialized view is refreshed by applying the higher-level change table to the higher-level materialized view with the refresh operation.

[0093] A block diagram of a general refresh operation, as performed in step 506, is shown in FIG. 6. The refresh operation is represented by the notation V=VV Thus, an updated view V is generated by applying the change table V to the unupdated view V using the refresh operation. The refresh operation take two parameters. The parameter denoted θ specifies the join conditions, which are used to match the tuples in V, which are to be updated, to the tuples in V that contain the updates. The parameter denoted U is a list of update function specifications, that is, a list of which tuple attributes are to be updated and the operations that are to be performed in order to update each attribute.

[0094] In step 602, for each tuple in the change table V, a tuple in the view V is found using the join conditions specified by parameter θ. In the example of FIG. 3, the join condition θ₃specifies matching a non-aggregated attribute of tuples in the view and change table, and the update function U₃specifies updating of at least some of the aggregated attributes of the tuples in the view. Thus, for each tuple v₃in V₃, a match in V₃using the join condition V₃category=V₃category (specified in θ₃) is sought. The tuple v₃=<C1, 170,3> in V₃matches with the tuple v₃=<C1,690,4> of V₃.

[0095] If a new tuple is inserted into the underlying table E, then no tuples will be found in the materialized view V that match a tuple in the change table V. The tuple in the change table will be inserted into the view. If there are a plurality of such tuples to be inserted, all such insertions will be converted to aggregated insertions or updates into by the refresh equation. The deletions from E need not necessarily result in any deletions from V. A tuple is deleted from V only if an aggregated attribute representing a count of a number of tuples in a group represented by a the tuple becomes zero, which is independent of the deletions of the deriving tuples from E.

[0096] In step 604, the found tuples are updated by updating the specified attributes. The tuple <C1,170,3> in V₃means that three more sales totaling $170 have occurred for C1 category. The total sales for C1 are now 7 for a total amount of $860. To reflect the change, the tuple V is updated to <C1,860,7> by adding together the corresponding aggregated attributes (specified by the U₃parameter of the refresh operator).

[0097] The change-table technique can also be used for maintenance of view expressions involving outerjoin operators. Outerjoin views are supported by SQL and are commonly used in practice, such as for data integration. The technique for maintaining outerjoin views by deriving maintenance expressions for outerjoin views V₄and V₅is illustrated. The net changes to V₄, in response to insertions Δsales into sales, can be succinctly summarized in a change table V₄. The change table V₄is computed and then propagated up as follows:

[0098] V₄=Δsales_{sales,storeID=stores,storeID}stores

[0099] V₅=σ_{(date≧1/1/95)}(V₄)

[0100] V₅=V₅₅

[0101] FIG. 4 shows the materialized view V₅402,the change tables V₄and V₅404 for the database instance of FIG. 3, and the refreshed view V₅406.

[0102] The process for refreshing an outerjoin view is similar to the process for refreshing an aggregate view. First-level change tables are computed, in the example of FIG. 4, using the equation V₄=Δsales_{sales,storeID=stores,storeID}stores. The first-level change tables are propagated upwards and the higher-level change table is computed, in the example of FIG. 4, using the equation V₅=σ_{(date≧1/1/95)}(V₄). The higher-level materialized view is refreshed by applying the higher-level change table to the higher-level materialized view with the refresh operation, in the example of FIG. 4, using the equation V₅=V₅V₅.

[0103] The major difference is in the specification of the refresh operation. For example, the refresh of V₅proceeds as follows. When an outerjoin is performed, two or more tables are merged to form a new table or view. The new table or view has a tuple corresponding to each value of a selected attribute or attributes present in any original table. Since it is not required that there be tuples corresponding to each value of the selected attribute in all original tables, the values of some attributes in the new table or view may be undefined. This is represented in FIG. 4 as NULL. Insertions into an original table may result in tuples being inserted into the new table or view, if no tuples in the view match the tuple in the change table, or they may result in undefined attributes being defined. Likewise, deletion from an original table may result in tuples being deleted from the new table or view, if the value of the selected attribute in that tuple is no longer present in any original table, or they may result in defined attributes becoming undefined.

[0104] For example, each tuple in V₅is matched with tuples in V5 that have the same stores attributes, but have all NULL's in the attributes of sales. This join condition used for matching is specified in θ₅. In the example of FIG. 4, the only matching pair is (v5, v5), where v5=<NY3,13,1/1/92,100,NY3,Buffalo,NY>∈V₅and v₅=<NULL,NULL,NULL,NULL,NY3,Buffalo,NY>∈V₅. The match results in an update of v5 to v5, according to the update specifications in U₅. The remaining unmatched tuples in V₅are inserted into the view V₅. The refreshed view V₅is also shown in FIG. 4.

[0105] Given the small sizes of stores, ∇sales, and V₄, the number of tuple accesses required to compute the change tables and refresh V₅is 10,000(to read Δsales)+1,000(to read stores)+200(to refresh V₅).

[0106] The present invention includes a novel technique to maintain general view expressions involving outerjoin operators. Previous work, for example, the article by A. Gupta, H. V. Jagadish, and I. S. Mumick, entitled Maintenance and self-maintenance of outerjoin views published in Proceedings of the NGITS, Tel Aviv, Israel, June 1997, only considers views that have outerjoin as the last operator. To apply the technique of that article to our example, it would be necessary to compute the intermediate view SSInfo, thereby incurring more than a billion tuple accesses.

[0107] 3. The Change-Table Technique for View Maintenance

[0108] This section explains the framework developed in the article by Xiaolei Qian and Gio Wiederhold entitled Incremental recomputation of active relational expressions. Published in IEEE Transactions on Knowledge and Data Engineering, pages 337-341, 1991, and the article by T. Griffin and L. Libkin entitled Incremental maintenance of views with duplicates published in Proceedings of the ACM SIGMOD International Conference on Management of Data, pages 328-339, San Jose, Calif., May 1995, for deriving incremental view maintenance expressions and relate it to the change-table technique of the present invention.

[0109] Let a database contain a set of relations R={R₁, R₂, . . . , R_n}. A change transaction t is defined to contain for each relation R_ithe expression R_i←(R_i{dot over (−)}∇R_i)ΔR_i, where ∇R_iis the set of tuples to be deleted from R_i, and ΔR_iis the set of tuples to be inserted into R_i.

[0110] Let V be a bag-algebra expression defined on a subset of the relations in R. The refreshed-expression New(V, t) is used to compute the new value of R. In the article by Griffin and Libkin (which uses the notation pre(t, V) instead) the expression New(V, t) is defined to be:

[0111] New(V, t)=(V{dot over (−)}∇(V, t))Δ(V, t).

[0112] So, the goal in deriving view maintenance expressions for a view V is to derive two functions ∇(V, t) and Δ(V, t) such that for any transaction t, the view V can be maintained by evaluating (V{dot over (−)}∇(V, t))Δ(V, t). In order to derive ∇(V, t) and Δ(V, t), the article by Griffin and Libkin gives change propagation equations that show how deletions and insertions are propagated up through each of the relational operators. The work of Griffin and Libkin was extended to include aggregate operators by D. Quass, Materialized Views in Data Warehouses, Ph.D. thesis, Stanford University, Department of Computer Science, 1997, Chapter 4.

[0113] The change-table technique of the present invention can be thought of as introducing a novel definition for New(V, t). The expression New(V, t) for view expressions involving aggregates and outerjoin is redefined as

[0114] New(V, t)=(V(V, t)),

[0115] where (V, t) is called the change table, is the refresh operator used to apply the net changes in a change table to its view, and (θ, U) are the parameters of the refresh operator. The parameter θ specifies the join conditions on the basis of which the tuples from the change table and the view are matched. The parameter U specifies the functions that are used to update the matched tuples in V.

[0116] The new definition of New(V, t) is motivated from the following observation. In the case of general view expressions involving aggregate operators, it is usually more efficient to propagate the change tables beyond an aggregate operator, instead of first computing and then propagating insertions and deletions. Propagation of the change table is particularly efficient when the change table depends only on the changes to the base relation, while the insertions and deletions depend on the old value of the view. As shown in the motivating example, if the aggregate node is not materialized, the computation of insertions and deletions could be very expensive.

[0117] The new definition of New(V, t) means that it is necessary to define a general refresh operator, and derive change propagation equations for propagating change tables through various relational, aggregate and outerjoin operators, to obtain a complete technique for efficient incremental maintenance of general view expressions. In the following section, a formal definition of the refresh operator is presented. In later sections, change propagation equations for general view expressions involving aggregate and outerjoin operators is derived.

[0118] 4. The refresh Operator

[0119] In this section, a formal treatment of the refresh operator is given. Given a materialized table V and its change table V, the refresh is used to apply the changes represented in a change table. The binary refresh operator is a generalization of the refresh algorithm used in the article by Mumick, Quass and Mumick.

[0120] The refresh operator is denoted by , where θ is a pair of two mutually exclusive join conditions and U is a list of update function specifications. The refresh operator takes two operands, a view V to be updated and a corresponding change table (denoted by V).

[0121] Let V and V be views with the same attribute names A₁, A₂. . . , A_n. The subscript θ associated with the operator is a pair of join conditions ℑ₁and ℑ₂. The superscript U is a list specifying the attributes that may be changed by the change operator, along with the update functions that specify the exact nature of the change to each attribute being changed. More precisely, U=<(A_i, 𝑓₁), (A_i, 𝑓₂), . . . , (A_i, 𝑓_k)>, where A_i, . . . , A_iare attributes of V and 𝑓₁, . . . , 𝑓_kare functions. Unless otherwise specified, it is assumed that the functions in U are binary functions. In an expression VV, each tuple v of V is checked for possible matches (due to ℑ₁or ℑ₂) with tuples in V. If a match is found due to the join condition ℑ₁, then the corresponding matching tuple v of V is updated as described in the next paragraph (using the specifications in the update list U). If the match is due to ℑ₂, the tuple v of V is deleted. The unmatched tuples in V are inserted into V. The matching done is one-to-one in the sense that a tuple v∈V matches with at most one tuple in V and vice-versa. If v finds more than one match in V, then an arbitrary matching tuple from V is picked.

[0122] Update of a tuple v of V matching with the tuple v of V is done based on the update functions in U. For each pair (A_i, 𝑓_j) in U, the A_iattribute of v is changed to 𝑓_j(v(A_i), vA_i)) where v(X) and v(X) denote the values of the X attribute of v and v respectively. Each pair in U is used to change the matching U is used to change the matching tuple V.

[0123] Consider the view V₃=Categorysales defined in Example 1 earlier. The CategorySales table as defined in Example 1 computes the total sales for each category. In this example, the refresh operation is illustrated by applying the changes summarized in a change table CategorySales to its view CategorySales using the refresh operator.

[0124] For this example, consider the instance of the base table shown in FIG. 3. FIG. 3 also shows the materialized table Categorysales for the given instance. In response to the insertion of the table Δsales into the base table sales, the change table CategorySales can be computed and is shown in the FIG. 3. The view CategorySales is refreshed using the expression CategorySales CategorySales, where the parameters, θ₃=(ℑ₁, ℑ₂) and U₃, of the refresh operator are defined as follows.

[0125] ℑ₁is (≡_category((CategorySales.NumCaSales+(CategorySales) .NumCaSales)≠0))

[0126] œ₂is (≡_category((CategorySales.NumCaSales+(CategorySales) .NumCaSales)=0))

[0127] U₃=<(SumCaSales, 𝑓),(NumCaSales, 𝑓)>, where 𝑓(x, y)=x+y for any x, y.

[0128] Note that ≡_categoryrepresents the predicate (CategorySales.category=(CategorySales).category) here. Now, the refresh operation is attempted to be run on the change table CategorySales and the view CategorySales of FIG. 3.

[0129] The first tuple v₃=<C1,170,3> of CategorySales matches with the tuple v₃=<C1,690,4> in CategorySales using the join condition ℑ₁. The match results in update of the tuple <C1,690,4> in CategorySales according to the specifications in the update list U₃. The attribute SumCaSales of the tuple v₃is changed to v₃.SumCaSales+v₃.SumCaSales=170+690=860 and the attribute NumCaSales is changed to 4+3=7. Similarly, the tuple <C2,210,2>∈V₃matches with the tuple <C2,120,2>∈V₃and is updated to <C2,330,4>. The tuple <C3,480,2>∈V₃is also updated to <C3,530,3> accordingly.

[0130] To illustrate deletion from the view Categorysales, let us assume that the change table Categorysales contains a tuple b=<C2,−210,−2> as a result of a deletion of a couple of tuples from the sales table. The tuple b will match with the tuple v=<C2,210,2>∈V₃using the join condition ℑ₂and the match will result in deletion of the tuple v from V₃.

[0131] One simple way to implement the refresh operator is using a nested loop algorithm, with the change table as the outer table, and the materialized view table as the inner table, as shown in Appendix A. The nested loop algorithm is just one possible way to implement the refresh operator. Quass and Mumick have developed a more efficient implementation of the refresh operation that uses existing outerjoin methods inside the DBMS. They defined a modify operator using a pair of join conditions and a series of condition-action lists. The modify operator can be easily implemented in a DBMS through slight modifications of existing physical query operators for outerjoins, such as hash outerjoin, sort-merge outerjoin, and nested-loop outerjoin. Cost-based optimization can be used to select the method most suitable for a given evaluation. Implementing the refresh operation of the present invention using the modify operator is significantly faster than iterative algorithms.

[0132] 5. Propagating Change Tables Generated at Aggregates

[0133] In this section, the derivation is given for the algebraic equations to (1) generate change tables at an aggregation node in a view expression, and (2) propagate these change tables through the relational, aggregate and outerjoin operators in a general view expression.

[0134] A change table for a view expression involving aggregates is defined as an aggregate-change table if the change table either originated at an aggregate operator or is a result of propagation of a change table that originated at an aggregate node, using the propagation equations presented in Table 3.

[0135] For example, the change tables, V₁, V₂, and V₃, computed for the views V₁, V₂, and V₃respectively in Example 1 are aggregate-change tables.

[0136] Note that the notions of aggregate-change table and outerjoin-change table have been defined only for simplifying the presentation of the material in this article. The present invention contemplates a general change table that encompasses aggregate-change tables, outerjoin-change tables and any other specific type of change table.

[0137] An aggregate function is defined as distributive if it can be computed by partitioning the input parameters into disjoint sets of parameters, aggregating each set individually, then further aggregating the (partial) results from each set into the final result.

[0138] An aggregate function is defined to be self-maintamnable if the new value of the function can be computed solely from the old value of the function and the changes (insertions or deletions) to the base data.

[0139] It will be shown that a special case of the general refresh operator is sufficient to apply the changes summarized in an aggregate-change table to its view. The special form yields very simple change propagation equations for propagation of aggregate-change tables through relational, aggregate and outerjoin operators.

[0140] The notation Attrs(X) is used to represent the set of attributes referenced in X. Thus, Attrs(U) refers to the set of attributes specified in the update list U and Attrs(θ) represents Attrs(ℑ₁)U Attrs(ℑ₂), where ℑ₁and ℑ₂are the join conditions in θ=(ℑ₁, ℑ₂).

[0141] 5.1 Generating the Aggregate-Change Table

[0142] Consider a view V defined as an aggregation over a select-project-join (SPJ) expression. In this section, a brief description is given of how an aggregate-change table is generated at V in response to the insertions and deletions at the base tables. The method is similar to that of generating “summary-delta tables” for a “summary table” as described in the article by Mumick, Quass and Mumick.

[0143] For the case when a view V is defined as an aggregation over an SPJ expression, the insertions and deletions to the base tables can be propagated to V as a single aggregate-change table, which is denoted by V. Without loss of generality, let us assume V to be π_G,B=𝑓(A)(R), where R is the SPJ subview, G is the set of group-by attributes, f is an aggregate function, and A is an attribute of R. In the case of distributive aggregate functions, the aggregate-change table V can be computed from the insertions and deletions into R by using the same generalized projection as the used for defining the view V. More precisely, V can be computed as

[0144] V=π_{G,𝑓(A),Count=sum(count)}(Π_G,A,count=1(ΔR)Π_{G,A=N(A),count=−1}(∇R)),

[0145] where the function N is suitably defined depending on the aggregate function 𝑓 as shown in Table 2. For example, in the case of a sum aggregate the function N negates the attribute value passed. Once the aggregate-change table has been computed, the net changes, now in the aggregate-change table, are then applied to the view using the refresh operator.

[0146] If the aggregate function 𝑓 is a self-maintainable aggregate function, then the special form of refresh operator defined in the next subsection is sufficient to apply the change table V to the view V. For the case of aggregate functions that are not self-maintainable, more complex functions are needed in the update list U of the refresh operator. For example, to handle deletions from a subview of a simple MIN/MAX aggregate view, the update function needs to compute the new MIN/MAX value, if needed, by accessing the base relations (see the article by Mumick, Quass and Mumick for details). The change propagation equations and other formalism presented in this article are independent of the complexity and input parameters of update functions used in the update list U of refresh.

[0147] 5.2 Refresh Operator for Applying Aggregate-Change Tables

[0148] For the case of applying net changes computed in an aggregate-change table to its view, the refresh operator required is a special case of the generic operator defined earlier in Section 4. The special characteristics of the refresh operator are used to derive simpler change propagation equations.

[0149] Recall that the expression used to refresh a view V using its change table V is V=VV, where θ=(ℑ₁, ℑ₂) and U is the update list. In the case of the refresh operator used to apply aggregate-change tables, ℑ₁is (≡_Gp) and ℑ₂is (≡_Gp), for some predicate p and a set of attributes G common to both V and V. As defined before, the notation ≡_Ghere represents the predicate _g∈G(V._g=V._g), as V and V are the left and right operands of the join operator. Also, the set of attributes G is disjoint from the set of attributes, Attrs(U), that are being updated. The predicate p specifies when the matching tuple in V is to be deleted, i.e., when the value of the attribute that stores the number of deriving base tuples becomes zero.

[0150] An aggregate-refresh operator is derived that is a special form of the refresh operator (as described above) used to apply an aggregate-change table to its view. The characteristics of the aggregate-refresh operator are used to derive simple change propagation equations for propagating aggregate-change tables.

[0151] A refresh operator is said to be an aggregate-refresh operator if:

[0152] θ=(ℑ₁, ℑ₂) and the join condition ℑ₁is (≡_Gp₁), and the join condition ℑ₂is (≡_Gp₂), for some predicates p₁, p₂and a set of attributes G common to both the view and its aggregate-change table.

[0153] p₁=p₂.

[0154] G∩Attrs(U)=φ.

[0155] 5.3 Change Propagation Equations

[0156] Table 3 gives the change propagation equations for propagating aggregate-change tables through relational, aggregate and outerjoin operators. Below, is a brief explanation of each row of Table 3. In this table, it is assumed that an aggregate-change table has been generated at the first aggregate operator in a view expression, in response to insertions and/or deletions from the view's subview. The above base case has not been shown in the table. In Theorem 1, the correctness of the refresh equations of Table 3 is proven.

[0157] Each row in the table considers propagation of an aggregate-change table through a relational, aggregate or outerjoin operator. The first column gives the equation number used for later reference in examples. The second column in the table gives the definition of V. The third column expresses the new expression for V due to a change occurring at one of the subexpressions E₁of V. In each row, possibly due to changes at base table, the subexpression E₁changes to E₁E₁where E₁is an aggregate-change table and is an aggregate-change operator. It is assumed that θ is (ℑ₁, ℑ₂), where ℑ₁is ≡_Gp1 and ℑ₂is ≡_Gp₁, for some predicate p₁and a set of attributes G in E₁. The fourth column gives the refresh equation which is used to refresh the view V and also yields the value of the propagated aggregate-change table. In Theorem 1 it is proven that the refresh equation given in the fourth column is equivalent to the expression in the third column for each row of Table 3. Theorem 1 also proves that the refresh operator used in the refresh equations of fourth column is an aggregate-refresh operator. The fifth column gives the expression for the propagated aggregate-change table V. Finally, the last column of the table mentions the conditions under which the equivalence of the fourth column and third column expressions hold, i.e., conditions under which the change propagation can be done. If the condition is not satisfied, then the refresh equation cannot be used to propagate the aggregate-change table. Later, it will be shown how to handle changes at such an operator node, when the condition of Table 3 is not satisfied.

[0158] The first row of the table depicts the case of a selection view V=σ_p(E), where E is a subexpression. In this case, as depicted in the third column, the subexpression E changes to EE, where E is an aggregate-change table and is an aggregate-change operator. The expression for the propagated aggregate-change table V=σ_p(E) is given in the fifth column. For the case of the selection view, the condition required for the change propagation is Attrs(p)⊂G. In other words, an aggregate-change table can be propagated through a selection operator if the selection operator if the selection condition is defined over the G attributes, which are the attributes that are not being updated.

[0159] The second row depicts the case of projection on a set of attributes A. The condition implies that an aggregate change table can be propagated through a duplicate-preserving projection only if all the attributes used in the join conditions of θ are included in the projection list A.

[0160] The third row considers the case of a cross product operation. In this case, the parameter θ of the refresh operator is changed to also include the condition ≡_Attrs(E)in the join conditions ℑ₁, and ℑ₂. No conditions are specified in the fifth column meaning that any aggregate-change table can be propagated through a cross product operator.

[0161] The fourth row depicting the case of a join operation follows from the combination of previous cases of selection and cross product.

[0162] The fifth row considers propagation of an aggregate-change table through the bag union operator. The expression for V in the fifth column gives only the change table (V=E₁). As the refresh equation in the fourth column shows, the refresh in this case is more complex than simply applying the change table. It is necessary to apply a set of deletions (∇V=E₂) into the view V. One can derive a very efficient refresh equation for the case of bag union operator, if the tuples in V are “tagged” L/R depending on whether they come from the left operand E₁or the right, operand E₂.

[0163] The sixth row depicts the case of propagation through a generalized projection (aggregate) operator. For the purposes of aggregate-change tables, it is assumed that any subexpression involving an aggregate operator stores with each tuple a count of the number of deriving base tuples. This count is stored in a general attribute which is called the count attribute. For example, NumCiSales and NumCaSales are count attributes in the views V₂and V₃of Example 1. After propagation through the aggregate operator, the join conditions and the update specifications of the refresh operator change as shown in the fourth column. The attribute named Cnt used in p₃is the count attribute of V. The condition in the fifth column says that each aggregate function 𝑓_iis distributive, the set of group-by attributes G′ is a subset of G, and the update list U used to change E contains (A_i, 𝑓_i) for all 1≦i≦k. Note that the duplicate-elimination operation is a special case of generalized projection, and is covered by the sixth row.

[0164] The seventh row gives the refresh equation for the case of propagating an aggregate-change table through a full outerjoin operation. In this case, the refresh operator specifications change as in the case of cross product. The outerjoin propagation equation in the seventh row is simplified a little (as discussed in Appendix C).

[0165] Note that no change propagation equation is given for the case of monus because an aggregate-change table cannot be propagated through the minus operator (See the paragraph on singularity points below). Also, the refresh operator used in each of the derived refresh equations of column 4 is an aggregate-refresh operator.

[0166] The operator nodes in a view expression tree, where none of the refresh equations in Table 3 apply, are called singularity points. Consider a view V and a singularity point V₁, which is a subexpression of V, in the expression tree of V. As propagation of the change table V₁is not possible, it is necessary to compute insertions (ΔV₁) and deletions (∇V₁) into V₁and propagate the insertions and deletions upwards. The tables ΔV₁and ∇V₁can be computed from V₁by applying the refresh algorithm (Algorithm 1). If V₁is not materialized, the base relations are used to compute (relevant parts of) V₁whenever required by the refresh algorithm. The insertions (ΔV₁) and deletions (∇V₁) to V₁are then propagated through the singularity point using the change propagation equations described in the article by Griffin and Libkin, and in the article by Quass. The propagation insertions and deletions at a singularity point can now be propagated upwards using techniques presented in this article (as they may result in change tables further on) or the techniques of Griffin and Libkin for propagating insertions and deletions through relational operators.

[0167] Assume that the refresh operator used in the expression of the third column in Table 3 is an aggregate-refresh operator. Then, the change propagation equations given in Table 3 for propagation of aggregate-change tables are correct, i.e., for each row, the expression in the third column is equivalent to the refresh equation in the fourth column. Also, the refresh operator derived in the refresh equation (column 4) is an aggregate-refresh operator as well.

[0168] In this example, the techniques developed in this section are illustrated as applied to the views of the motivating Example 1. Recall from Example 1 the definitions of V₁(SISales), V₂(CitySales), and V₃(CategorySales). Thus:

[0169] V₁′=π_{storeID,itemID,SumSISales=sum(price), NumSISales=count(*)}(sales)

[0170] V₁=σ_date>1/1/95(V₁′)

[0171] V₂′=V₁stores

[0172] V₂=π_{city,itemID,SumSISales=sum(price),NumSISales=count(*)}(V₂′)

[0173] V₃′=V₁items

[0174] V₃=π_{category,SumCaSales=sum(SumSISales),NumCaSales=sum(NumSISales)}(V₃′)

[0175] where the (virtual) views V₁′, V₂′ V₃′ have been added for better illustration of how the aggregate-change tables propagation. The change propagation equations of Table 3 are used to derive the maintenance expressions for V₂and V₃in response to changes in sales, as follows.

[0176] V₁′=π_{storeID,itemID,SumSISales=sum(price),NumSISales=sum(count)}(Π_{storeID,price,count=1}(Δsales)

[0177] Π_{storeID,price=−price,count=−1}(∇Sales))  [From Section 5.1]

[0178] V₁=σ_date>1/1/95(V₁′)  [From (1) in Table 3]

[0179] V₁=V₁(V₁), where θ₁=(≡_{{storeID,itemID}}p, ≡_{{storeID,itemID}})

[0180] V₂′=V₁stores

[0181] V₂′=V₂₂′,  [From (4) in Table 3]

[0182] where θ₁₂is (≡_{{storeID,itemID}∪Attrs(stores){storeID,itemID}∪Attrs(stores)}

[0183] V₂=π_{city,SumSISales=sum(SumSISales),NumCiSales=sum(NumSISales)}(V₂′)  [From (6) in Table 3]

[0184] V₃′=V₁items

[0185] V₃′=V₃′₃′,  [From (4) in Table 3]

[0186] where θ₁₃is (≡_{{storeID,itemID}∪Attrs(items){storeID,itemID}∪Attrs(items)})

[0187] V₃=π_{category,SumCaSales=sum(SumSISales),NumCaSales=sum(NumSISales)}(V₃′)

[0188] V₃=V₃₃, where θ₃is (≡_{categorycategory})  [From (6) in Table 3]

[0189] In all the above equations, U is of the form <(SUM, 𝑓)(COUNT, 𝑓)> and p is of the form (LHS.COUNT+RHS.COUNT)=0), where SUM is the aggregated attribute (SumSISales, SumCiSales, or SumCaSales) in the corresponding view, COUNT is the count attribute (NumSISales, NumCiSales, or NumCaSales) depending on the view, and 𝑓(x, y)=x+y for all x, y. (Recall that LHS and RHS refer to the left and right operands of the join operation where p occurs.)

[0190] As described in Example 1, the above derived maintenance expressions for V₂and V₃are very efficient compared to the expressions derived by previous approaches.

[0191] 6. Maintaining Outerjoin Views Efficiently

[0192] In this section, it is shown how our change-table techniques can be used to derive efficient and simple algebraic expressions for maintenance of view expressions involving outerjoin operators. Outerjoin is supported in SQL. Further, outerjoins have recently gained importance because data from multiple distributed databases can be integrated by means of outerjoin views. Outerjoins are also extensively used in object-related relational systems.

[0193] A change table for a view involving outerjoin operations is defined as an outerjoin-change table if the change table was either generated at an outerjoin operator or is a result of propagation of an outerjoin-change table, using the propagation equations that will be derived for propagating outerjoin-change tables.

[0194] For example, the change tables, V₄and V₅, computed for the views V₄and V₅in Example 1 are outerjoin-change tables.

[0195] First, it is shown how the insertions and deletions into an outerjoin view (RS), in response to insertions into the base table R, can be summarized into an outerjoin-change table. The next changes represented by the outerjoin-change table are then applied to the outerjoin view using the refresh operator. Computation of an outerjoin-change table at an outerjoin view in response to deletions from a base table requires a more general refresh operator, as discussed in Appendix C.

[0196] 6.1 Generating Outerjoin-Change Table At An Outerjoin Node

[0197] Given tables R(A₁, A₂, . . . , A_n) and S(B₁, B₂, . . . , B_m), consider an outerjoin view V(A₁, . . . A_n, B₁, . . . , B_m)=RS, where J is an equi-join condition. Insertions into R, ΔR, result in some insertions and deletions into V. These set of insertions and deletions to V can be summarized into an outerjoin-change table V defined as V=ΔRS. Note that the tables V and V have the same schema and attribute names. It is shown that with the following specification of the refresh operator, the net changes in the outerjoin-change table V can be applied to the view V to obtain the correctly refreshed V. The specifications θ=(ℑ₁, ℑ₂) and U, of the refresh operator used to apply V to V are defined as follows.

[0198] ℑ₁is (≡_Gp) where G=Attrs(S) and p=(Ë_1≦j≦n(V.A_j=NULL)).

[0199] ℑ₂is FALSE.

[0200] The update list U of update function is <(A₁, 𝑓), (A₂, 𝑓), . . . , (A_n, 𝑓)>, where 𝑓(x, y)=y for all x, y.

[0201] Consider the view V=RS defined as above. For the above definition of V and the refresh operator specifications of θ=(ℑ₁, ℑ₂) and U, the following holds:

[0202] (R)S=(R)(V)

[0203] 6.2 Propagating Outerjoin-Change Tables

[0204] Only a special form of the generic refresh operator, which is called an outerjoin-refresh operator, is required to refresh a view using its outerjoin-change table.

[0205] Let {A₁, A₂, . . . , A_n, B₁, B₂, . . . , B_m} be the set of attributes in V and its outerjoin-change table V. A refresh operator used to apply the outerjoin-change table V to its view V is said to be an outerjoin-refresh operator if:

[0206] The join condition ℑ₁is (≡_Gp), where G={B₁, B₂, . . . , B_m} and p is a predicate on the attributes (LHS.A₁, LHS.A₂, . . . , LHS.A_n). The join condition ℑ₂is FALSE.

[0207] U is <(A₁, 𝑓), (A₂, 𝑓), . . . , (A_n, 𝑓)>, where 𝑓(x, y)=y for all x, y. Note that Attrs(U)∩G=φ and Attrs(U)∩G=Attrs(V).

[0208] The refresh equations given in Table 3 correctly propagate an outerjoin-change table as well, except for the case of propagation through the outerjoin operator, for which a different equation is derived, as follows: Consider a view V=E₁E₂where E₁and E₂are general view expressions. Suppose that the expression E₁is refreshed to E₁E₁using its outerjoin-change table E₁where the refresh operator is an outerjoin-refresh operator. Let θ=(≡_Gp, FALSE), for some predicate p and a set of attributes G common to E₁and E₁. The following equation shows how to propagate the outerjoin-change table E₁to V under the condition Attrs(J)⊂G.

[0209] V=(E₁₂)

[0210] V=((E₁₁)E₂)=E₁₂)(V)

[0211] where θ₁=(≡_Attrs(E)((p_G)(Ë_e∈Attrs(E)LHS.e₁=NULL)),FALSE),

[0212] U₁=<(A₁, 𝑓),(A₂, 𝑓), . . . ,(A_k, 𝑓)>, and {A₁, A₂, . . . , A_k}=Attrs(E₁).

[0213] The function 𝑓 is such that 𝑓(x, y)=y for all x, y. Note that the refresh operator used in the equation (7b) to refresh V using V is an outerjoin-refresh as well.

[0214] Assume that the refresh operator used in the expression of the third column in Table 3 is an outerjoin-refresh operator. Then, the change propagation equations given in Table 3, with the following two changes, correctly propagate outerjoin-change tables.

[0215] The refresh equation for selection view is correct even without the condition given in column 6.

[0216] The above equation (7b) is used for propagation through an outerjoin operator.

[0217] Also, the refresh operator derived in each of the refresh equations (column 4) is also an outerjoin-refresh operator.

[0218] Consider a view V=π_{A,B,F=sum(D),H=sum(E),Num=Count(*)}((σ_A<5((R_C=DS)T)), where R(A, B, C), S(D, E), and T(A, B, L) are base relations, and is the natural join operation, i.e., a join with the join condition (≡_{A,B}). Recall that for the purposes of calculating the SUM aggregates, the attribute value of NULL is taken as 0, provided at least one tuple has a non-NULL value.

[0219] For clarity of presentation, let us assume V₁=R_C=DS, V₂=V₁T, V₃=σ_A>5(V₂). Let us define a predicate p as ((LHS.D=NULL) (LHS.E=NULL)), a predicate q as ((LHS.Num+RHS.Num)=0), an update list U₁as <(F, 𝑓),(H, 𝑓)>, and U as <(D, g),(E, g),(Num, g)>. Here, 𝑓(x, y)=y for all x, y and g(x, y)=x+y for all x, y≠NULL and g(NULL, y)=y. The techniques of maintaining views involving outerjoin operators by deriving maintenance expressions for V in response to insertions, ∇S, into S, are illustrated.

[0220] V₁=(R)

[0221] V₁=V₁₁, where θ₁is (≡_Attrs(R)FALSE)  [From Theorem 2]

[0222] V₂=V₁

[0223] V₂=V₂₂, where θ₂is (≡_Attrs(R)∪_Attrs(T), FALSE)  [From (4) in Table 3]

[0224] V₃σ_A>5(V₂)

[0225] V₃=V₃₃  [From (1) in Table 3]

[0226] V=π_{A,B,F=Sum(D),H=Sum(E),Num=Count(*)}(V₃)

[0227] V=V, where θ is (≡_{A,B}{A,B})  [From (4) in Table 3]

[0228] Input

[0229] Table V(A₁, A₂, . . . , A_n).

[0230] Change Table V(A₁, A₂, . . . , A_n).

[0231] θ=(ℑ₁, ℑ₂), U=<(A_i, 𝑓₁), (A_i, 𝑓₂), . . . , (A_i, 𝑓_k)>

[0232] Output

[0233] Refreshed table V, i.e., VV.

[0234] Method

[0235] DECLARE cursor box_CURSOR FOR

[0236] SELECT A₁, A₂, . . . , A_nFROM V;

[0237] OPEN cursor_box;

[0238] LOOP

[0239] FETCH cursor_box INTO :a₁, :a₂, . . . , :a_n,;

[0240] UNTIL not-found

[0241] DECLARE cursor_view CURSOR FOR

[0242] SELECT A₁, A₂, . . . , A_nFROM V

[0243] WHERE (ℑ₁(A₁, A₂, . . . , A_n, : a₁, : a₂, . . . , : a_n) OR ℑ₂(A₁, . . . , A_n,

[0244] :a₁, . . . , : a_n)) AND (NOT changed);

[0245] OPEN cursor_view;

[0246] LOOP

[0247] FETCH cursor_view INTO :a₁:a₂, . . . , : a_n;

[0248] UNTIL not-found

[0249] IF not-found

[0250] INSERT INTO VVALUES (:a₁, :a₂, . . . , :a_n);

[0251] ELSE-IF ℑ₂(:a₁, :a₂, . . . , :a_n,:a₁, :a₂, . . . , :a_n)

[0252] DELETE FROM V WHERE CURRENT OF cursor_view;

[0253] break;

[0254] ELSE

[0255] UPDATE V SET update attributes=function of update attributes from cursor_view as changed.

[0256] break;

[0257] ENDIF

[0258] ENDLOOP

[0259] CLOSE cursor_view;

[0260] ENDLOOP

[0261] CLOSE cursor_box;

[0262] Proof:

[0263] Selection: V=σ_p(E). First, it is shown that if e∈E affects a tuple v∈σ_p(E)⊂E by updating or deleting v from R, then e∈σ_p(E). As e results in an update or deletion of v, the pair (es, e) satisfies the join condition (≡_G). Now, as Attrs(p)⊂G. if es satisfies p then e satisfies p too. Thus, all tuples in E that can update or delete a tuple in V=σ_p(E) are in σ_p(E)=V. Therefore, all the required updates or deletions to V are captured by the refresh equation.

[0264] Let I be the set of tuples in E that do not find a match in E.I is the set of tuples inserted into E and thus, σ_p(I) should be inserted into V. As σ_p(I)⊂σ_p(E)=V, and as no tuple in σ_p(I) will find a match in V⊂E, σ_p(I) will be inserted into V using the refresh equation of V. Therefore, refresh equation doesn't miss any legitimate insertions into V.

[0265] Also, a tuple v∈σ_p(E) cannot find a mach in σ_{{overscore (p)}}E₁because Attrs(p)⊂G. Therefore, if v doesn't find a match in V=σ_p(E), then v does not find a match in E. Hence, all insertions into V due to the refresh equation are legitimate.

[0266] Projection: V=II_A(E). If Attrs(θ)⊂A, then the information needed to decide the effect of a tuple e∈E on a tuple e∈E, if any, is available in II_A(s). Hence, E can be directly applied to V=II_A(E) after the same projection step. Note that the resulting refresh operator is also an aggregate-refresh operator.

[0267] Cross Product: V=E₁×E₂. Suppose a tuple e₁∈E₁matches with a tuple e₁∈E₁. Consider the set E₁^[]e=(e₁×E₂)⊂V. It is easy to see that each tuple (e₁, e₂)∈E₂^[]ewill result in a match with a tuple (e₁, e₂)∈(e₁×E₂) due to the join conditions in θ₁. The matches will result in appropriate updates and deletions.

[0268] Also any unmatched tuple e₁∈E₁will also result in the unmatched tuples in (e₁×E₂)⊂V.

[0269] Join: V=E₁E₂. Follows from the previous cases, but stated in the table for convenience.

[0270] Union: V=E₁E₂. As ((E₁E₂){dot over (−)}E₂)=E₁, the equivalence of the expression is obvious.

[0271] Aggregation: V=π_G′,𝑓(A)(E). Without loss of generality, this case is proven when k=1. Let 𝑓₁=𝑓 and A₁=A. It is assumed that (G′⊂G)A⊂Attrs(U), U=<(A₁, 𝑓), (A₂, 𝑓), . . . , (A_k, 𝑓)> and 𝑓 is a distributive function. Let us assume that the tuples e₁, e₂, . . . e_k∈E have the same G′ values and their aggregated attribute A values are a₁, a₂, . . . , a_k. Also, assume that the tuple e_imatches with a tuple e_i∈E due to ℑ₁and that the aggregated attribute A value of e_iis a₁. Note that e_i's have the same G′ values too. The attribute value a_iof e_iis updated to 𝑓(a_i, a_i) due to U. Thus the aggregated value in V derived from e_i, . . . , e_kshould change from 𝑓(a₁, a₂, . . . , a_n) to 𝑓(𝑓(a₁, a₁), 𝑓(a₂, a₂), . . . , 𝑓(a_n, a_n)). Now, by the definition of V, the tuples e₁, . . . , e_k∈E will be grouped to yield the aggregate attribute value 𝑓(a₁, . . . , a_n). The refresh equation of V correctly changes the aggregated value of the grouped value of e_is from 𝑓(a₁, a₂, . . . a_n) to 𝑓(𝑓(a₁, a₂, . . . a_n), 𝑓(a₁, . . . , a_n). As 𝑓 is a distributive function, 𝑓(𝑓(a₁, a₁), 𝑓(a₂, a₂), . . . 𝑓(a_n, a_n))=𝑓(𝑓(a₁, a₂, . . . , a_n), 𝑓(a₁, . . . , a_n)), hence the refresh equation of V correctly updates the aggregated attribute values in V.

[0272] All insertions into E due to E will be converted to aggregated insertions or updates into V by the refresh equation. The deletions from E need not necessarily result in any deletions from V. A tuple is deleted from V only if its aggregated attributes become zero, which is independent of the deletions of the deriving tuples from E.

[0273] Outerjoin: V=EE₂. Suppose E₁induces a set of insertions I into the relation E₁. Each tuple i∈I results in a set of tuples E₂ⁱ=iE₂in V. No tuple e₂ⁱ∈E₂ⁱfinds a match in V due to the predicate (≡_G≡_τ), because if it did, i would have found a match in E₁due to ≡_G. Therefore, the refresh equation results in E₂ⁱbeing inserted into V for each i∈I.

[0274] Let us assume that M(⊂E₁) is a set of tuples that find a match in E₁due to ≡_Gand hence, result in either deletion or update of a tuple in E₁. Each tuple m∈M results in a set of tuples E₂^m=mE₂in V. If m matched with a tuple e₂∈E₂due to both having the same G attributes, then each tuple (r,m)∈E₁^mwould match the corresponding tuple (r,s)∈E₁^s=E₁S. The tuple (r,s) exists in E₁s because (r,m)∈E₁^mand Attrs(J)⊂G. Also, note that E₂^s⊂V. Thus the refresh equation of V affects the updates and deletions correctly.

[0275] This completes the proof.

[0276] Proof: Due to insertion of ΔR into R, the view V should change as follows. The set of tuples ΔRS should be inserted into V but the tuples of the kind (NULL, . . . , NULL, s₁, s₂, . . . , s_m)∈V should be deleted from V if a corresponding tuple (r₁, r₂, . . . r_n, s₁, s₂, . . . , s_m) is being inserted into V from V.

[0277] The above effect an be achieved by changing a tuple v=(NULL, . . . , NULL, s₁, s₂, s_m) in V to v=(r₁, r₂, . . . , r_n, s₁, s₂, . . . , s_m) if such a tuple v exists in V. The refresh of V would be complete if the tuples v in V for which no such match occurs are inserted into V. By the definition of the refresh operator and its specification, one can see that is exactly what is achieved by the expression VV.

[0278] Proof. The proof is the same as in Theorem 1 except for the cases of selection and outerjoin, which uses a different equation.

[0279] V=σ_p(E). The second characteristic of the outerjoin-refresh operator is used to show that σ_p(EE=σ_p(E).

[0280] Using the same arguments as in Theorem 1, the refresh equation of V doesn't miss any legitimate insertions into V affected by the refresh operation EE.

[0281] Now it is shown that all insertions into V using the refresh equation are legitimate. The refresh equation may induce an insertion of the tuple v∈V into V if v doesn't find a match in V. If v∈I, where I is the set of tuples in E that don't find a match in E, then the insertion is obviously legitimate. Suppose, v∉I. That implies that v∈E found a match with a tuple e∈E. Because of the second characteristic of an outerjoin-refresh operator and its specification, any match results in the tuple in E being updated to the matching tuple in E. Thus, the effect of the refresh operation EE is that the tuple e is updated to v. As v∈V, it satisfies the selection condition p and hence, v is included in V. Thus, the insertion of v into V by the refresh equation is correct.

[0282] In the refresh equation, if a tuple v∈V is updated due to a tuple v∈V=σ_p(E), then v∈E would have been updated by v too in the refresh operation of E. As v is updated to v which satisfied the predicate p, the updated tuple v is correctly retained in V by the refresh equation. This shows that the updates to V in the refresh equation are legitimate.

[0283] The only updates the refresh equation might miss are of the kind where a tuple e∈E matches with a tuple e′∈τ_{{overscore (p)}}(E). In such a case, the tuple e′ is updated to e. If e∈σ_p(E), then the updated tuple e should be included in V. This happens in the refresh equation too, as e doesn't find a match in V and hence is inserted into V.

[0284] Outerjoin: V=E₁E₂. The correctness of the refresh equation is proven. Let M(⊂E₁) be the set of tuples in E₁that find a match in E₁due to ℑ₁=≡_Gp. Note that G is a set of attributes in E₁and p is a predicate over the rest of the attributes in E₁. Consider m∈M and that m finds a match e₁∈E₁due to ℑ₁. Each m results in the set of tuples E₂^m=mE₂in V=EE₂. Each tuple e₂^m=<m, e₂>∈E₂^mwill find a corresponding match in v=<e₁, e₂>∈V due to the join condition ≡_{Attrs(E2)∪G}p. Note that as Attrs(J)⊂G. the existence of tuple <m, e₂> in E₂^mimplies the existence of v in V. The tuple <e₁, e₂> gets updated to <m, e₂> due to the list U₁in the refresh equation of V. This update affected by the refresh equation is correct because m changes e₁to m too, as Attrs(G)∪Attrs(U)=Attrs(E₁) and m and e₁have the same G attributes. Thus, all the updates into E₁are correctly propagated to V.

[0285] The refresh equation of V results in more updates in V. Let I⊂E₁be the set of tuples in E₁that are inserted into E₁(because they didn't find a ℑ₁in E₁). Consider i∈I. Each i results in the set of tuples E₂ⁱ=iE₂in V. Tuple <i, e₂>∈E₂ⁱcould only find a match v∈V due to the join condition ≡_Attrs(E)(Ë_e∈Attrs(E)LHS.e=NULL), in which case the tuple v is correctly updated to <i, e₂>. All the unmatched tuples in V are correctly inserted into V.

[0286] The changes in V=RS due to deletions from a base relation R cannot be summarized in an outerjoin-change table within our restricted definition of refresh operator. In this section, it is shown that deletions can be propagated from a base table through a outerjoin operator by keeping a count of duplicate tuples in one of the base relations and using an extended version of the refresh operator.

[0287] Let S^setdenote the expression π_{Attrs(S),Num=Count(*)}(S). In response to deletions ∇R from R, V, called an OJdeletion-change table, is defined as ∇RS^set. The view V is refreshed using the same refresh equation VV, where θ=(≡_Attrs(V), ≡_Attrs(S)) and U=<(A₁, 𝑓), . . . , (A_n, 𝑓)>, where 𝑓(x, y)=NULL for all x, y and {A₁, . . . , A_k}=Attrs(R). The refresh algorithm used to refresh the view Vusing its OJdeletion-change table is shown in Algorithm 2. The propagation equations of Table 3 for propagating aggregate-change tables through an outerjoin operator can be extended by introducing counts, in the manner described above.

[0288] Input

[0289] View V(A₁, . . . , A_n, B₁, . . . , B_m)

[0290] Change Table V(A₁, . . . , A_n, B₁, . . . , B_m, Num)

[0291] θ=(ℑ₁, ℑ₂), U=<(A₁, 𝑓₁), (A₂, 𝑓₂), . . . , (A_k, 𝑓_n)>

[0292] Output

[0293] Refresh table V, i.e., VV.

[0294] BEGIN

[0295] for each tuple v=(r, s, l) in V

[0296] Let {v₁, . . . , V_t} be the tuples in V that match

[0297] v due to the join condition ℑ₁.

[0298] if there is a tuple v′∈V such that v′∉{v₁, . . . , v₁} and

[0299] v′ matches with v due to the join condition ℑ₂

[0300] then

[0301] Delete tuples v₁, v₂, . . . , v₁from V;

[0302] else

[0303] Update each tuple v₁, v₂, . . . , v_Iin V using the

[0304] specifications in U;

[0305] endif

[0306] endfor

[0307] RETURN V

[0308] END

[0309] Note that the refresh operator doesn't need to query the sources and hence, can be executed very efficiently. Change propagation equations required for propagating an OJdeletion-change table through rational and aggregate operations can be appropriately defined.

[0310] Although specific embodiments of the present invention have been described, it will be understood by those of skill in the art that there are other embodiments that are equivalent to the described embodiments. Accordingly, it is to be understood that the invention is not to be limited by the specific illustrated embodiments, but only by the scope of the appended claims.