Multi-phase data expiration path for a database

Patent metadata

Field	Value
Publication number	US-9684687-B1
Application number	US-201414203231-A
Country	US
Kind code	B1
Filing date	Mar 10, 2014
Priority date	Mar 10, 2014
Publication date	Jun 20, 2017
Grant date	Jun 20, 2017

Abstract

Each row of data in a first database table is associated with a plurality of time thresholds that indicate, respectively, when a given row of data in the first table is current, stale, or expired. When a given row of data expires, that row is moved from the first table to a second database table that stores expired data. One of the time thresholds indicates a predetermined amount of time that the expired data will be maintained in the second table. Once that threshold expires, that row of data is deleted from the second table. Until then, however, a user may issue queries to access the expired data along with the current data and the stale data.

First claim

What is claimed is:

1. A computer-implemented method comprising:
creating first and second database tables in a memory of a computing device, wherein the first and second database tables define a multi-phase expiration path for data stored in the first and second database tables, and are created based on keywords that identify a plurality of time values defining respective predetermined time periods for each phase in the multi-phase expiration path;
transitioning the data stored in the first database table along the multi-phase expiration path from an initial phase in which the data is current to a final phase in which the data is expired;
moving expired data from the first database table to the second database table to be stored for a predetermined period of time; and
retrieving current data from the first database table and expired data from the second database table responsive to receiving a query from a user.

2. The computer-implemented method of claim 1 wherein the multi-phase expiration path for the data stored in the first database table further comprises an intermediate phase in which the data in the first database table is stale but not expired.

3. The computer-implemented method of claim 2 wherein retrieving current data from the first database table and expired data from the second database table responsive to receiving a query from the user further comprises:
retrieving stale data from the first database table in addition to the current data responsive to receiving the query from the user; and
indicating, to the user, that some of the data retrieved from the first database table comprises stale data.

4. The computer-implemented method of claim 2 wherein transitioning the data stored in the first database table along the multi-phase expiration path from an initial phase in which the data is current to a final phase in which the data is expired comprises:
transitioning the data stored in the first database table from the initial phase to the intermediate phase after a first predetermined time period has expired; and
transitioning the data stored in the first database table from intermediate phase to the final phase after a second predetermined time period has expired, wherein the second predetermined time period is not less than the first predetermined time period.

5. The computer-implemented method of claim 2 wherein each row of data in the first database table comprises a reference timestamp indicating when the data in the row was added or updated, and wherein the respective predetermined time periods for each phase in the multi-expiration path comprises:
a first time threshold defining a first predetermined time period during which a selected row comprises data that is current;
a second time threshold defining the second predetermined time period during which the selected row comprises data that is stale; and
a third time threshold defining a fixed time period for which the expired data remains in the second database table.

6. The computer-implemented method of claim 5 wherein transitioning the data stored in the first database table along the multi-phase expiration path from an initial phase in which the data is current to a final phase in which the data is expired comprises transitioning the selected row of data along the multi-phase expiration path based on a comparison of the reference timestamp of the selected row to the time thresholds.

7. The computer-implemented method of claim 5 further comprising calculating respective time values for the second and third time thresholds based on a time value for the first time threshold.

8. The computer-implemented method of claim 1 further comprising deleting a row of the expired data from the second database table if a reference timestamp associated with the row of expired data is greater than a current timestamp.

9. A computing device comprising:
a communications interface circuit configured to communicate data with a client device via a communications network;
a memory circuit configured to store the data in first and second database tables, wherein the first and second database tables are created to define a multi-phase expiration path for the data stored in the first and second database tables, and are created based on keywords that identify a plurality of time values defining respective predetermined time periods for each phase in the multi-phase expiration path; and
a processing circuit communicatively connected to both the communications interface circuit and the memory circuit, and configured to:
transition the data stored in the first database table along the multi-phase expiration path from an initial phase in which the data is current to a final phase in which the data is expired;
move expired data from the first database table to a second database table in the memory circuit to be stored for a predetermined period of time; and
retrieve current data from the first database table and expired data from the second database table responsive to receiving a query from a user over the communications interface circuit.

10. The computing device method of claim 9 wherein the multi-phase expiration path for the data stored in the first database table further comprises an intermediate phase in which the data in the first database table is stale but not expired.

11. The computing device of claim 10 wherein to retrieve current data from the first database table and expired data from the second database table responsive to receiving a query from the user, the processor circuit is further configured to:
retrieve stale data from the first database table in addition to the current data; and
indicate, to the user, that some of the data retrieved from the first database table comprises stale data.

12. The computing device of claim 10 wherein to transition the data stored in the first database table along the multi-phase expiration path from an initial phase in which the data is current to a final phase in which the data is expired, the processor circuit is further configured to:
transition the data stored in the first database table from the initial phase to the intermediate phase after a first predetermined time period has expired;
transition the data stored in the first database table from intermediate phase to the final phase after a second predetermined time period has expired, wherein the second predetermined time period is not less than the first predetermined time period.

13. The computing device of claim 10 wherein each row of data in the first database table comprises a reference timestamp indicating when the data in the row was added or updated, and wherein the processing circuit is further configured to:
maintain a first time threshold defining a first predetermined time period during which a given row comprises data that is current;
maintain a second time threshold defining the second predetermined time period during which the given row comprises data that is stale; and
maintain a third time threshold defining a fixed time period for which the expired data remains in the second database table.

14. The computing device of claim 13 wherein the processor circuit is further configured to transition the given row of data along the multi-phase expiration path based on a comparison of a current timestamp to each of the first, second, and third time thresholds.

15. The computing device of claim 9 wherein the processor circuit is further configured to delete a row of expired data from the second database table if a reference timestamp associated with the row of expired data is greater than a current timestamp.

Assignees

• Ca Inc

Inventors

• Dubost Philippe

Classifications

• G06F17/30339

  Physics · mapped topic

• G06F17/30371 Primary

  Physics · mapped topic

• G06F16/2282 Primary

  Tablespace storage structures; Management thereof · CPC title

• G06F16/2477

  Temporal data queries · CPC title

• G06F16/219

  Managing data history or versioning (querying versioned data G06F16/2474; querying temporal data G06F16/2477) · CPC title