Method and mechanism for determining life intervals for a key in an index

• US 8,015,161 B2
• Filed: 09/03/2004
• Issued: 09/06/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: Sinha, Bipul, Ganesh, Amit, Raja, Vivekanandhan
• Primary Examiner(s): Padmanabhan; Kavita
• Application Number:
  US10/933,828
• Publication Number:
  US 20050120064A1
• Time in Patent Office: 2,559 Days
• Field of Search: 707/206, 707/203, 707/201, 707/200, 707/2, 714/15, 711/167
• 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 determine one or more life intervals when given a key. In addition, one or more life intervals can be determined for each key within a range of keys. This type of process is useful for auditing purposes or if it is desire to implement a query upon past versions when a key or range of keys is specified for the data for which past versions is desired. This facilitates algorithms, mechanisms, and processes to implement a query upon past versions.

Claims

1. A computer implemented method for determining one or more life intervals for a key in a database system for retrieving one or more past versions of a location for a data item in a database table in the database system, comprising:

using a computer system which comprises at least one processor and is configured for performing;

receiving the key for which it is desired to determine one or more life intervals, wherein the key is used as an input of a query statement to identify the data item in the database table;

identifying a location where the key exists or should exist by using at least a database index structure that comprises a plurality of indices for the database table;

determining the one or more life intervals for the key based at least in part upon analysis of a type of operation detected against the key and further in part upon whether the key existed prior to an action to retrieve the one or more past versions of the location, in which the act of determining the one or more life intervals comprises;

applying a portion of a log record to restore the database index structure in its entirety to one or more prior states that include one or more past incarnations of the index, wherein

the log record comprises an undo log record for the database system that is stored separately from the database table,

the database table comprises a single version of the data item, and

an access to the database table for retrieving the one or more past versions uses the key or a range of keys instead of one or more row-ids.

2. The method of claim 1, in which the action of reviewing past versions of the location associated with the key is performed by applying one or more undo records, in which a context is updated based upon identification of an undo record having a different transaction identifier.

3. The method of claim 1 in which the key is a primary key.

4. The method of claim 1 in which the location is an index block.

5. The method of claim 1 in which the action of reviewing past versions of the location associated with the key is performed by applying one or more undo records, in which the undo records are applied until a low point of a range interval has been reached or until there are no further undo records to apply.

6. The method of claim 1 in which the type of operation detected is an insert operation.

7. The method of claim 6 further comprising:

determining a commit time for the transaction that performed the insert operation; and

searching for the key prior to the commit time.

8. The method of claim 6, in which it is determined that the key did not exist before the transaction, and that the start point of the presently detected life interval is based upon the commit time of the transaction that performed the insert operation.

9. The method of claim 6 in which it is determined that the key existed before the transaction that performed the insert operation.

10. The method of claim 9 in which a present insert has occurred and the presently detected life interval is based upon the commit time of the transaction that performed the insert operation to the end-life-interval time for the present analysis.

11. The method of claim 9 in which a delete has occurred, and the presently detected life interval is based upon the commit time of the transaction that performed the insert operation to the current time or a subsequent transaction commit.

12. The method of claim 9 in which further undo records are applied to identify start point to the presently detected life interval.

13. The method of claim 1 in which the type of operation detected is a delete operation.

14. The method of claim 13 in which it is determined that the key existed before the transaction that performed the delete operation.

15. The method of claim 14 in which the presently detected life interval is based upon the commit time of the transaction that performed the delete operation to the current time or a subsequent transaction commit.

16. The method of claim 13 in which it is determined that the key did not exist before the transaction that performed the delete operation, wherein no present life interval is detected.

17. The method of claim 1 in which the one or more life intervals are used to audit the database system.

18. The method of claim 17 in which an audit trail is created based upon the one or more life intervals.

19. The method of claim 1 in which the one or more life intervals are used to generate past versions of data associated with the key.

20. The computer implemented method of claim 1, further comprising using the computer system for performing:

applying a table decoration clause to the database table to facilitate the act of applying the portion of the log record to restore the one or more past versions of the data item over the one or more life intervals, wherein

the table decoration clause causes to identify all versions of the data item that exist or ever existed within the one or more life intervals.

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

determining whether a structural change has occurred to the location, and if the structural change is determined to have occurred to the location, accessing the database table or the index structure as of a time prior to an occurrence of the structural change.

22. The computer implemented method of claim 21, in which the structural change comprises a block split or a block coalesce.

23. A computer program product comprising a computer usable storage medium having executable code which, when executed by at least one processor, causes the at least one processor to execute a process for determining one or more life intervals for a key in a database system for retrieving one or more past versions of a location for a data item in a database table in the database system, the process comprising:

using a computer system which comprises the at least one processor and is configured for performing;

receiving the key for which it is desired to determine one or more life intervals, wherein

the key is used as an input of a query statement to identify the data item in the database table;

identifying a location in the database system where the key exists or should exist by using at least a database index structure that comprises a plurality of indices for the database table;

determining the one or more life intervals for the key based upon analysis of type of operation detected against the key and further in part upon whether the key existed prior to an action to retrieve the one or more past versions of the location, in which the act of determining the one or more life intervals comprises;

applying a portion of a log record to restore the database index structure in its entirety to one or more prior states that include one or more past incarnations of the index, wherein

the log record comprises an undo log record for the database system that is stored separately from the database table,

the database table comprises a single version of the data item, and

an access to the database table for the retrieval of the one or more past versions uses the key or a range of keys instead of one or more row-ids.

24. The computer program product of claim 23, in which the process further comprises:

determining a commit time for the transaction that performed an insert or a delete operation; and

searching for the key prior to the commit time.

25. The computer program product of claim 24, in which it is determined that the key did not exist before the transaction, and that the start point of the presently detected life interval is based upon the commit time of the transaction that performed the insert or the delete operation.

26. The computer program product of claim 23, the process further comprising:

determining whether a structural change has occurred to the location, and if the structural change is determined to have occurred to the location, accessing the database table or the index structure as of a time prior to an occurrence of the structural change.

27. The computer program product of claim 26, in which the structural change comprises a block split or a block coalesce.

28. A system for determining one or more life intervals for a key in a database system for retrieving one or more past versions of a location for a data item in a database table in the database system, comprising:

a computer system which comprises at least one processor and is configured for performing a process comprising;

receiving the key for which it is desired to determine one or more life intervals, wherein

the key is used as an input of a query statement to identify the data item in the database table;

identifying a location where the key exists or should exist by using at least a database index structure that comprises a plurality of indices for the database table;

determining the one or more life intervals for the key based at least in part upon analysis of type of operation detected against the key and further in part upon whether the key existed prior to an action to retrieve the one or more past versions of the location, in which the act of determining the one or more life intervals comprises;

applying a portion of a log record to restore the database index structure in its entirety to one or more prior states that include one or more past incarnations of the index, wherein

the log record comprises an undo log record for the database system that is stored separately from the database table,

the database table comprises a single version of the data item, and

an access to the database table for retrieving the one or more past versions uses the key or a range of keys instead of one or more row-ids.

29. The system of claim 28, the at least one computer system is further configured for performing:

determining a commit time for the transaction that performed an insert or a delete operation; and

searching for the key prior to the commit time.

30. The system of claim 29, in which it is determined that the key did not exist before the transaction, and that the start point of the presently detected life interval is based upon the commit time of the transaction that performed the insert or the delete operation.

31. The system of claim 28, the process further comprising:

determining whether a structural change has occurred to the location, and if the structural change is determined to have occurred to the location, accessing the database table or the index structure as of a time prior to an occurrence of the structural change.

32. The system of claim 31, in which the structural change comprises a block split or a block coalesce.