PREVENTING STALENESS IN QUERY RESULTS WHEN USING ASYNCHRONOUSLY UPDATED INDEXES

• US 20170116279A1
• Filed: 10/27/2015
• Published: 04/27/2017
• Est. Priority Date: 10/27/2015
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Behnen, Marion E., Klauke, Joern, Zuzarte, Calisto P., Seifert, Jens P.
• Granted Patent: US 10,614,070 B2
• Time in Patent Office: Days
• CPC Class Codes: G06F 16/2379 Updates performed during online database operations; commit processing
• CPC Class Codes: G06F 16/2455 Query execution
• CPC Class Codes: G06F 16/24558 Binary matching operations
• CPC Class Codes: G06F 16/2456 Join operations
• CPC Class Codes: G06F 16/284 Relational databases

Abstract

A method, computer program product, and computer system for optimizing query processing is provided. An asynchronously updated index is provided for a main dataset. A time-sequences log of data modifications to the main dataset is provided. A query of the main dataset is received. The main dataset is joined with the time-sequenced log data resulting in a first intermediate result. The query is processed by keeping one or more entries satisfying the query by emulating a function of the asynchronously updated index resulting in a second intermediate result. Updated, deleted dataset entries are deleted from the asynchronously updated index. The query is processed resulting in a third intermediate result. A union of the second intermediate result and third intermediate result is built defining a final result.

Claims

1. A method, the method comprising:

providing, by one or more processors, an asynchronously updated index for a main dataset in a database system;

providing, by one or more processors, a time-sequenced log data of modifications to the main dataset, wherein the log data of modifications includes at least one of (i) an addition, (ii) an update and (iii) a deletion of affected entries of the main dataset after a cutoff time of a last asynchronous index update;

receiving, by one or more processors, a query of the main dataset;

joining, by one or more processors, the main dataset with the time-sequenced log data resulting in a first intermediate result comprising one or more entry of the main dataset after the cutoff time, wherein the one or more entry is at least one of (i) inserted, (ii) updated, and (iii) deleted;

processing, the one or more processors, the query by keeping the one or more entries satisfying the query by emulating a function of the asynchronously updated index access resulting in a second intermediate result;

filtering, by one or more processors, at least one of (i) an out updated dataset entry and (ii) a deleted dataset entry from the asynchronously updated index using the time-sequenced log data building a lookup table;

processing, by one or more processors, the query against the base table using the lookup table resulting in a third intermediate result; and

building, by one or more processors, a union of the second intermediate result and the third intermediate result defining a final result of the query.

2. The method of claim 1, wherein the time-sequenced log data is read once by the database system for at least one of (a) joining the main dataset with the time-sequenced log data and (b) filtering out updated dataset entries and deleted dataset entries from the asynchronously updated index.

3. The method of claim 1, wherein the database system is a relational database system.

4. The method of claim 3, wherein each dataset is a table of the relational database system.

5. The method of claim 1, wherein the asynchronously updated index is a text search index.

6. The method of claim 1, wherein the asynchronously updated index is a spatial index.

7. The method of claim 1, wherein the processing of the query further comprises:

determining, by one or more processors, whether one or more stale query results are acceptable.

8. A computer program product, the computer program product comprising:

a computer readable storage medium and program instructions stored on the computer readable storage medium, the program instructions comprising;

program instructions to provide an asynchronously updated index for a main dataset in a database system;

program instructions to provide a time-sequenced log data of modifications to the main dataset, wherein the log data of modifications includes at least one of (i) an addition, (ii) an update and (iii) a deletion of affected entries of the main dataset after a cutoff time of a last asynchronous index update;

program instructions to receive a query of the main dataset;

program instructions to join the main dataset with the time-sequenced log data resulting in a first intermediate result comprising one or more entry of the main dataset after the cutoff time, wherein the one or more entry is at least one of (i) inserted, (ii) updated, and (iii) deleted;

program instructions to program instructions to process the query by keeping the one or more entries satisfying the query by emulating a function of the asynchronously updated index access resulting in a second intermediate result;

program instructions to filter at least one of (i) an out updated dataset entry and (ii) a deleted dataset entry from the asynchronously updated index using the time-sequenced log data building a lookup table;

program instructions to process the query against the base table using the lookup table resulting in a third intermediate result; and

program instructions to build a union of the second intermediate result and the third intermediate result defining a final result of the query.

9. The computer program product of claim 8, wherein the time-sequenced log data is read once by the database system for at least one of (a) joining the main dataset with the time-sequenced log data and (b) filtering out updated dataset entries and deleted dataset entries from the asynchronously updated index.

10. The computer program product of claim 9, wherein the database system is a relational database system.

11. The computer program product of claim 10, wherein each dataset is a table of the relational database system.

12. The computer program product of claim 8, wherein the asynchronously updated index is a text search index.

13. The computer program product of claim 8, wherein the asynchronously updated index is a spatial index.

14. The computer program product of claim 8, wherein the processing of the query further comprises:

program instructions to determine whether one or more stale query results are acceptable.

15. A computer system, the computer system comprising:

one or more computer processors;

one or more computer readable storage media;

program instructions stored on the computer readable storage media for execution by at least one of the one or more processors, the program instructions comprising;

program instructions to provide an asynchronously updated index for a main dataset in a database system;

program instructions to provide a time-sequenced log data of modifications to the main dataset, wherein the log data of modifications includes at least one of (i) an addition, (ii) an update and (iii) a deletion of affected entries of the main dataset after a cutoff time of a last asynchronous index update;

program instructions to receive a query of the main dataset;

program instructions to join the main dataset with the time-sequenced log data resulting in a first intermediate result comprising one or more entry of the main dataset after the cutoff time, wherein the one or more entry is at least one of (i) inserted, (ii) updated, and (iii) deleted;

program instructions to program instructions to process the query by keeping the one or more entries satisfying the query by emulating a function of the asynchronously updated index access resulting in a second intermediate result;

program instructions to filter at least one of (i) an out updated dataset entry and (ii) a deleted dataset entry from the asynchronously updated index using the time-sequenced log data building a lookup table;

program instructions to process the query against the base table using the lookup table resulting in a third intermediate result; and

program instructions to build a union of the second intermediate result and the third intermediate result defining a final result of the query.

16. The computer system of claim 15, wherein the time-sequenced log data is read once by the database system for at least one of (a) joining the main dataset with the time-sequenced log data and (b) filtering out updated dataset entries and deleted dataset entries from the asynchronously updated index.

17. The computer system of claim 15, wherein the database system is a relational database system.

18. The computer system of claim 17, wherein each dataset is a table of the relational database system.

19. The computer system of claim 15, wherein the asynchronously updated index is a text search index.

20. The computer system of claim 15, wherein the processing of the query further comprises:

program instructions to determine whether one or more stale query results are acceptable.