Method and mechanism for row versioning

• US 8,032,496 B2
• Filed: 09/03/2004
• Issued: 10/04/2011
• Est. Priority Date: 09/06/2003
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Sinha, Bipul, Ganesh, Amit, Raja, Vivekanandhan
• Primary Examiner(s): Mofiz; Apu
• Assistant Examiner(s): RAHMAN, MOHAMMAD N
• Application Number:
  US10/933,832
• Publication Number:
  US 20050114409A1
• Time in Patent Office: 2,587 Days
• Field of Search: 707200-206, 707 2- 4, 707/802, 707/806, 707/685, 707/695, 711/167, 714/15
• US Class Current: 707/685
• CPC Class Codes: G06F 16/219 Managing data history or versioning querying versioned data G06F16/2474; querying temporal data G06F16/2477

Abstract

Disclosed are algorithms, mechanisms, processes, mechanisms, and systems to implement a query upon past versions. In one approach, the version query can be defined as either “rowid”-based access or non-rowid based access. With rowid based access, a given rowid is used to identify the data for which past versions are desired. Also disclosed is a mechanism, system, interface, and process to allow specification of a new table decoration clause to obtain one or more past version of one or more data items. The version query provides for a direct interface to get multiple versions of the row(s). In addition, information about the past versions can be retrieved and displayed along with the past versions.

Claims

1. A computer implemented method for displaying past versions of a data item, comprising:

using at least a processor configured for performing a process, the process comprising;

receiving a request on a database table for one or more past versions of the data item that exist or would have existed over an interval, wherein

the database table comprises a current version of the data item without the one or more past versions of the data item, and

the request is directed at the database table and comprises a specification of the interval;

using at least a key, a range of keys, or one or more data blocks that are programmed for storing at least the data item in the database table by identifying one or more log records from a log of the database table; and

applying the one or more log records to at least a portion of the database table by using the key, the range of keys, or the one or more data blocks to restore the one or more past versions of the data item over the interval, wherein

the one or more log records comprise the one or more past versions and are used to reverse one or more changes made to the data item of the database table.

2. The method of claim 1 further comprising:

identifying a Previously Presented state for a past version based upon determining if a transaction identifier changes based upon applying the one or more log records.

3. The method of claim 1 in which a context object maintains a state during processing.

4. The method of claim 3 in which the context object stores a transaction identifier and a time value.

5. The method of claim 4 in which the context object is updated based upon an undo record having a different transaction identifier.

6. The method of claim 4 in which the time value comprises a SCN value.

7. The method of claim 1 in which the one or more log records comprise undo records.

8. The method of claim 7 in which the interval is bounded by an undo record retention period.

9. The method of claim 1 in which a primary key value is used to identify the data item for which past versions are desired.

10. The method of claim 9, in which the method further comprises:

determining one or more life intervals and physical addresses for the primary key; and

obtaining past versions at the physical addresses over the interval.

11. The method of claim 9 in which each of the physical addresses comprises a rowid value.

12. The method of claim 9 in which, for each log record that pertains to the primary key, determining if the log record corresponds to a change in transaction identifier, wherein a log record that both corresponds to the primary key and to the change in transaction identifier relates to a version of the data item.

13. The method of claim 1 in which a key range used to identify the data item for which past versions are desired.

14. The method of claim 13, in which the method further comprises:

determining one or more life intervals and physical addresses for keys in the key range; and

obtaining past versions at the physical addresses over the interval.

15. The method of claim 14 further comprising:

analyzing blocks associated with the key range;

splitting one or more ranges of keys to determine the one or more life intervals and physical addresses.

16. The method of claim 15 in which a separator key is identified to determine whether to split the one or more ranges of keys.

17. The method of claim 1 in which a physical address value is used to identify which past versions are desired.

18. The method of claim 17, in which the method further comprises:

for each log record, determining if the log record pertains to a slot associated with the rowid value.

19. The method of claim 17 in which, for each log record that pertains to the slot, determining if the log record corresponds to a change in transaction identifier, wherein a log record that both corresponds to the slot and to the change in transaction identifier relates to another version.

20. The method of claim 17 in which the rowid value is specified by a user.

21. The method of claim 20 in which a validity check is made for the rowid value.

22. The method of claim 17 in which the rowid value is provided by an index.

23. The method of claim 1 in which a table value is used to identify which past versions are desired, in which the table corresponds to one or more blocks.

24. The method of claim 23, the method further comprising:

generating versions of data within each of the one or more blocks.

25. The method of claim 24 in which, for each log record that pertains to a slot within a block, determining if the log record corresponds to a change in transaction identifier, wherein a log record that both corresponds to the slot and to the change in transaction identifier relates to another version.

26. The method of claim 24 in which the method is performed for each slot in the block.

27. The method of claim 1, in which the applying one or more log records to one or more blocks to restore one or more past versions of the data item over the interval continues until one of the one or more log records which corresponds to an event of interest is identified.

28. The method of claim 27, in which the event of interest comprises a structural change to at least one of the one or more data blocks, wherein the structural change comprises block split where a single data block for storing data items in the database table splits into a plurality of data blocks for storing the data items or multiple data blocks coalescence where two or more data blocks for storing the database table merge into a first single data block for storing the database table.

29. The computer implemented method of claim 1, the action for identifying the one or more log records from the log comprising:

determining one or more validity intervals for the key, the range of keys, or the one or more data blocks; and

applying the one or more log records only to one or more blocks that are associated with the key, the range of keys, or the one or more data blocks and the one or more validity intervals.

30. The computer implemented method of claim 29, the action for determining the one or more validity intervals for the key, the range of keys, or the one or more data blocks comprising:

determining a data item identifier for the data item in the database table for the key, the range of keys, or the one or more data blocks;

identifying an index block where the key, the range of keys, or the one or more data blocks existed or would have existed by traversing an index tree;

identifying the one or more blocks that are associated with the key, the range of keys, or the one or more data blocks where the key, the range of keys, or the one or more data blocks existed or would have existed by traversing an index tree for the database table; and

determining the one or more validity intervals within the interval by at least applying log records in the log in a first temporal order on the index block.

31. A computer program product comprising a tangible computer usable volatile or nonvolatile medium having executable code which, when executed by a computer processor, causes the processor to execute a process for displaying past versions of a data item, the process comprising:

using at least the computer processor configured for performing a process, the process comprising;

receiving a request on a database table, which is decorated with the table decoration clause, for one or more past versions of a data item that exist or would have existed over an interval, wherein

the database table comprises a current version of the data item without the one or more past versions of the data item, and

the request is directed at the database table and comprises a specification of the interval;

using at least a key, a range of keys, or one or more data blocks that are programmed for storing at least the data item in the database table by identifying one or more log records from a log of the database table; and

applying the one or more log records to at least a portion of the database table by using the key, the range of keys, or the one or more data blocks to restore the one or more past versions of the data item over the interval, wherein

the one or more log records comprise the one or more past versions and are used to reverse one or more changes made to the data item of the database table.

32. The computer program product of claim 31, in which a primary key value is used to identify the data item for which past versions are desired.

33. The computer program product of claim 32, in which the process further comprises:

determining one or more life intervals and physical addresses for the primary key; and

obtaining past versions at the physical addresses over the interval.

34. The computer program product of claim 31, in which a key range used to identify the data item for which past versions are desired.

35. The computer program product of claim 34, in which the process further comprises:

determining one or more life intervals and physical addresses for keys in the key range; and

obtaining past versions at the physical addresses over the interval.

36. The computer program product of claim 35, in which the process further comprises:

analyzing blocks associated with the key range;

splitting one or more ranges of keys to determine the one or more life intervals and physical addresses.

37. The computer program product of claim 31, the action for identifying the one or more log records from the log comprising:

determining one or more validity intervals for the key, the range of keys, or the one or more data blocks; and

applying the one or more log records only to one or more blocks that are associated with the key, the range of keys, or the one or more data blocks and the one or more validity intervals.

38. The computer program product of claim 37, the action for determining the one or more validity intervals for the key, the range of keys, or the one or more data blocks comprising:

determining a data item identifier for the data item in the database table for the key, the range of keys, or the one or more data blocks;

identifying an index block where the key, the range of keys, or the one or more data blocks existed or would have existed by traversing an index tree;

identifying the one or more blocks that are associated with the key, the range of keys, or the one or more data blocks where the key, the range of keys, or the one or more data blocks existed or would have existed by traversing an index tree for the database table; and

determining the one or more validity intervals within the interval by at least applying log records in the log in a first temporal order on the index block.

39. The system of claim 31, the action for identifying the one or more log records from the log comprising:

determining one or more validity intervals for the key, the range of keys, or the one or more data blocks; and

applying the one or more log records only to one or more blocks that are associated with the key, the range of keys, or the one or more data blocks and the one or more validity intervals.

40. The system of claim 39, the action for determining the one or more validity intervals for the key, the range of keys, or the one or more data blocks comprising:

determining a data item identifier for the data item in the database table for the key, the range of keys, or the one or more data blocks;

identifying an index block where the key, the range of keys, or the one or more data blocks existed or would have existed by traversing an index tree;

identifying the one or more blocks that are associated with the key, the range of keys, or the one or more data blocks where the key, the range of keys, or the one or more data blocks existed or would have existed by traversing an index tree for the database table; and

determining the one or more validity intervals within the interval by at least applying log records in the log in a first temporal order on the index block.

41. A system for displaying past versions of a data item, comprising:

at least a processor configured for performing a process, the process comprising;

receiving a request on a database table, which is decorated with the table decoration clause, for one or more past versions of the data item that exist or would have existed over an interval, wherein

the database table comprises a current version of the data item without the one or more past versions of the data item, and

the request is directed at the database table and comprises a specification of the interval;

using at least a key, a range of keys, or one or more data blocks that are programmed for storing at least the data item in the database table by identifying one or more log records from a log of the database table; and

applying one or more log records to at least a portion of the database table by using the key, the range of keys, or the one or more data blocks to restore the one or more past versions of the data item over the interval, in which

the one or more log records comprise the one or more past versions and are used to reverse one or more changes made to the data item of the database table.

42. The system of claim 41, in which a primary key value is used to identify the data item for which past versions are desired.

43. The system of claim 42, in which the process is further configured for performing:

determining one or more life intervals and physical addresses for the primary key; and

obtaining past versions at the physical addresses over the interval.

44. The system of claim 41, in which a key range used to identify the data item for which past versions are desired.

45. The system of claim 44, in which the processor is further configured for performing:

determining one or more life intervals and physical addresses for keys in the key range; and

obtaining past versions at the physical addresses over the interval.

46. The system of claim 45, in which the processor is further configured for performing:

analyzing blocks associated with the key range;

splitting one or more ranges of keys to determine the one or more life intervals and physical addresses.