Management of updates in a database system

• US 9,378,235 B2
• Filed: 03/15/2013
• Issued: 06/28/2016
• Est. Priority Date: 03/11/2013
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Bestgen, Robert J., Beuch, Daniel E.
• Primary Examiner(s): PYO, MONICA M
• Application Number:
  US13/833,016
• Publication Number:
  US 20140258251A1
• Time in Patent Office: 1,201 Days
• Field of Search: 707/609, 707/610, 707/618, 707/696
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2272 Management thereof
• CPC Class Codes: G06F 16/2453 Query optimisation

Abstract

A method, system, and computer program product to efficiently maintain a database is disclosed. The method, system, and computer program product may include structuring the database to have a table, an index associated with the table, and a log associated with the index. The method, system, and computer program product may include receiving a maintenance request related to the index. The method, system, and computer program product may include accounting for the maintenance request when the index is being searched. The method, system, and computer program product may include adding the maintenance request to the log when the index is not being searched.

Claims

1. A system comprising:

a memory;

one or more processors; and

program code including a query optimizer, the program code configured to be executed by the one or more processors to process a database query, the program code configured to;

structure, using a database management system (DBMS), a database to have a table, an index associated with the table, and a log associated with the index;

order entries of the log, based on a key;

receive, with a parser, a maintenance request related to the index;

determine, with the DBMS, that the index is being searched;

add, using a batch process, the maintenance request to the log in response to determining that the index is not being searched, determining that the index is not being searched including determining that a pipeline is either closed or open and inactive between an application and the index, wherein the pipeline being open and inactive includes the pipeline being open, but not actively in use by a search request associated with the application and the index, wherein the maintenance request is a change to the index and the maintenance request is used to determine delays in maintenance of index;

determine, with the DBMS, that the index is being searched; and

account for the maintenance request, in response to determining that the index is being searched, wherein account for the maintenance request includes update the index with the maintenance request and resume searching.

2. The system of claim 1, wherein the program code is further configured to account for duplicate entries of the log, in response to multiple updates to a row in the log that establish duplicate entries in the log, using only a latest update to decrease actual maintenance on the index.

3. The system of claim 1, wherein the key corresponds to a key column of the index.

4. The system of claim 1, wherein a change to the index is selected from the group consisting of adding, deleting, and changing a row of a particular index.

5. The system of claim 1, wherein the program code is further configured to order entries of the log by matching keys, as entries are added, for efficiency.

6. The system of claim 1, wherein the program code is further configured to:

initiate the maintenance request, based on a predictive element that uses access history data, to anticipate an application coming online.

7. The system of claim 1, wherein the program code is further configured to:

establish a log size threshold;

monitor a size of the log associated with the index;

determine that the size of the log associated with the index has reached the log size threshold; and

initiate, in response to determining the size of the log associated with the index has reached the log size threshold, the maintenance request.

8. The system of claim 1, wherein the pipeline being closed includes the pipeline connecting the application and the index not being associated with the search request, and wherein the pipeline being closed includes the pipeline having a lack of data flow.