Method and apparatus for refreshing materialized views

• US 20050091180A1
• Filed: 10/22/2003
• Published: 04/28/2005
• Est. Priority Date: 10/22/2003
• Status: Abandoned Application

• Current Assignee: Hewlett-Packard Development Company, L.P. (HP Inc.)
• Original Assignee: Hewlett-Packard Development Company, L.P. (HP Inc.)
• Inventors: Bar-or, Amir, Bortnikov, Edward, Peleg, Nitzan, Sherman, Yuval
• Application Number:
  US10/690,762
• Publication Number:
  US 20050091180A1
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2393 Updating materialised views

Abstract

The disclosed embodiments relate to a system and method for refreshing a materialized view that is at least in part derived from a table. The system may be adapted to provide availability of the table and the materialized view while the materialized view is being refreshed. The system may include a refresh log that contains a plurality of entries, each of the plurality of entries corresponding to a change in the table, each of the plurality of entries comprising an epoch identifier. The system may also include a refresh manager that performs a refresh operation on the materialized view in multiple steps by (a) successively reading a first subset of the plurality of entries indicated by a specific epoch identifier from the refresh log, (b) identifying a second subset of the plurality of entries from within the first subset of the plurality of entries, the second subset of the plurality of entries falling within a primary key value boundary and (c) applying the second subset of the plurality of entries to the materialized view.

Claims

1. A system that allows a table and a materialized view to be available while the materialized view is being refreshed, the system comprising:

a materialized view that is derived at least in part from a table;

a refresh log that contains a plurality of entries, each of the plurality of entries corresponding to a change in the table, each of the plurality of entries comprising an epoch identifier; and

a refresh manager that performs a refresh operation on the materialized view in multiple steps by (a) successively reading a first subset of the plurality of entries indicated by a specific epoch identifier from the refresh log, (b) identifying a second subset of the plurality of entries from within the first subset of the plurality of entries, the second subset of the plurality of entries falling within a primary key value boundary and (c) applying the second subset of the plurality of entries to the materialized view.

2. The system set forth in claim 1, wherein the corresponding epoch identifiers represent epoch numbers that have been created since a previous refresh operation on the materialized view.

3. The system set forth in claim 1, wherein the second subset of the plurality of entries is applied to the materialized view in a primary key order.

4. The system set forth in claim 1, wherein the refresh manager is adapted to distinguish between entries of the second subset of the plurality of entries that have already been applied to the materialized view in previous transactions and entries of the second subset of the plurality of entries that have not been applied to the materialized view in the event of a failure of the refresh operation.

5. A method of refreshing a materialized view that is in part derived from a table, the method being adapted to improve the availability of the table and the materialized view while the materialized view is being refreshed, the method comprising:

deriving a materialized view from at least one table;

assigning an epoch identifier to changes made to the at least one table;

storing an entry corresponding to each change to the at least one table in a refresh log that includes a plurality of entries, each of the plurality of entries comprising an epoch identifier; and

performing a refresh operation in multiple operations, each of the multiple operations comprising (a) successively reading a first subset of the plurality of entries indicated by a specific epoch identifier from the refresh log, (b) identifying a second subset of the plurality of entries from within the first subset of the plurality of entries, the second subset of the plurality of entries falling within a primary key value boundary and (c) applying the second subset of the plurality of entries to the materialized view.

6. The method set forth in claim 5, comprising applying the second subset of the plurality of entries to the materialized view in a primary key order.

7. The method set forth in claim 5, comprising defining the epoch identifier to correspond to changes that have been made to the table since a previous refresh operation on the materialized view.

8. The method set forth in claim 5, comprising distinguishing between entries of the second subset of the plurality of entries that have already been applied to the materialized view in previous transactions and entries of the second subset of the plurality of entries that have not been applied to the materialized view in the event of a failure of the refresh operation.

9. A system that provides availability of a table and a materialized view while the materialized view is being refreshed, the table being derived at least in part from the materialized view, the system comprising:

a refresh log that contains a plurality of entries; and

a refresh manager that computes a table delta based on the refresh log and applies the table delta to the materialized view.

10. The system set forth in claim 9, wherein each of the plurality of entries comprises an epoch identifier.

11. The system set forth in claim 10, wherein the epoch identifier corresponds to changes that have been made to the table since a previous refresh operation on the materialized view.

12. The system set forth in claim 9, wherein the table delta is applied to the materialized view in a primary key order.

13. The system set forth in claim 9, wherein the table delta is used to refresh the materialized view in multiple transactions.

14. The system set forth in claim 9, wherein a primary key value for each entry from the refresh log is recorded after that entry is applied to the materialized view.

15. The system for refreshing the materialized view set forth in claim 9, wherein the refresh manager is adapted to distinguish between a first subset of the plurality of entries that have already been applied to the materialized view in previous transactions and a second subset of the plurality of entries that have not been applied to the materialized view in the event of a failure of the refresh operation.

16. A method of refreshing a materialized view that is derived at least in part from a table, the method being adapted to provide availability of the table and the materialized view while the materialized view is being refreshed, the method comprising the acts of:

storing a plurality of entries corresponding to changes in the table in a refresh log;

computing a table delta based on the refresh log;

refreshing the materialized view based on the table delta.

17. The method set forth in claim 16, wherein the table delta is applied to the materialized view in a primary key order.

18. The method set forth in claim 16, comprising updating the materialized view in multiple transactions.

19. The method set forth in claim 16, comprising storing an epoch identifier as a portion of each of the plurality of entries.

20. The method set forth in claim 19, comprising defining the epoch identifier to correspond to changes that have been made to the table since a previous refresh operation on the materialized view.

21. The method set forth in claim 16, comprising recording the primary key value for each entry from the update log after that entry is applied to the materialized view.

22. The method set forth in claim 16, comprising distinguishing between a first subset of the plurality of entries that have already been applied to the materialized view in previous transactions and a second subset of the plurality of entries that have not been applied to the materialized view in the event of a failure of the act of refreshing the materialized view.

23. A system that provides availability of a table and a materialized view while the materialized view is being refreshed, the table being derived at least in part from the materialized view, the system comprising:

a refresh log that contains a plurality of entries; and

means for computing a table delta based on the refresh log; and

means for applying the contents of the table delta to the materialized view.

24. The system set forth in claim 23, wherein each of the plurality of entries comprises an epoch identifier.

25. The system set forth in claim 24, wherein the epoch identifier corresponds to changes that have been made to the table since a previous refresh operation on the materialized view.

26. The system set forth in claim 23, wherein the means for applying the table delta to the materialized view is adapted to distinguish between a first subset of the plurality of entries that have already been applied to the materialized view in previous transactions and a second subset of the plurality of entries that have not been applied to the materialized view in the event of a failure of applying the table delta to the materialized view.

27. A computer program, comprising:

a machine readable medium;

a refresh log stored on the machine readable medium, the refresh log containing a plurality of entries; and

a refresh manager stored on the machine readable medium, the refresh manager being adapted to refresh a materialized view that is derived at least in part from a table by computing a table delta based on the refresh log and applying the table delta to the materialized view.

28. The computer program set forth in claim 27, wherein each of the plurality of entries comprises an epoch identifier.

29. The computer program set forth in claim 28, wherein the epoch identifier corresponds to changes that have been made to the table since a previous refresh operation on the materialized view.

30. The computer program set forth in claim 27, wherein the refresh manager is adapted to distinguish between a first subset of the plurality of entries that have already been applied to the materialized view in previous transactions and a second subset of the plurality of entries that have not been applied to the materialized view in the event of a failure of a refresh operation.