Managing a temporal key property in a database management system

Patent metadata

Field	Value
Publication number	US-10262025-B2
Application number	US-201514800266-A
Country	US
Kind code	B2
Filing date	Jul 15, 2015
Priority date	Oct 5, 2011
Publication date	Apr 16, 2019
Grant date	Apr 16, 2019

Abstract

Embodiments provide an approach for efficiently searching a temporal key index as well as enforcing key distinctiveness property in a database table corresponding to the index. For example, by addressing time range overlaps in an index having a sort order for temporal keys, a database management tool of a server system may efficiently process client requests while enforcing a temporal key property in the database table.

First claim

What is claimed is:

1. A computer-implemented method for enforcing a temporal key property in a database table, the method comprising:
receiving a request to perform a group update of one or more database rows in the database table, wherein each update in the group update includes a queried temporal key, a current start time, a requested start time, a current end time, and a requested end time associated with the respective update;
accessing, by operation of one or more computer processors, an index corresponding to temporal keys in the database table, wherein each entry in the index includes a stored temporal key, a stored start time, and a stored end time for one database row in the database table;
for each requested update included in the group update:
deleting a database row of the database table and an entry of the index that include the stored temporal key, the stored start time, and the stored end time that match the queried temporal key, the current start time, and the current end time of the requested update; upon determining that the index does not include an entry having a stored temporal key matching the queried temporal key of the update, adding an entry to the index that corresponds to the update and inserting a database row into the database table that corresponds to the update, and
upon determining that the index includes an entry having a stored temporal key matching the queried temporal key of the update:
upon determining that no overlap exists between the requested start time and the requested end time of the update and a stored start time and a stored end time for an index entry, adding an entry to the index that corresponds to the update and inserting a database row into the database table that corresponds to the update; and
upon determining that an overlap exists between the requested start time and the requested end time of the update and a stored start time and a stored end time for an index entry, storing a pointer to the update in an overlap data structure and not performing the update at a first time; and

at a second time, after the first time, for each update referenced by the overlap data structure:
upon determining that the index does not include an entry having the queried temporal key of the update, adding a new entry to the index that corresponds to the update and inserting a new database row into the database table that corresponds to the update; and
upon determining that the index includes an entry having the queried temporal key of the update:
upon determining that no overlap exists between the requested start time and the requested end time of the update and a stored start time and a stored end time for an index entry, adding an additional entry to the index that corresponds to the update and inserting an additional database row into the database table that corresponds to the update; and
upon determining that the overlap stills exists between the requested start time and the requested end time of the update and a stored start time and a stored end time for an index entry, rejecting the update.

2. The computer-implemented method of claim 1 , wherein the index has a bi-directionally navigable sort order, and wherein determining whether an overlap exists between the update and an index entry comprises at least one of:
(i) determining whether the index includes an entry having a stored temporal key, a stored start time, and a stored end time matching the queried temporal key, the requested start time, and the requested end time of the update;
(ii) determining whether a prior index entry exists having stored end time after the requested start time of the update, the prior index entry being the highest entry in the index that sorts lower than the update and that has a stored temporal key matching the queried temporal key of the update; and
(iii) determining whether a subsequent index entry exists having a stored start time prior to the requested end time of the update, the subsequent index entry being the lowest entry in the index that sorts higher than the update and that has a stored temporal key matching the queried temporal key of the update.

3. The computer-implemented method of claim 1 , wherein the index has a forward and ascending uni-directionally navigable sort order, and wherein determining whether an overlap exists between the update and the index entry comprises at least one of:
(i) determining whether the index includes an entry having a stored temporal key, a stored start time, and a stored end time matching the queried temporal key, the requested start time, and the requested end time of the update; and
(ii) determining whether a subsequent index entry exists having a stored start time prior to the requested end time of the update, the subsequent index entry being the lowest entry in the index that sorts higher than the update and that has a stored temporal key matching the queried temporal key of the update.

4. The computer-implemented method of claim 1 , wherein the index has a backward and ascending uni-directionally navigable sort order, and wherein determining whether an overlap exists between the update and an index entry comprises at least one of:
(i) determining whether the index includes an entry having a stored temporal key, a stored start time, and a stored end time matching the queried temporal key, the requested start time, and the requested end time of the update; and
(ii) determining whether a prior index entry exists having a stored end time after the requested start time of the update, the prior index entry being the highest entry in the index that sorts lower than the update and that has a stored temporal key matching the temporal key of the update.

5. A computer-readable storage medium storing an application, which, when executed, performs an operation for enforcing a temporal key property in a database table, the operation comprising:
receiving a request to perform a group update of one or more database rows in the database table, wherein each update in the group update includes a queried temporal key, a current start time, a requested start time, a current end time, and a requested end time associated with the respective update;
accessing, by operation of one or more computer processors, an index corresponding to temporal keys in the database table, wherein each entry in the index includes a stored temporal key, a stored start time, and a stored end time for one database row in the database table;
for each requested update included in the group update:
deleting a database row of the database table and an entry of the index that include the stored temporal key, the stored start time, and the stored end time that match the queried temporal key, the current start time, and the current end time of the requested update; upon determining that the index does not include an entry having a stored temporal key matching the queried temporal key of the update, adding an entry to the index that corresponds to the update and inserting a database row into the database table that corresponds to the update, and
upon determining that the index includes an entry having a stored temporal key matching the queried temporal key of the update:
upon determining that no overlap exists between the requested start time and the requested end time of the update and a stored start time and a stored end time for an index entry, adding an entry to the index that corresponds to the update and inserting a database row into the database table that corresponds to the update; and
upon determining that an overlap exists between the requested start time and the requested end time of the update and a stored start time and a stored end time for an index entry, storing a pointer to the update in an overlap data structure and not performing the update at a first time; and;

Assignees

• IBM

Inventors

• Iyer Balakrishna R
• Lyle Robert W
• Mukai Jerry

Classifications

• G06F17/30371 Primary

  Physics · mapped topic

• G06F17/30339

  Physics · mapped topic

• G06F17/30595

  Physics · mapped topic

• G06F17/30345

  Physics · mapped topic

• G06F17/30336

  Physics · mapped topic