Database system that provides for history-enabled tables

• US 9,384,222 B2
• Filed: 04/05/2012
• Issued: 07/05/2016
• Est. Priority Date: 12/01/2005
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Banerjee, Jayanta, Hanckel, Robert, Ravada, Siva
• Primary Examiner(s): SYED, FARHAN M
• Application Number:
  US13/440,702
• Publication Number:
  US 20120191682A1
• Time in Patent Office: 1,552 Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/217 Database tuning G06F16/2282 takes precedence; database performance monitoring G06F11/3409

Abstract

Techniques for history enabling a table in a database system so that past versions of rows of the history-enabled table are available for temporal querying. The table is history enabled by adding a start time column to the table and creating a history table for the history-enabled table. The start time field'"'"'s value in a row of the history-enabled table indicates when the contents of the row last changed. The rows of the history table are copies of rows of the history-enabled table that have been deleted or updated. The rows include end time fields whose values indicate when the row was updated or deleted. A history table'"'"'s row thus indicates a period in which the history table'"'"'s row was in the history-enabled table. Temporal queries are performed on a view which is the union of the history-enabled table and the history table.

Claims

1. A computer implemented method for creating and maintaining a history-enabled data structure in a relational database system, the method comprising:

using at least one processor to perform a process, the process comprising;

identifying a first database table that is history-enabled by modifying a schema for the first database table to include a start time column, the first database table comprising a current row;

creating a history row in a history table to correspond to the current row when the current row is modified in the first database table, wherein the history row is not created by obtaining information from a redo log having a set of redo records;

creating a history view by at least determining a union of the first database table and the history table, wherein the history view is a relational data structure having a plurality of history view rows, a history view start time column, and a history view end time column, wherein history view rows from the first database table have history view start time column values from the start up time column of the respective rows of the first database table;

performing a temporal query on the history view by using a SQL query; and

generating a temporal query result with one or more rows in the history view for a period of time from the history view.

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

tracking the first database table by updating or inserting the history row with data in the current row prior to occurrence of the current row being modified.

3. The computer implemented method of claim 2, in which the act of tracking the history of the first database table further comprises:

updating or inserting a row period value in the history table with start time data of the current row of the first database table-and a change time data associated with the change; and

updating or inserting the start time data of the current row in the first database table with the change time data associated with the change.

4. The computer implemented method of claim 3, in which the act of updating or inserting the start time data of the current row in the first database table comprises:

identifying a transaction that gives rise to the change in the current row of the first database table;

identifying a transaction identifier for the transaction;

determining a commit time at which the transaction is committed based at least in part upon the transaction identifier; and

updating or inserting the commit time as the start time data for the current row in the first database table.

5. The computer implemented method of claim 4, in which the act of updating or inserting the start time data of the current row in the first database table further comprises:

setting a first time point during execution of the transaction but before the transaction is committed.

6. The computer implemented method of claim 5, in which the first time point corresponds to a data manipulation language operation that is associated with the change and occurs prior to the commit time of the transaction.

7. The computer implemented method of claim 6, in which the act of tracking the history of the first database table further comprises:

updating the history row in the history table by updating or inserting the commit time as the start time data table.

8. The computer implemented method of claim 1, the process further comprising:

identifying a row that corresponds to a row period value which overlaps at least a part of the period of time; and

classifying the row as the one or more rows for the temporal query result.

9. The computer implemented method of claim 1, wherein the current row is stored in a current row table, and the current row table comprises a start time column that corresponds to the current row in the first database table and comprises information that indicates a time point at which data in the current row becomes current.

10. The computer implemented method of claim 1, wherein the history table includes a period column having a row period value that indicates a first period of time during which a corresponding row exists or existed.

11. An article of manufacture comprising a non-transitory computer readable medium having stored thereupon a sequence of operations which, when executed by at least one processor, causes the at least one processor to perform a method for creating and maintaining a history-enabled data structure in a relational database system, the method comprising:

using at least one processor to perform a process, the process comprising;

identifying a first database table that is history-enabled, the first database table comprising a current row;

creating a history row in a history table to correspond to the current row when the current row is modified in the first database table, wherein the history row is not created by obtaining information from a redo log having a set of redo records;

creating a history view by at least determining a union of the first database table and the history table, wherein the history view is a relational data structure having a plurality of history view rows, a history view start time column, and a history view end time column, wherein history view rows from the first database table have history view start time column values from the start up time column of the respective rows of the first database table;

performing a temporal query on the history view by using a SQL query; and

generating a temporal query result with one or more rows in the history view for a period of time from the history view.

12. The article of manufacture of claim 11, in which the process further comprises:

tracking the first database table by updating or inserting the history row with data in the current row prior to occurrence of the current row being modified.

13. The article of manufacture of claim 12, in which the process of tracking the history of the first database table further comprises:

updating or inserting a row period value in the history table with start time data of the current row of the first database table and a change time data associated with the change; and

updating or inserting the start time data of the current row in the first database table with the change time data associated with the change.

14. The article of manufacture of claim 13, in which the process of updating or inserting the start time data of the current row comprises:

identifying a transaction that gives rise to the change in the current row of the first database table;

identifying a transaction identifier for the transaction;

determining a commit time at which the transaction is committed based at least in part upon the transaction identifier; and

updating or inserting the commit time as the start time data for the current row in the first database table.

15. The article of manufacture of claim 14, in which the process of updating or inserting the start time data of the current row in the first database table further comprises:

setting a first time point during execution of the transaction but before the transaction is committed.

16. A system for creating and maintaining a history-enabled data structure in a relational database system, the system comprising:

at least one processor that is to;

identify a first database table that is history-enabled by modifying a schema for the first database table to include a start time column, the first database table comprising a current row;

create a history row in a history table to correspond to the current row when the current row is modified in the first database table, wherein the history row is not created by obtaining information from a redo log having a set of redo records;

create a history view by at least determining a union of the first database table and the history table, wherein the history view is a relational data structure having a plurality of history view rows, a history view start time column, and a history view end time column, wherein history view rows from the first database table have history view start time column values from the start up time column of the respective rows of the first database table;

perform a temporal query on the history view by using a SQL query; and

generate a temporal query result having one or more rows from the history view for a period of time in the history view.

17. The system of claim 16, in which the at least one processor is further to:

tracking the first database table by update or insert the history row with data in the current row prior to occurrence of the current row being modified.

18. The system of claim 17, in which the at least one processor that is to track the history of the first database table is further to:

update or insert a row period value in the history table with start time data of the current row of the first database table and a change time data associated with the change; and

update or insert the start time data of the current row in the first database table with the change time data associated with the change.

19. The system of claim 18, in which the at least one processor that is to update or insert the start time data of the current row in the first database table is further to:

identify a transaction that gives rise to the change in the current row of the first database table;

identify a transaction identifier for the transaction;

determine a commit time at which the transaction is committed based at least in part upon the transaction identifier; and

update or inserting the commit time as the start time data for the current row in the first database table.

20. The system of claim 19, in which the at least one processor that is to update or insert the start time data of the current row in the first database table is further to:

set a first time point during execution of the transaction but before the transaction is committed.