Patent US6125360A successfully refreshed from Google Patents.
US6125360A
Expired / Lapsed
US
⏱️ 0.0 years remaining
Incremental maintenance of materialized views containing one-to-N lossless joins
Assignee: Oracle International Corp
•
Filing: 1998-07-02
•
Granted: 2000-09-26
•
Expires: 2018-07-02
Abstract
Abstract A method and apparatus are provided for performing incremental refreshes to materialized views defined by one-to-N lossless joins. Each base table of the materialized view is selected to be processed as the current "selected table". The processing of the current selected table varies depending on whether the selected table is the right side table of an outer join. If the selected table is not the right table of an outer join, then the selected table is processed by (1) deleting rows from the materialized view based on rows of the selected table that have been updated or deleted in the selected table during the batch window, and (2) inserting rows into the materialized view based on updates and inserts into the selected table that occurred during the batch window. If the selected table is the right table of an outer join, then changes made to the selected table are processed in a way that reduces the number of changes that have to be made to the materialized view. According to one embodiment of the invention, operations performed during the incremental refresh are performed by issuing database statements (e.g. SQL queries) to a database server. The incremental refresh techniques described herein are "memoryless" in that they do not require a record of the sequence of changes that were made during a batch window. Techniques are described for performing the incremental refresh steps through the use of database commands and queries.
Inventors
- 👤 Andrew Witkowski
- 👤 Karl Dias
Bibliographic Details
Application Number
—
Jurisdiction
US
Data Source
google_patents
Last Synchronized
2026-11-05 19:25
CPC Classifications
G; G06; G06F; G06F16/00; G06F16/20; G06F16/24; G06F16/245; G06F16/2455; G06F16/24553; G06F16/21; G06F16/217; Y; Y10; Y10S; Y10S707/00; Y10S707/99931; Y10S707/99932; Y10S707/99933; Y10S707/99934; Y10S707/99936; Y10S707/99941; Y10S707/99943; Y10S707/99944
Claims Analysis
Total Claims: 38
Claim 1
Independent Claim
174 words
A method for performing, after a batch window, an incremental refresh of a materialized view defined by a lossless one-to-n 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 updating the materialized view to reflect deletions to the selected table by processing each row (Tj) that combines with a changed row in the selected table as follows if Tj only combines in the materialized view with changed rows of the selected table, then the rows in the materialized view that contain Tj are removed from the materialized view and replaced with a row in which selected columns are set to NULL; and if Tj combines in the materialized view with both changed and unchanged rows of the selected table, then the rows in the materialized view that result from Tj combining with changed rows are deleted, leaving only the rows in which Tj combines with unchanged rows of the selected table.
Claim 2
Independent Claim
174 words
A method for performing, after a batch window, an incremental refresh of a materialized view defined by a lossless one-to-n 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 updating the materialized view to reflect deletions to the selected table by processing each row (Tj) that combines with a changed row in the selected table as follows if Tj only combines in the materialized view with changed rows of the selected table, then the rows in the materialized view that contain Tj are removed from the materialized view and replaced with a row in which selected columns are set to NULL; and if Tj combines in the materialized view with both changed and unchanged rows of the selected table, then the rows in the materialized view that result from Tj combining with changed rows are deleted, leaving only the rows in which Tj combines with unchanged rows of the selected table.
Claim 3
Dependent Claim
77 words
The method of claim 1 further comprising the steps of: 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 during the batch window; and inserting rows into the materialized view based on updates and inserts into the selected table that occurred during the batch window.
Claim 4
Dependent Claim
115 words
The method of claim 1 further comprising the steps of: if the selected table is the right table of an outer join, then updating the materialized view to reflect insertions to the selected table by performing the steps of removing from the materialized view any rows that exist in the materialized view for rows from the left side of the outer join that, prior to the batch window, did not combine with any rows of the selected table but which, after the batch window, combine with at least one changed row of the selected table; and inserting into the materialized view rows formed by combinations that result from the changed rows in the selected table.
Claim 5
Dependent Claim
38 words
The method of claim 1 wherein the step of replacing with a row in which selected columns are set to NULL includes replacing with a row in which columns corresponding to OJGraph(Ti), where Ti is the selected table.
Claim 6
Dependent Claim
60 words
The method of claim 1 wherein the step of updating the materialized view to reflect deletions to the selected table includes the step of setting to NULL selected columns of rows in the materialized view that are derived from rows in left side of the join (Tj) that join with exactly one row in the selected table that was deleted.
Claim 7
Dependent Claim
75 words
The method of claim 3 wherein the step of updating the materialized view to reflect insertions to the selected table includes the step of setting NULL values in selected columns of a particular set of rows in the materialized view to new values, where the particular set of rows includes those rows that are derived from rows in left side of the join (Tj) that join with exactly one changed row in the selected table.
Claim 8
Dependent Claim
27 words
The method of claim 1 wherein the step of updating the materialized view to reflect deletions to the selected table is performed by executing queries on tables.
Claim 9
Dependent Claim
77 words
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 updating the materialized view based on the set of changed rows.
Claim 10
Dependent Claim
66 words
The method of claim 1 further comprising the steps of: determining whether, during the batch window, the only changes made to the selected table were inserts; and if the selected table is the right table of an outer join and the only changes made to the selected table during the batch window were inserts, then skipping the step of updating the materialized view to reflect deletions.
Claim 11
Dependent Claim
66 words
The method of claim 2 further comprising the steps of: determining whether, during the batch window, the only changes made to the selected table were inserts; and if the selected table is not the right table of an outer join and the only changes made to the selected table during the batch window were inserts, then skipping the step of deleting rows from the materialized view.
Claim 12
Dependent Claim
76 words
The method of claim 3 further comprising the steps of: determining whether, during the batch window, the only changes made to the selected table were deletes or drop partitions; and if the selected table is the right table of an outer join and the only changes made to the selected table during the batch window were deletes or drop partitions, then skipping the step of updating the materialized view to reflect insertions to the selected table.
Claim 13
Dependent Claim
99 words
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 during the batch window 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; and deleting the rows of interest for an inner join.
Claim 14
Dependent Claim
86 words
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.
Claim 15
Independent Claim
144 words
A method for performing, after a batch window, an incremental refresh of a materialized view defined by a lossless one-to-n 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 updating the materialized view to reflect insertions to the selected table by performing the steps of removing from the materialized view any rows that exist in the materialized view for rows from the left side of the outer join that, prior to the batch window, did not combine with any rows of the selected table but which, after the batch window, combine with at least one changed row of the selected table; and inserting into the materialized view rows formed by combinations that result from the changed rows in the selected table.
Claim 16
Independent Claim
144 words
A method for performing, after a batch window, an incremental refresh of a materialized view defined by a lossless one-to-n 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 updating the materialized view to reflect insertions to the selected table by performing the steps of removing from the materialized view any rows that exist in the materialized view for rows from the left side of the outer join that, prior to the batch window, did not combine with any rows of the selected table but which, after the batch window, combine with at least one changed row of the selected table; and inserting into the materialized view rows formed by combinations that result from the changed rows in the selected table.
Claim 17
Dependent Claim
77 words
The method of claim 14 further comprising the steps of: 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 during the batch window; and inserting rows into the materialized view based on updates and inserts into the selected table that occurred during the batch window.
Claim 18
Dependent Claim
177 words
A computer-readable medium bearing instructions for performing an incremental refresh, after a batch window, of a materialized, lossless, one-to-n 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 updating the materialized view to reflect deletions to the selected table by processing each row (Tj) that combines with a changed row in the selected table as follows if Tj only combines in the materialized view with changed rows of the selected table, then the rows in the materialized view that contain Tj are removed from the materialized view and replaced with a row in which selected columns are set to NULL; and if Tj combines in the materialized view with both changed and unchanged rows of the selected table, then the rows in the materialized view that result from Tj combining with changed rows are deleted, leaving only the rows in which Tj combines with unchanged rows of the selected table.
Claim 19
Dependent Claim
177 words
A computer-readable medium bearing instructions for performing an incremental refresh, after a batch window, of a materialized, lossless, one-to-n 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 updating the materialized view to reflect deletions to the selected table by processing each row (Tj) that combines with a changed row in the selected table as follows if Tj only combines in the materialized view with changed rows of the selected table, then the rows in the materialized view that contain Tj are removed from the materialized view and replaced with a row in which selected columns are set to NULL; and if Tj combines in the materialized view with both changed and unchanged rows of the selected table, then the rows in the materialized view that result from Tj combining with changed rows are deleted, leaving only the rows in which Tj combines with unchanged rows of the selected table.
Claim 20
Dependent Claim
81 words
The computer-readable medium of claim 16 further comprising instructions for performing the steps of: 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 during the batch window; and inserting rows into the materialized view based on updates and inserts into the selected table that occurred during the batch window.
Claim 21
Dependent Claim
119 words
The computer-readable medium of claim 16 further comprising instructions for performing the steps of: if the selected table is the right table of an outer join, then updating the materialized view to reflect insertions to the selected table by performing the steps of removing from the materialized view any rows that exist in the materialized view for rows from the left side of the outer join that, prior to the batch window, did not combine with any rows of the selected table but which, after the batch window, combine with at least one changed row of the selected table; and inserting into the materialized view rows formed by combinations that result from the changed rows in the selected table.
Claim 22
Dependent Claim
39 words
The computer-readable medium of claim 16 wherein the step of replacing with a row in which selected columns are set to NULL includes replacing with a row in which columns corresponding to OJGraph(Ti), where Ti is the selected table.
Claim 23
Dependent Claim
61 words
The computer-readable medium of claim 16 wherein the step of updating the materialized view to reflect deletions to the selected table includes the step of setting to NULL selected columns of rows in the materialized view that are derived from rows in left side of the join (Tj) that join with exactly one row in the selected table that was deleted.
Claim 24
Dependent Claim
76 words
The computer-readable medium of claim 18 wherein the step of updating the materialized view to reflect insertions to the selected table includes the step of setting NULL values in selected columns of a particular set of rows in the materialized view to new values, where the particular set of rows includes those rows that are derived from rows in left side of the join (Tj) that join with exactly one changed row in the selected table.
Claim 25
Dependent Claim
28 words
The computer-readable medium of claim 16 wherein the step of updating the materialized view to reflect deletions to the selected table is performed by executing queries on tables.
Claim 26
Dependent Claim
81 words
The computer-readable medium of claim 16 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 updating the materialized view based on the set of changed rows.
Claim 27
Dependent Claim
70 words
The computer-readable medium of claim 16 further comprising instructions for performing the steps of: determining whether, during the batch window, the only changes made to the selected table were inserts; and if the selected table is the right table of an outer join and the only changes made to the selected table during the batch window were inserts, then skipping the step of updating the materialized view to reflect deletions.
Claim 28
Dependent Claim
70 words
The computer-readable medium of claim 17 further comprising instructions for performing the steps of: determining whether, during the batch window, the only changes made to the selected table were inserts; and if the selected table is not the right table of an outer join and the only changes made to the selected table during the batch window were inserts, then skipping the step of deleting rows from the materialized view.
Claim 29
Dependent Claim
80 words
The computer-readable medium of claim 18 further comprising instructions for performing the steps of: determining whether, during the batch window, the only changes made to the selected table were deletes or drop partitions; and if the selected table is the right table of an outer join and the only changes made to the selected table during the batch window were deletes or drop partitions, then skipping the step of updating the materialized view to reflect insertions to the selected table.
Claim 30
Dependent Claim
104 words
The computer-readable medium of claim 16 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 computer-readable medium further includes instructions for performing the steps of determining when the only change to the selected table during the batch window 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; and deleting the rows of interest for an inner join.
Claim 31
Dependent Claim
91 words
The computer-readable medium of claim 16 wherein the materialized view includes a plurality of base tables, the computer-readable medium further comprising instructions for performing 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.
Claim 32
Dependent Claim
153 words
A computer-readable medium comprising sequences of instructions for performing an incremental refresh, after a batch window, of a materialized view defined by a lossless one-to-n join, the computer-readable medium comprising 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 updating the materialized view to reflect insertions to the selected table by performing the steps of removing from the materialized view any rows that exist in the materialized view for rows from the left side of the outer join that, prior to the batch window, did not combine with any rows of the selected table but which, after the batch window, combine with at least one changed row of the selected table; and inserting into the materialized view rows formed by combinations that result from the changed rows in the selected table.
Claim 33
Dependent Claim
153 words
A computer-readable medium comprising sequences of instructions for performing an incremental refresh, after a batch window, of a materialized view defined by a lossless one-to-n join, the computer-readable medium comprising 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 updating the materialized view to reflect insertions to the selected table by performing the steps of removing from the materialized view any rows that exist in the materialized view for rows from the left side of the outer join that, prior to the batch window, did not combine with any rows of the selected table but which, after the batch window, combine with at least one changed row of the selected table; and inserting into the materialized view rows formed by combinations that result from the changed rows in the selected table.
Claim 34
Dependent Claim
81 words
The computer-readable medium of claim 29 further comprising instructions for performing the steps of: 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 during the batch window; and inserting rows into the materialized view based on updates and inserts into the selected table that occurred during the batch window.
Claim 35
Dependent Claim
265 words
A database system including: a plurality of base tables; a one-to-n lossless materialized join view derived from said base tables; a mechanism for identifying changes that occur to said base tables during a batch window; 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 updating the materialized view to reflect deletions to the selected table by processing each row (Tj) that combines with a changed row in the selected table as follows if Tj only combines in the materialized view with changed rows of the selected table, then the rows in the materialized view that contain Tj are removed from the materialized view and replaced with a row in which selected columns are set to NULL; and if Tj combines in the materialized view with both changed and unchanged rows of the selected table, then the rows in the materialized view that result from Tj combining with changed rows are deleted, leaving only the rows in which Tj combines with unchanged rows of the selected table; and 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 during the batch window; and inserting rows into the materialized view based on updates and inserts into the selected table that occurred during the batch window.
Claim 36
Dependent Claim
265 words
A database system including: a plurality of base tables; a one-to-n lossless materialized join view derived from said base tables; a mechanism for identifying changes that occur to said base tables during a batch window; 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 updating the materialized view to reflect deletions to the selected table by processing each row (Tj) that combines with a changed row in the selected table as follows if Tj only combines in the materialized view with changed rows of the selected table, then the rows in the materialized view that contain Tj are removed from the materialized view and replaced with a row in which selected columns are set to NULL; and if Tj combines in the materialized view with both changed and unchanged rows of the selected table, then the rows in the materialized view that result from Tj combining with changed rows are deleted, leaving only the rows in which Tj combines with unchanged rows of the selected table; and 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 during the batch window; and inserting rows into the materialized view based on updates and inserts into the selected table that occurred during the batch window.
Claim 37
Dependent Claim
235 words
A database system including: a plurality of base tables; a one-to-n lossless materialized join view derived from said base tables; a mechanism for identifying changes that occur to said base tables during a batch window; 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 updating the materialized view to reflect insertions to the selected table by performing the steps of removing from the materialized view any rows that exist in the materialized view for rows from the left side of the outer join that, prior to the batch window, did not combine with any rows of the selected table but which, after the batch window, combine with at least one changed row of the selected table; and inserting into the materialized view rows formed by combinations that result from the changed rows in the selected table; and 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 during the batch window; and inserting rows into the materialized view based on updates and inserts into the selected table that occurred during the batch window.
Claim 38
Dependent Claim
235 words
A database system including: a plurality of base tables; a one-to-n lossless materialized join view derived from said base tables; a mechanism for identifying changes that occur to said base tables during a batch window; 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 updating the materialized view to reflect insertions to the selected table by performing the steps of removing from the materialized view any rows that exist in the materialized view for rows from the left side of the outer join that, prior to the batch window, did not combine with any rows of the selected table but which, after the batch window, combine with at least one changed row of the selected table; and inserting into the materialized view rows formed by combinations that result from the changed rows in the selected table; and 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 during the batch window; and inserting rows into the materialized view based on updates and inserts into the selected table that occurred during the batch window.
References Cited (Backward Citations)
USPTO Annuity & Maintenance Windows
| Annuity Stage | Window Opens | Payment Deadline | Surcharge Period Until | Estimated Fee | Status |
|---|---|---|---|---|---|
| 3.5 Years (Year 4) | 2003-09-26 | 2004-03-27 | 2004-09-26 | $2000.00 | Window Closed / Lapsed |
| 7.5 Years (Year 8) | 2007-09-26 | 2008-03-27 | 2008-09-26 | $3760.00 | Window Closed / Lapsed |
| 11.5 Years (Year 12) | 2011-09-26 | 2012-03-27 | 2012-09-26 | $7700.00 | Window Closed / Lapsed |