Database system that provides for history-enabled tables

摘要

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.

主权项

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.