Incremental maintenance of materialized views containing one-to-one lossless joins

• US 6,134,543 A
• Filed: 07/02/1998
• Issued: 10/17/2000
• Est. Priority Date: 07/02/1998
• Status: Expired due to Term

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle Corporation
• Inventors: Dias, Karl, Witkowski, Andrew
• Primary Examiner(s): Alam, Hosain T.
• Assistant Examiner(s): ALAM, SHAHID AL
• Application Number:
  US09/109,782
• Time in Patent Office: 838 Days
• Field of Search: 707/2, 707/3, 707/4, 707/6-7, 707/10, 705/26, 705/27, 705/34, 706/16, 709/204, 717/2
• US Class Current: 1/1
• CPC Class Codes: G06F 16/217 Database tuning G06F16/2282 takes precedence; database performance monitoring G06F11/3409
• CPC Class Codes: G06F 16/24553 of query operations
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99936 Pattern matching access

Abstract

A method and apparatus are provided for performing incremental refreshes to materialized views defined by one-to-one lossless joins. Each base table of the materialized view is selected to be processed as the current "selected table". During the processing, if the selected table is the right table of an outer join, then selected columns of rows of the materialized view are set to NULL based on rows of the selected table that have been updated or deleted in the selected table after a prior refresh operation. Then, NULL values in selected columns of the materialized view are updated to reflect new values caused by updates and inserts into the selected table that occurred after the prior refresh operation. If the selected table is not the right table of an outer join, then rows from the materialized view are deleted based on rows of the selected table that have been updated or deleted in the selected table after the prior refresh operation. Then, rows are inserted into the materialized view based on updates and inserts into the selected table that occurred after the prior refresh operation. The incremental refresh techniques are "memoryless" in that they do not require a record of the sequence of changes that were made subsequent to the prior refresh operation. Techniques are described for performing the incremental refresh steps through the use of database commands and queries.

Claims

1. A method for performing an incremental refresh of a materialized view defined by a lossless one-to-one join, the method comprising the steps of:

establishing a base table of the materialized view as a selected table;

if the selected table is the right table of an outer join, then performing the steps of

setting to NULL selected columns of rows of the materialized view based on rows of the selected table that have been updated or deleted in the selected table after a prior refresh operation;

updating NULL values in selected columns of the materialized view to reflect new values caused by updates and inserts into the selected table that occurred after the prior refresh operation;

if the selected table is not the right table of an outer join, then performing the steps of

deleting rows from the materialized view based on rows of the selected table that have been updated or deleted in the selected table after the prior refresh operation; and

inserting rows into the materialized view based on updates and inserts into the selected table that occurred after the prior refresh operation.

2. The method of claim 1 wherein the steps of setting to NULL, updating NULL values, deleting rows and inserting rows are performed by executing queries on tables.

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

storing change data that indicates changes made to base tables of the materialized view without tracking the order of the changes,

identifying a set of changed rows based on the change data, the set of changed rows including rows of the selected table that have been changed since a previous refresh operation;

performing the step of deleting rows from the materialized view based on the set of changed rows;

identifying a set of still-existing changed rows, said set of still-existing changed rows including those rows where changed after the prior refresh operation but still exist in the selected table; and

performing the step of inserting rows into the materialized view based on the set of still-existing changed rows.

4. The method of claim 1 further comprising the steps of:

determining whether, since the prior refresh operation, the only changes made to the selected table were inserts;

if the selected table is the right table of an outer join and the only changes made to the selected table after the prior refresh operation were inserts, then skipping the step of setting to NULL selected columns;

if the selected table is not the right table of an outer join and the only changes made to the selected table after the prior refresh operation were inserts, then skipping the step of deleting rows from the materialized view.

5. The method of claim 1 further comprising the steps of:

determining whether, since the prior refresh operation, the only changes made to the selected table were deletes or drop partitions;

if the selected table is the right table of an outer join and the only changes made to the selected table after the prior refresh operation were deletes or drop partitions, then skipping the step of updating NULL values;

if the selected table is not the right table of an outer join and the only changes made to the selected table after the prior refresh operation were deletes or drop partitions, then skipping the step of inserting rows into the materialized view.

6. The method of claim 1 wherein:

rowids from base tables of the materialized view are stored in the materialized view;

the rowids stored in the materialized view indicate the partitions to which corresponding rows base tables belong;

the method further includes the steps of

determining when the only change to the selected table since the prior refresh operation is that a partition was dropped;

inspecting the materialized view to identify rows of interest in the materialized view, said rows of interest being rows that include values from the dropped partition;

deleting the rows of interest for an inner join; and

setting to NULL selected columns of the rows of interest for an outer join.

7. The method of claim 1 wherein the materialized view includes a plurality of base tables, the method further comprising the steps of:

performing said incremental refresh operation by iteratively processing each of said plurality of base tables as said selected table;

detecting a crash during the incremental refresh operation prior to processing all of the plurality of base tables; and

after said crash, processing each base table again as said selected table without regard to whether the base table had been processed prior to said crash.

8. The method of claim 1 wherein the step of setting to NULL is performed by performing operations specified in the following statement:

space="preserve" listing-type="tabular">______________________________________ update MV set columns-of(OJGraph(Ti) = null where MV.Ti.sub.-- rid in (select rid from Dlt.sub.-- Ti) ______________________________________

wherein MV is the materialized view,

OJGraph(Ti) is the part of the join query that defines the materialized view that is reachable from the selected table by either inner joins or left outer joins,

MV.Ti_-- rid is a column in the materialized view that stores unique identifiers for rows from the selected table, and

Dlt_-- Ti is the set of changed rows from the selected table.

9. The method of claim 1 wherein the step of updating null values is performed by performing operations specified in the following statement:

space="preserve" listing-type="tabular">______________________________________ update MV (select mv-columns-of(OJGraph(Ti)), columns-of(OJGraph(Ti)) from tables-of(POJGraph(Ti)), MV where join-conditions-of(POJGraph(Ti)) and Ti.rowid in (select rid from Dlt.sub.-- Ti) and MV.Tj.sub.-- rid = Tj.rowid ) v set columns-of(OJGraph(Ti)) = columns-of(OJGraph(Ti)) ______________________________________

wherein MV is the materialized view,

Ti represents the selected table;

Ti is the right side of the join Tj→

Ti,

OJGraph(Ti) is the part of the join query that defines the materialized view that is reachable from the selected table by either inner joins or left outer joins,

POJGraph(Ti) is the subgraph Tj→

OJGraph(Ti),

MV.Ti_-- rid is a column in the materialized view that stores unique identifiers for rows from the selected table, and

Dlt_-- Ti is the set of changed rows from the selected table.

10. The method of claim 1 wherein the step of deleting rows is performed consistent with the statement:

delete MV

where

MV.Ti_-- rid in (select rid from Dlt_-- Ti);

wherein MV is the materialized view,

MV.Ti_-- rid is a column in the materialized view that stores unique identifiers for rows from the selected table, and

Dlt_-- Ti is the set of changed rows from the selected table.

11. The method of claim 1 wherein the step of inserting rows is performed by executing a modified view definition query, wherein the modified view definition query is the view definition query for the materialized view in which references to the selected table are replaced by references to a row source that includes the rows that have been updated and inserted into the selected table since the prior refresh operation.

12. A computer-readable medium bearing instructions for performing an incremental refresh of a materialized, lossless, one-to-one join view, the instructions including instructions for performing the steps of:

establishing a base table of the materialized view as a selected table;

if the selected table is the right table of an outer join, then performing the steps of

setting to NULL selected columns of rows of the materialized view based on rows of the selected table that have been updated or deleted in the selected table after a prior refresh operation;

updating NULL values in selected columns of the materialized view to reflect new values caused by updates and inserts into the selected table that occurred after the prior refresh operation;

if the selected table is not the right table of an outer join, then performing the steps of

deleting rows from the materialized view based on rows of the selected table that have been updated or deleted in the selected table after the prior refresh operation; and

inserting rows into the materialized view based on updates and inserts into the selected table that occurred after the prior refresh operation.

13. The computer readable medium of claim 12 wherein the steps of setting to NULL, updating NULL values, deleting rows and inserting rows are performed by executing queries on tables.

14. The computer readable medium of claim 12 further including instructions for performing the steps of:

storing change data that indicates changes made to base tables of the materialized view without tracking the order of the changes,

identifying a set of changed rows based on the change data, the set of changed rows including rows of the selected table that have been changed since a previous refresh operation;

performing the step of deleting rows from the materialized view based on the set of changed rows;

identifying a set of still-existing changed rows, said set of still-existing changed rows including those rows where changed after the prior refresh operation but still exist in the selected table; and

performing the step of inserting rows into the materialized view based on the set of still-existing changed rows.

15. The computer readable medium of claim 12 further comprising instructions for performing the steps of:

determining whether, since the prior refresh operation, the only changes made to the selected table were inserts;

if the selected table is the right table of an outer join and the only changes made to the selected table after the prior refresh operation were inserts, then skipping the step of setting to NULL selected columns;

if the selected table is not the right table of an outer join and the only changes made to the selected table after the prior refresh operation were inserts, then skipping the step of deleting rows from the materialized view.

16. The computer readable medium of claim 12 further comprising instructions for performing the steps of:

determining whether, since the prior refresh operation, the only changes made to the selected table were deletes or drop partitions;

if the selected table is the right table of an outer join and the only changes made to the selected table after the prior refresh operation were deletes or drop partitions, then skipping the step of updating NULL values;

if the selected table is not the right table of an outer join and the only changes made to the selected table after the prior refresh operation were deletes or drop partitions, then skipping the step of inserting rows into the materialized view.

17. A database system including:

a plurality of base tables;

a one-to-one lossless materialized join view derived from said base tables;

a mechanism for identifying changes that occur to said base tables after a prior refresh operation;

a database server configured to incrementally refresh said materialized view by performing the steps of;

establishing a base table of the materialized view as a selected table;

if the selected table is the right table of an outer join, then performing the steps of

setting to NULL selected columns of rows of the materialized view based on rows of the selected table that have been updated or deleted in the selected table after a prior refresh operation;

updating NULL values in selected columns of the materialized view to reflect new values caused by updates and inserts into the selected table that occurred after the prior refresh operation;

if the selected table is not the right table of an outer join, then performing the steps of

deleting rows from the materialized view based on rows of the selected table that have been updated or deleted in the selected table after the prior refresh operation; and

inserting rows into the materialized view based on updates and inserts into the selected table that occurred after the prior refresh operation.

18. The database system of claim 17 wherein the database server is configured to perform the steps of setting to NULL, updating NULL values, deleting rows and inserting rows are performed by executing queries on tables.

19. The database system of claim 17 wherein the database server is further configured to perform the steps of:

storing change data that indicates changes made to base tables of the materialized view without tracking the order of the changes,

identifying a set of changed rows based on the change data, the set of changed rows including rows of the selected table that have been changed since a previous refresh operation;

performing the step of deleting rows from the materialized view based on the set of changed rows;

identifying a set of still-existing changed rows, said set of still-existing changed rows including those rows where changed after the prior refresh operation but still exist in the selected table; and

performing the step of inserting rows into the materialized view based on the set of still-existing changed rows.

20. The database system of claim 17 wherein the database server is further configured to perform the steps of:

determining whether, since the prior refresh operation, the only changes made to the selected table were inserts;

if the selected table is the right table of an outer join and the only changes made to the selected table after the prior refresh operation were inserts, then skipping the step of setting to NULL selected columns;

if the selected table is not the right table of an outer join and the only changes made to the selected table after the prior refresh operation were inserts, then skipping the step of deleting rows from the materialized view.

21. The database system of claim 17 wherein the database server is further configured to perform the steps of:

determining whether, since the prior refresh operation, the only changes made to the selected table were deletes or drop partitions;

if the selected table is the right table of an outer join and the only changes made to the selected table after the prior refresh operation were deletes or drop partitions, then skipping the step of updating NULL values;

if the selected table is not the right table of an outer join and the only changes made to the selected table after the prior refresh operation were deletes or drop partitions, then skipping the step of inserting rows into the materialized view.