Fast refresh of snapshots

• US 5,963,959 A
• Filed: 05/30/1997
• Issued: 10/05/1999
• Est. Priority Date: 05/30/1997
• Status: Expired due to Term

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle Corporation
• Inventors: Souder, Benny, Downing, Alan, Sun, Harry
• Primary Examiner(s): Fetting, Anton
• Assistant Examiner(s): Corrielus, Jean M.
• Application Number:
  US08/865,645
• Time in Patent Office: 858 Days
• Field of Search: 707/201, 707/200, 707/104, 707/100, 707/102, 707/103, 707/2
• US Class Current: 1/1
• CPC Class Codes: G06F 16/273 Asynchronous replication or reconciliation
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface
• CPC Class Codes: Y10S 707/99945 Object-oriented database structure processing
• CPC Class Codes: Y10S 707/99952 Coherency, e.g. same view to multiple users

Abstract

A method and apparatus employs primary key values stored in a master table to drive a fast refresh mechanism for a snapshot defined on the master table. A primary key is a set of columns in the master table having a combined value that uniquely identifies each row in the master table. When rows of the master table are modified, the primary key values of the modified rows are recorded in a master log. In response to a fresh command, differences between the master table and snapshot are reconciled based on primary key values stored in the master table, the master log, and the snapshot.

Claims

1. A method of refreshing a snapshot, defined by a snapshot definition query that references a master table having a primary key, said master table located at a master site, said method comprising the computer-implemented steps of:

detecting a modification of a row of said master table;

in response to detecting said modification of said row of said master table, recording the primary key value stored in said row of said master table in a master log; and

in response to initiation of a refresh operation, reconciling differences between said snapshot and said master table based on primary key values stored in said master log, in said master table, and in said snapshot.

2. The method of claim 1, further comprising the step of validating that each row of said master table stores a primary key value and that each said primary key value is unique within said master table.

3. The method of claim 1, wherein the step of detecting a modification of a row of said master table includes the step of detecting an insertion of said row into said master table.

4. The method of claim 1, wherein the step of detecting a modification of a row of said master table includes the step of detecting a deletion of said row from said master table.

5. The method of claim 1, wherein:

the step of detecting a modification of a row of said master table includes the step of detecting an update of said row having an old primary key value before the update and a new primary key value after the update in said master table; and

the step of recording the primary key value stored in said row of said master table in a master log includes the steps of;

recording the old primary key value in said master log and

if the new primary key value is not equal to the old primary key value, then recording the new primary key value in said master log.

6. The method of claim 1, wherein the step of reconciling differences between said snapshot and said master table based on primary key values stored in said master log, master table, and said snapshot includes the steps of:

creating a master view at said master site defined by said snapshot definition query on said master table;

removing a first refresh row from said snapshot based on primary key values stored in said master view, said master log, and said snapshot;

updating a second refresh row of said snapshot based on primary key values stored in said master view, said master log, and said snapshot; and

inserting a third refresh row into said snapshot based on primary key values stored in said master view, said master log, and said snapshot.

7. The method of claim 6, wherein the step of removing a first refresh row from said snapshot based on primary key values stored in said master view, said master log, and said snapshot includes the steps of:

getting a primary key value from said master log, wherein said primary key value is not stored in any row of said master view and said first refresh row of said snapshot has said primary key value; and

deleting said first refresh row from said snapshot.

8. The method of claim 6, wherein the step of updating a second row of said snapshot based on primary key values stored in said master view, said master log, and said snapshot includes the steps of:

getting a primary key value from said master log, wherein said primary key value is stored in a current row of said master view;

searching for said second refresh row of said snapshot having said primary key value;

if said second refresh row is found, then updating said second refresh row of said snapshot with values of said current row of said master view.

9. The method of claim 8, wherein the step of inserting a third refresh row into said snapshot based on primary key values stored in said master view, said master log, and said snapshot includes the step of if said second refresh row is not found, then inserting said current row of master view as said third refresh row into said snapshot.

10. The method of claim 6, wherein the step of inserting a third refresh row into said snapshot based on primary key values stored in said master view, said master log, and said snapshot includes the steps of:

getting a primary key value from said master log, wherein said primary key value is stored in a current row of said master view;

searching for a refresh row of said snapshot having said primary key value; and

if said refresh row is not found, then inserting said current row of master view as said third refresh row into said snapshot.

11. The method of claim 1, further comprising the steps of:

detecting a modification of a row of said snapshot;

in response to detecting said modification of a row of said snapshot, performing the steps of;

propagating said modification to said master table and

recording the primary key value stored in said row of said snapshot in a snapshot log with a modification indication; and

wherein the step of reconciling differences between said snapshot and said master table based on primary key values stored in said master log, master table, and said snapshot includes the step of reconciling differences between said snapshot and said master table based on primary key values stored in said master log, master table, said snapshot, and said snapshot log.

12. The method of claim 11, wherein:

the step of detecting a modification of a row of said snapshot includes the step of detecting an insertion of said row into said snapshot and

the step of recording the primary key value stored in said row of said snapshot in a snapshot log with a modification indication includes the step of recording the primary key value stored in said row of snapshot in said snapshot log with an insertion indication.

13. The method of claim 11, wherein:

the step of detecting a modification of a row of said snapshot includes the step of detecting a deletion of said row from said snapshot and

the step of recording the primary key value stored in said row of said snapshot in a snapshot log with a modification indication includes the step of recording the primary key value stored in said row of snapshot in said snapshot log with a deletion indication.

14. The method of claim 11, wherein:

the step of detecting a modification of a row of said snapshot includes the step of detecting an update of said row having an old primary key value before the update and a new primary key value after the update in said snapshot; and

the step of recording the primary key value stored in said row of said snapshot in a snapshot log with a modification indication includes the steps of;

recording said new primary key value in said snapshot log with an update indication; and

if said new primary key value is not equal to said old primary key value, then recording said old primary key value in said snapshot log with an update indication.

15. The method of claim 11, wherein:

the step of detecting a modification of a row of said snapshot includes the steps of;

detecting an insertion of a first modified row into said snapshot;

detecting a deletion of a second modified row from said snapshot; and

detecting an update of a third modified row having an old primary key value before the update and a new primary key value after the update in said snapshot; and

the step of recording the primary key value stored in said row of said snapshot in a snapshot log with a modification indication includes the steps of;

recording the primary key value stored in said first modified row of snapshot in said snapshot log with an insertion indication;

recording the primary key value stored in said second modified row of snapshot in said snapshot log with a deletion indication

recording said new primary key value in said snapshot log with an update indication; and

if the new primary key value is not equal to the old primary key value, then recording the old primary key value in said snapshot log with said update indication.

16. The method of claim 15, wherein the step of reconciling differences between said snapshot and said master table based on primary key values stored in said master log, master table, said snapshot, and said snapshot log includes the steps of:

creating a master view defined by said snapshot definition query on said master table;

removing a first refresh row from said snapshot based on primary key values stored in said master view, said master log, said snapshot log, and said snapshot;

updating a second refresh row of said snapshot based on primary key values stored in said master view, said master log, said snapshot log, and said snapshot; and

inserting a third refresh row into said snapshot based on primary key values stored in said master view, said master log, said snapshot log, and said snapshot.

17. The method of claim 16, wherein the step of removing a first refresh row from said snapshot based on primary key values stored in said master view, said master log, said snapshot log, and said snapshot includes the steps of:

getting a first primary key value from said snapshot log, wherein said first primary key value is not stored in any row of said master view; and

deleting said first refresh row having said second primary key value from said snapshot;

getting a second primary key value from said master log, wherein said second primary key value is not stored in any row of said master view;

deleting a second row having said second primary key value from said snapshot;

getting a third primary key value from said snapshot log, wherein said third primary key value is stored with said insertion indication; and

deleting a third row having said third primary key value from said snapshot.

18. The method of claim 16, wherein the step of updating a second refresh row of said snapshot based on primary key values stored in said master view, said master log, said snapshot log, and said snapshot includes the steps of:

getting a first primary key value from said master log, wherein said first primary key value is stored in a first current row of said master view;

searching for a first row of said snapshot having said first primary key value; and

if said first row of said snapshot is found, then updating said first row of said snapshot with said first current row of said master view;

getting a second primary key value from said snapshot log, wherein said second primary key value is stored in a second current row of said master view;

searching for said second row of said snapshot, wherein said second row of said snapshot has stored therein said second primary key value; and

if said second row of said snapshot is found, then updating said second row of said snapshot with said second current row of said master view.

19. The method of claim 18, wherein the step of inserting a third refresh row into said snapshot based on primary key values stored in said master view, said master log, said snapshot log, and said snapshot includes the steps of:

if said second row of said snapshot is not found, then inserting said second current row of said master view as said third refresh row into said snapshot; and

if said first row of said snapshot is not found, then inserting said first current row of said master view as a fourth refresh row into said snapshot.

20. The method of claim 16, wherein the step of inserting a third refresh row into said snapshot based on primary key values stored in said master view, said master log, said snapshot log, and said snapshot includes the steps of:

getting a first primary key value from said master log, wherein said first primary key value is stored in a first current row of said master view;

searching for a first row of said snapshot having said first primary key value; and

if said first row of said snapshot is not found, then inserting said first current row of said master view as a fourth refresh row into said snapshot;

getting a second primary key value from said snapshot log, wherein said second primary key value is stored in a second current row of said master view;

searching for said second row of said snapshot, wherein said second row of said snapshot has stored therein said second primary key value; and

if said second row of said snapshot is not found, then inserting said second current row of said master view as said third refresh row into said snapshot.

21. The method of claim 1, further comprising the steps of:

in response to detecting said modification of said row of said master table, recording a default value for a modification timestamp in said master log; and

in response to initiation of said refresh operation, performing the steps of;

generating a refresh timestamp, indicating when said refresh operation was initiated;

resetting modification timestamps of said default value stored in said master log to said refresh timestamp;

purging rows from said master log based on said refresh timestamp, and the modification timestamp recorded in each of said rows.

22. The method of claim 21, further comprising the steps of:

registering administrative information about said snapshot; and

recording a reference to said refresh timestamp with said administrative information.

23. The method of claim 1, further comprising the step of recording the rowid of said row of said master table in said master log in response to detecting said modification of said row of said master table.

24. The method of claim 23, wherein the step of reconciling differences between said snapshot and said master table based on primary key values stored in said master log, master table, and said snapshot includes the step of reconciling differences between said snapshot and said master table based on primary key values and rowids stored in said master log and said snapshot and based on primary key values stored in and rowids of said master table.

25. The method of claim 23, further comprising the step of in response to initiation of a second refresh operation, reconciling differences between a second snapshot and said master table based on rowids stored in said master log and said second snapshot and based on rowids of said master table.

26. The method of claim 1, further comprising the step of refreshing a second snapshot, defined by a second snapshot definition query that references a second master table that is located at said master site, wherein the step of refreshing said second snapshot includes the steps of:

detecting a second modification of a second row of said second master table;

in response to detecting said second modification of said second row of said second master table, recording the rowid of said second row of said second master table in a second master log; and

in response to initiation of a second refresh operation, reconciling differences between said second snapshot and said second master table, based on rowids recorded in said second master log and said second snapshot and based on the rowids of said second master table.

27. A computer readable medium having stored thereon sequences of instructions for refreshing a snapshot, residing at a snapshot site, defined by a snapshot definition query that references a master table, residing a master site and having a primary key, said sequences of instructions includes sequences of instructions for performing the steps of:

detecting a modification of a row of said master table;

in response to detecting said modification of said row of said master table, recording the primary key value stored in said row of said master table in a master log; and

in response to initiation of a refresh operation, transmitting the primary key value of a refresh row to said snapshot site based on primary key values stored in said master log and in said master table.

28. The computer readable medium of claim 27, further comprising sequences of instructions for performing the step of validating that each row of said master table stores a primary key value and that each said primary key value is unique within said master table.

29. The computer readable medium of claim 27, wherein the step of detecting a modification of a row of said master table includes the step of detecting an insertion of said row into said master table.

30. The computer readable medium of claim 27, wherein the step of detecting a modification of a row of said master table includes the step of detecting a deletion of said row from said master table.

31. The computer readable medium of claim 27, wherein:

the step of detecting a modification of a row of said master table includes the step of detecting an update of said row having an old primary key value before the update and a new primary key value after the update in said master table; and

the step of recording the primary key value stored in said row of said master table in a master log includes the steps of;

recording the old primary key value in said master log and

if the new primary key value is not equal to the old primary key value, then recording the new primary key value in said master log.

32. The computer readable medium of claim 27, wherein the step of transmitting the primary key value of a refresh row to said snapshot site based on primary key values stored in said master log and in said master table includes the steps of:

creating a master view defined by said snapshot definition query on said master table;

determining the primary key value stored in a refresh row from said snapshot based on primary key values stored in said master view and said master log; and

transmitting said primary key value to said snapshot site whereby causing said refresh row to be updated.

33. The computer readable medium of claim 32, wherein the step of determining the primary key value stored in a refresh row from said snapshot based on primary key values stored in said master view and said master log includes the step of getting a primary key value from said master log, wherein said primary key value is not stored in any row of said master view.

34. The computer readable medium of claim 32, wherein the step of determining the primary key value stored in a refresh row from said snapshot based on primary key values stored in said master view and said master log includes the step of getting a primary key value from said master log, wherein said primary key value is stored in a current row of said master view.

35. The computer readable medium of claim 27, further comprising sequences of instructions for performing the steps of:

in response to detecting said modification of said row of said master table, recording a default value for a modification timestamp in said master log; and

in response to initiation of said refresh operation, performing the steps of;

generating a refresh timestamp, indicating when said refresh operation was initiated;

resetting modification timestamps of said default value stored in said master log to said refresh timestamp;

purging rows from said master log based on said refresh timestamp, and the modification timestamp recorded in each of said rows.

36. The computer readable medium of claim 35, further comprising sequences of instructions for performing the steps of:

registering administrative information about said snapshot; and

recording a reference to said refresh timestamp with said administrative information.

37. The computer readable medium of claim 27, further comprising sequences of instructions for performing the step of recording the rowid of said row of said master table in said master log.

38. The computer readable medium of claim 37, wherein the step transmitting the primary key value of a refresh row to said snapshot site based on primary key values stored in said master log and in said master table includes the step of transmitting the primary key value of said refresh row to said snapshot site based on primary key values and rowids stored in said master log and said snapshot and based on primary key values stored in and rowids of said master table.

39. The computer readable medium of claim 37, further comprising sequences of instructions for performing the step of transmitting the rowid of a second refresh row to a second snapshot site based on primary key values and rowids stored in said master log and a second snapshot and based on primary key values stored in and rowids of said master table.

40. The computer readable medium of claim 27, further comprising sequences of instructions for performing the step of refreshing a second snapshot, defined by a second snapshot definition query that references a second master table that is located at said master site, wherein the step of refreshing said second snapshot includes the steps of:

detecting a second modification of a second row of said second master table;

in response to detecting said second modification of said second row of said second master table, recording the rowid of said second row of said second master table in a second master log; and

in response to initiation of a second refresh operation, reconciling differences between said second snapshot and said second master table, based on rowids recorded in said second master log and said second snapshot and based on the rowids of said second master table.