DATABASE SYSTEM THAT PROVIDES FOR HISTORY-ENABLED TABLES

• US 20120191682A1
• Filed: 04/05/2012
• Published: 07/26/2012
• Est. Priority Date: 12/01/2005
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Hanckel, Robert, Ravada, Siva, BANERJEE, Jayanta
• Granted Patent: US 9,384,222 B2
• Time in Patent Office: Days
• US Class Current: 707/703
• 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 data structure that is to be history-enabled, the first data structure comprising a current row;

identifying or creating a current row data structure;

identifying or creating a row history data structure that comprises a history row which corresponds to the current row in the first data structure; and

tracking history of the first data structure without requiring accessing a redo log.

2. The computer implemented method of claim 1, in which the act of tracking the history of the first data structure comprises:

tracking a change in the current row of the first data structure; and

updating the current row data structure and the row history data structure based at least in part upon the change.

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

updating or inserting the history row with data in the current row of the first data structure prior to occurrence of the change.

4. The computer implemented method of claim 3, in which the act of tracking the history of the first data structure further comprises:

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

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

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 data structure comprises:

identifying a transaction that gives rise to the change in the current row of the first data structure;

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 data structure.

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

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

7. The computer implemented method of claim 6, 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.

8. The computer implemented method of claim 2, in which the act of tracking the history of the first data structure further comprises:

updating a first history row in the row history data structure or creating a second history row in the row history data structure based at least in part upon the change in the current row.

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

identifying or creating a database view for both the current row data structure and the row history data structure.

10. The computer implemented method of claim 9, the process further comprising:

performing a temporary query on the database view for a period of time for which one or more rows in the database view that satisfy the temporary query are desired; and

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

11. The computer implemented method of claim 10, in which the act of performing the temporary query on the database view is performed on the database view rather than on the first data structure or the row history data structure.

12. The computer implemented method of claim 10, 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 temporary query result.

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

14. The computer implemented method of claim 1, wherein the database view is created on a union of both the current row data structure and the row history data structure.

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

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

updating a first history row in the row history data structure or creating a second history row in the row history data structure based at least in part upon the change in the current row.

17. 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 data structure that is to be history-enabled, the first data structure comprising a current row;

identifying or creating a current row data structure;

identifying or creating a row history data structure that comprises a history row which corresponds to the current row in the first data structure; and

tracking history of the first data structure without requiring accessing a redo log.

18. The article of manufacture of claim 17, the process further comprising:

tracking a change in the current row of the first data structure; and

updating the current row data structure and the row history data structure based at least in part upon the change.

19. The article of manufacture of claim 18, in which the process of tracking the history of the first data structure further comprises:

updating or inserting the history row with data in the current row of the first data structure prior to occurrence of the change.

20. The article of manufacture of claim 19, in which the process of tracking the history of the first data structure further comprises:

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

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

21. The article of manufacture of claim 20, in which the process of updating or inserting the start time data of the current row in the first data structure comprises:

identifying a transaction that gives rise to the change in the current row of the first data structure;

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 data structure.

22. The article of manufacture of claim 21, in which the process of updating or inserting the start time data of the current row in the first data structure further comprises:

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

23. The article of manufacture of claim 17, the process further comprising:

identifying or creating a database view for both the current row data structure and the row history data structure.

24. The article of manufacture of claim 23, the process further comprising:

performing a temporary query on the database view for a period of time for which one or more rows in the database view that satisfy the temporary query are desired; and

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

25. An apparatus for creating and maintaining a history-enabled data structure in a relational database system, the apparatus comprising:

at least one processor that is to;

identify a first data structure that is to be history-enabled, the first data structure comprising a current row;

identify or create a current row data structure;

identify or create a row history data structure that comprises a history row which corresponds to the current row in the first data structure; and

track history of the first data structure without requiring accessing a redo log.

26. The at least one processor of claim 25, the at least one processor is further to:

track a change in the current row of the first data structure; and

update the current row data structure and the row history data structure based at least in part upon the change.

27. The system of claim 26, in which the at least one processor that is to track the history of the first data structure is further to:

update or insert the history row with data in the current row of the first data structure prior to occurrence of the change.

28. The system of claim 27, in which the at least one processor that is to track the history of the first data structure is further to:

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

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

29. The system of claim 28, in which the at least one processor that is to update or insert the start time data of the current row in the first data structure is further to:

identify a transaction that gives rise to the change in the current row of the first data structure;

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 data structure.

30. The system of claim 29, in which the at least one processor that is to update or insert the start time data of the current row in the first data structure is further to:

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

31. The system of claim 25, the at least one processor that is further to:

identify or create a database view for both the current row data structure and the row history data structure.

32. The system of claim 31, the at least one processor that is to is further to:

perform a temporary query on the database view for a period of time for which one or more rows in the database view that satisfy the temporary query are desired; and

generate a temporary query result with the one or more rows in the database view for the period of time from the database view.