Method and mechanism for row versioning

• US 20050114409A1
• Filed: 09/03/2004
• Published: 05/26/2005
• 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
• Granted Patent: US 8,032,496 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• 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. -26. (canceled)

27. A method for displaying past versions of a data item, comprising:

receiving a request for a past version of a data item over an interval;

applying one or more log records against one or more blocks to retrieve one or more past versions of the data item over the interval; and

presenting information about the past versions.

28. The method of claim 27 further comprising:

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

29. The method of claim 27 in which a context object maintains state during processing.

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

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

32. The method of claim 30 in which the time value comprises a SCN value.

33. The method of claim 27 in which the one or more log records comprise undo records.

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

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

36. The method of claim 35, 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.

37. The method of claim 35 in which each of the physical addresses comprises a rowid value.

38. The method of claim 35 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.

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

40. The method of claim 39, 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.

41. The method of claim 40 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.

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

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

44. The method of claim 43, in which the method further comprises:

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

45. The method of claim 43 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.

46. The method of claim 43 in which the rowid value is specified by a user.

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

48. The method of claim 43 in which the rowid value is provided by an index.

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

50. The method of claim 49, the method further comprising:

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

51. The method of claim 50 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.

52. The method of claim 50 in which the method is performed for each slot in the block.

53. A computer program product comprising a computer usable medium having executable code to execute a process for displaying past versions of a data item, the process comprising the steps of:

receiving a request for a past version of a data item over an interval;

applying one or more log records against one or more blocks to retrieve one or more past versions of the data item over the interval; and

presenting information about the past versions.

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

logic for receiving a request for a past version of a data item over an interval;

logic applying one or more log records against one or more blocks to retrieve one or more past versions of the data item over the interval; and

logic presenting information about the past versions.

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

logic for receiving a request for a past version of a data item over an interval;

logic for determining a type of access for the request, the type of access selected from access based upon a key, based upon a key range, based upon a physical address, based upon a block, or based upon a table;

logic for applying one or more log records against one or more blocks to retrieve one or more past versions of the data item over the interval, the one or more blocks identified based upon determining the type of access; and

logic for presenting information about the past versions.

56. A computer program product comprising a computer usable medium having executable code to execute a process for displaying past versions of a data item, the process comprising the steps of:

receiving a request for a past version of a data item over an interval;

determining a type of access for the request, the type of access selected from access based upon a key, based upon a key range, based upon a physical address, based upon a block, or based upon a table;

applying one or more log records against one or more blocks to retrieve one or more past versions of the data item over the interval, the one or more blocks identified based upon determining the type of access; and

presenting information about the past versions.