Database system that provides for history-enabled tables

• US 8,156,083 B2
• Filed: 12/01/2005
• Issued: 04/10/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: Banerjee, Jayanta, Hanckel, Robert, Ravada, Siva
• Primary Examiner(s): Jalil, Naveen Abel
• Assistant Examiner(s): SYED, FARHAN M
• Application Number:
  US11/291,446
• Publication Number:
  US 20070130226A1
• Time in Patent Office: 2,322 Days
• Field of Search: None
• US Class Current: 707/684
• 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 of creating and maintaining a history-enabled table in a relational database system, comprising:

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

identifying or creating a current row table by adding a start time column to a pre-existing table that comprises a current column in a relational database system and is to be history enabled;

identifying or creating a row history table in the relational database system that includes a period column and a history column corresponding to the current column in the current row table;

tracking history of the pre-existing table without reading an undo log;

responding to a change in a field in a current row of the current row table, the field being in the current column corresponding to the history column of the row history table, by at least;

creating or updating a history row of the row history table to include a data of the field prior to the change in the field, in which

the row history table comprising a row period value that indicates a time period between a start time value of the current row of the current row table and a change time at which the change occurs; and

updating the start time value of the current row in the current row table to the change time at which the change occurs;

creating a history view that comprises a database view of a union of the current row table and the row history table, wherein the history view includes a period column, and a row period value in the period column is determined from the current row table and indicates a period of time that has not yet ended; and

performing a temporal query on the history view by using at least a temporal query function defined by the relational database management system, wherein the temporal query function comprises a function that takes a period of time as a parameter and returns a set of rows of the history view for which the period of time overlaps another period indicated by the row period value of one of the set of rows of the row history table.

2. The history-enabled table set forth in claim 1, wherein the current row includes a row period field, and the row period value of the row period in the current row indicates a period that has not yet ended.

3. The history-enabled table set forth in claim 1, wherein

the history row further includes a start time field;

when the current row is copied into a given history row, the start time value of the current row is copied into a corresponding start time field corresponding to the given history row; and

the row period value comprises an end time value that is set when the history row is being copied to indicate a time of an update or deletion of the current row that is copied.

4. The history-enabled table set forth in claim 3, wherein the current row includes a row period field for the row period value, and the row period value indicates an end time value that indicates a period that has not yet ended.

5. The method of claim 1, wherein

the current row table comprises a set of current rows and is determined by adding the start time column to the pre-existing table;

the row history table comprises a set of history rows; and

the history-enabled table comprises a view which comprises a union of the current row table and the row history table.

6. The history-enabled table set forth in claim 5, wherein the view is materialized.

7. The history-enabled table set forth in claim 5, wherein the current row table and the row history table comprises respective base tables.

8. The history-enabled table set forth in claim 5, wherein the start time value and one or more times of one or more updates or deletions comprise commit times.

9. The history-enabled table set forth in claim 5, wherein the start time value and one or more times of updates or deletions comprise one or more DML times.

10. The history-enabled table set forth in claim 9, wherein the one or more DML times are later converted to one or more corresponding commit times.

11. The history-enabled table set forth in claim 1, wherein

the current row in the pre-existing table comprises a current value of a data item instead of a prior value of the data item.

12. The history-enabled table set forth in claim 5, wherein in the union, the period column whose one or more row period values indicate a time period that has not yet ended is added to the set of current rows.

13. An apparatus for creating and maintaining a history-enabled table in a relational database management system, the apparatus comprising:

at least one processor;

a memory;

a pre-existing table in the relational database system that is to be history-enabled;

a history-enabled table creator that is to use the at least one processor to make a current row table by adding a start time column to the current row table comprising a column, corresponding to a current row in the pre-existing table, and to create a row history table that has a period column and a history row, the history row corresponding to the current row in the current row table, wherein

the history-enabled table creator that is further to create a history view which comprises a database view of a union of the current row table and the row history table, and

the history view is to include the period column, in which a row period value in the period column is determined from the current row table and indicates a period of time that has not yet ended;

a history-enabled table maintainer that is to use the at least one processor to respond to a change in a field in the current row of the current row table and to track history of the pre-existing table without reading an undo log, in which

the history-enabled table maintainer is to update or create the history row in the row history table to include a data of the field prior to the change in the field,

the row history table is to comprise a row period value that indicates a time period between a start time value of the current row of the current row table and a change time at which the change occurs, and

the history-enabled table maintainer is to update the start time value of the current row in the current row table to the change time at which the change occurs; and

a query engine that is to perform a temporal query on the history view by using at least a temporal query function that is defined by the relational database management system, wherein the temporal query function comprises a function that takes a period of time as a parameter and returns a set of rows of the history view for which the period of time overlaps another period indicated by the row period value of one of the set of rows of the row history table.

14. The apparatus set forth in claim 13, wherein

the row history table as created by the history-enabled table further has a start time column;

the row period value is to indicate an end time value at which the change in the current row occurs; and

the table maintainer is further to copy copies the copied current table row'"'"'s a start time value of the current row in current row table to a corresponding field of the start time column of the history table.

15. The apparatus set forth in claim 13, further comprising:

a query engine that is to perform the temporal query on at least the row history table by using at least the temporal query function defined by the relational database management system.

16. The apparatus set forth in claim 13, wherein the temporal query function comprises a function that takes an instant of time as a parameter and returns a set of rows of the history view for which the instant of time is included in a period indicated by the row period value of one of the set of rows of the row history table.

17. The apparatus set forth in claim 13, wherein the temporal query function comprises a function that takes a period of time as a parameter and returns a set of rows of the history view for which the period of time overlaps another period indicated by the row period value of one of the set of rows of the row history table.

18. The apparatus for creating and maintaining a history-enabled table set forth in claim 13, wherein the table maintainer is to use a commit time as the start time and the change time at which the change in the current row table occurs.

19. The apparatus for creating and maintaining a history-enabled table set forth in claim 13, wherein the table maintainer is to use a DML time as the start time value and the change time at which the change in the current row table occurs.

20. The apparatus for creating and maintaining a history-enabled table set forth in claim 19, further comprising:

a DML time converter which is to convert the DML time to a corresponding commit time.

21. The apparatus set forth in claim 13, wherein the pre-existing table that is to be history enabled comprises a previously-defined table in the relational database management system.

22. A computer-implemented method of creating and maintaining a history-enabled table in a relational database system, the method comprising:

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

identifying or creating a current row table by adding a start time column to a pre-existing table that comprises a current column in a relational database system and is to be history enabled;

identifying or creating a row history table in the relational database system that has a period column and a history column corresponding to the current column in the current row table;

tracking history of the pre-existing table without reading an undo log;

responding to a change in a field in a row of the current row table, the field being in the current column corresponding to the history column of the row history table, by at least;

creating or updating a history row of the row history table to include a data of the field prior to the change in the field, in which

the row history table comprising a row period value that indicates a time period between a start time value of the current row of the current row table and a change time at which the change occurs; and

updating the start time value of the current row in the current row table to the change time at which the change occurs;

creating a history view that comprises a database view of a union of the current row table and the row history table, wherein the history view includes a period column, and a row period value in the period column is determined from the current row table and indicates a period of time that has not yet ended; and

performing a temporal query on the history view by using at least a temporal query function defined by the relational database management system, wherein the temporal query function comprises a function that takes a period of time as a parameter and returns a set of rows of the history view for which the period of time overlaps another period indicated by the row period value of one of the set of rows of the row history table.

23. The method set forth in claim 22, wherein

the action of identifying or creating the row history table further comprising adding a start time column to the row history table, and

the row period value corresponds to an end time value that indicates an end time at which the change occurs, and the the start time column of the current row table is updated with the start time value.

24. The method set forth in claim 22, further comprising:

performing a temporal query on at least the row history table by using at least a temporal query function defined by the relational database management system.

25. The method set forth in claim 22, further comprising:

creating a history view that comprises a database view of a union of the current row table and the row history table.

26. The method set forth in claim 25, further comprising:

performing a temporal query on the history view by using at least a temporal query function defined by the relational database management system.

27. The method set forth in claim 25, wherein the history view includes a period column, and a row period value in the period column is determined from the current row table and indicates a period of time that has not yet ended.

28. The method set forth in claim 27, further comprising:

performing a temporal query on the history view by using at least a temporal query function defined by the relational database management system.

29. The method set forth in claim 28, wherein the temporal query function comprises a function that takes an instant of time as a parameter and returns a set of rows of the history view for which the instant of time is included in a period indicated by the row period value of one of the set of rows of the row history table.

30. The method set forth in claim 28, wherein the temporal query function comprises a function that takes a period of time as a parameter and returns a set of rows of the history view for which the period of time overlaps another period indicated by the row period value of one of the set of rows of the row history table.

31. The method of creating and maintaining a history-enabled table set forth in claim 22, wherein in the action of responding to the change, the start time and the time at which the change occurs comprise one or more commit times.

32. The method of creating and maintaining a history-enabled table set forth in claim 22, wherein

in the action of responding to the change, the start time and the time at which the change occurs comprise DML times.

33. The method of creating and maintaining a history-enabled table set forth in claim 32, further comprising:

converting the DML times to corresponding commit times.

34. The method of creating and maintaining a history-enabled table set forth in claim 22, wherein the pre-existing table to be history enabled comprises a previously-defined table in the relational database management system.

35. An apparatus for creating and maintaining a history-enabled table in a relational database management system, comprising:

at least one processor;

a memory;

a pre-existing table in the relational database system that is to be history-enabled;

a history-enabled table creator that is to use the at least one processor to make a current row table by adding a start time column to the current row table comprising a column, corresponding to a current row in the pre-existing table, and to create a row history table that has a period column and a history row,

the history row corresponding to the current row in the current row table,

wherein

the history-enabled table creator is further to create a history view that comprises a database view of a union of the current row table and the row history table, and

the history view includes the period column, in which a row period value in the period column is determined from the current row table and indicates a period of time that has not yet ended;

a history-enabled table maintainer that is to use the at least one processor to respond to a change in a field in the current row of the current row table and to track history of the pre-existing table without reading an undo log, wherein

the history-enabled table maintainer is to update or create the history row in the row history table to include a data of the field prior to the change in the field,

the row history table comprising a row period value that indicates a time period between a start time value of the current row of the current row table and a change time at which the change occurs, and

the history-enabled table maintainer is to update the start time value of the current row in the current row table to the change time at which the change occurs; and

a query engine that is to perform a temporal query on the history view by using at least a temporal query function that is defined by the relational database management system, wherein the temporal query function comprises a function that takes a period of time as a parameter and returns a set of rows of the history view for which the period of time overlaps another period indicated by the row period value of one of the set of rows of the row history table.