Management of updates in a database system

• US 9,378,234 B2
• Filed: 03/11/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/793,216
• Publication Number:
  US 20140258215A1
• Time in Patent Office: 1,205 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 computer-implemented method of managing a database contained in a storage facility, comprising:

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

ordering entries of the log, based on a key;

receiving, using a parser, a maintenance request related to the index;

determining, with the DBMS, 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, the pipeline being open and inactive including the pipeline being open, but not actively in use by a search request associated with the application and the index;

adding, using a batch process, the maintenance request to the log in response to determining that the index is not being searched, wherein the maintenance request is a change to the index and the maintenance request is used to determine delays in maintenance of the index;

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

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

2. The method of claim 1, further comprising:

accounting for duplicate entries of the log, in response to multiple updates to a row in the log, which establish duplicate entries in the log, using only a latest update to decrease actual maintenance on the index.

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

4. The method of claim 1, wherein ordering entries of the log includes matching keys, as entries are added, for efficiency.

5. The method of claim 1, further comprising:

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

6. The method of claim 1, further comprising:

establishing a log size threshold;

monitoring a size of the log associated with the index;

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

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

7. The method of claim 1, further comprising:

establishing a refresh period threshold;

monitoring an amount of time the index is not current;

determining that the amount of time the index is not current has reached the refresh period threshold; and

initiating, in response to determining that the amount of time the index is not current has reached the refresh period threshold, the maintenance request.

8. The method of claim 1, wherein the pipeline being closed includes the pipeline connecting the application and the index not being associated with a search request.

9. The method of claim 1, wherein the pipeline being closed includes the pipeline having a lack of data flow.

10. The method of claim 1, wherein adding the maintenance request to the log includes:

dynamically storing the maintenance request in the log to batch updates for performance at a later time.

11. The method of claim 10, wherein batch updates update multiple rows in one operation to reduce input/output costs, working set memory sizes, central processing unit consumption, and performance overhead.