System, structure, interface, and semantics for implementing row versions: accessing past versions of a data item

• US 7,941,408 B2
• Filed: 09/03/2004
• Issued: 05/10/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: Jacobs, Kenneth R., Raja, Vivekanandhan, Sinha, Bipul, Ganesh, Amit
• Primary Examiner(s): Padmanabhan; Kavita
• Application Number:
  US10/934,079
• Publication Number:
  US 20050120062A1
• Time in Patent Office: 2,440 Days
• Field of Search: 707/203, 707/2, 707/201, 707/200, 707/167, 707/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 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. Also disclosed are algorithms, mechanisms, and processes that are employed 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 process for determining one or more life intervals when given a key.

Claims

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

using a computing system that comprises at least one processor and is configured or programmed for performing a process comprising;

receiving a request on the database table of a database system for one or more past versions of a data item over an interval, wherein

the request comprises a specification of the interval,

the database system comprises the database table and a log record which comprises an undo log record for the database system that is stored separately from the database table and is used to reverse one or more changes made to the database table, and

the database table comprises the data item;

applying a portion of the log record to the database table to create one or more prior-version database tables for the database table that comprise the one or more past versions, wherein

each of the database table and the one or more prior-version database tables comprises a single version of the data item

retrieving the one or more past versions of the data item over the interval by querying against the one or more prior-version database tables that were created by applying the portion of the log record to the database table to identify the one or more past versions over the interval from the one or more prior-version database tables; and

presenting information about the one or more past versions over the interval.

2. The method of claim 1 in which the database decoration clause of the request identifies either logical time or temporal time for the interval.

3. The method of claim 2 in which the logical time relates to a SCN-based interval.

4. The method of claim 2 in which the temporal time relates to a timestamp-based interval.

5. The method of claim 1 in which the information comprises the one or more past versions.

6. The method of claim 1 in which the information comprises start time/SCN values, end time/SCN values, transaction identifier for the transaction that produces the version, or operation that resulted in the version.

7. The method of claim 1 in which the interval comprises an open interval.

8. The method of claim 1 in which the interval comprises a closed interval.

9. The method of claim 1 in which the interval has a minimum and a maximum interval value.

10. The method of claim 9 in which a default minimum interval value comprises a time when no undo records are available.

11. The method of claim 9 in which a default maximum interval value comprises a present time at which the request is executed.

12. The method of claim 1 in which the request is executed as of a particular point in time.

13. The method of claim 1 in which the request comprises a SQL query having the following syntax:

VERSIONS BETWEEN {SCN|TIMESTAMP} MINVALUE AND MAXVALUE.

14. The method of claim 1 in which the request comprises a SQL query having the following syntax:

[VERSIONS BETWEEN {SCN|TIMESTAMP} expr AND expr] AS OF {SCN|TIMESTAMP} expr.

15. The method of claim 1 in which a view is decorated based upon the information about the past versions.

16. The method of claim 1 in which a view definition uses the information about the past versions.

17. The method of claim 1 in which the request is a SQL statement.

18. The method of claim 1 in which the information about the past versions is not produced across changes in table specifications.

19. The method of claim 1 in which an update operations is translated as an insert and a delete operation.

20. The method of claim 19 in which the request is operated upon an index organized table (IOT).

21. The method of claim 1, in which the interval may extend beyond a time period for which one or more log records are maintained.

22. The method of claim 1, in which the database decoration clause enables a user to decorate a table of a query statement to retrieve one or more past versions of the data item within the interval.

23. The computer implemented method of claim 1, further comprising:

applying a table decoration clause to the database table to facilitate the act of retrieving the one or more past versions of the data item over the interval, wherein

the table decoration clause causes to identify all versions of the data item that exist or ever existed within the interval.

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

receiving a request on the database table of a database system for one or more past versions of a data item over an interval, wherein

the request comprises a specification of the interval,

the database system comprises the database table and a log record which comprises an undo log record for the database system that is stored separately from the database table and is used to reverse one or more changes made to the database table, and

the database table comprises the data item;

applying a portion of the log record to the database table to create one or more prior-version database tables for the database table that comprise the one or more past versions, wherein

each of the database table and the one or more the prior-version database tables comprises a single version of the data item;

retrieving the one or more past versions of the data item over the interval by querying against the one or more prior-version database tables that were created by applying the portion of the log record to the database table to identify the one or more past versions over the interval from the one or more prior-version database tables; and

presenting information about the one or more past versions over the interval.

25. The computer program product of claim 24, in which the request of the process comprises a SQL query defining the following syntax:

VERSIONS BETWEEN {SCN|TIMESTAMP} MINVALUE AND MAXVALUE.

26. The computer program product of claim 24, in which the request of the process comprises a SQL query defining the following syntax:

[VERSIONS BETWEEN {SCN|TIMESTAMP} expr AND expr] AS OF {SCN|TIMESTAMP} expr.

27. A system for displaying past versions of a data item in a database table, comprising:

a computing system that comprises at least one processor and is configured or programmed for performing a process comprising;

receiving a request on the database table of a database system for one or more past versions of a data item over an interval, wherein

the request comprises a specification of the interval,

the database system comprises the database table and a log record which comprises an undo log record for the database system that is stored separately from the database table and is used to reverse one or more changes made to the database table, and

the database table comprises the data item;

applying a portion of the log record to the database table to create one or more prior-version database tables for the database table that comprise the one or more past versions, wherein

each of the database table and the one or more prior-version database tables comprises a single version of the data item;

retrieving the one or more past versions of the data item over the interval by querying against the one or more prior-version database tables that were created by applying the portion of the log record to the database table to identify the one or more past versions over the interval from the one or more prior-version database tables; and

presenting information about the one or more past versions over the interval.

28. The system of claim 27, in which the request comprises a SQL query having the following syntax:

VERSIONS BETWEEN {SCN|TIMESTAMP} MINVALUE AND MAXVALUE.

29. The system of claim 27, in which the request comprises a SQL query having the following syntax:

[VERSIONS BETWEEN {SCN|TIMESTAMP} expr AND expr] AS OF {SCN|TIMESTAMP} expr.